SQL Dasar untuk Python

Konsep tabel, primary key, dan foreign key dari Chapter Dasar Database Relasional belum berguna kalau kita tidak bisa mengoperasikannya. Bahasa operasinya bernama SQL, dan keahlian ini berlaku lintas database: perintah yang kita tulis untuk SQLite hari ini juga dikenali PostgreSQL dan MySQL dengan sedikit variasi. Pada chapter ini kita menulis SQL nyata dari Python menggunakan module sqlite3, dengan tabel produk toko kelontong sebagai arena latihan.

Membuat Tabel dan Menyisipkan Data #

SQLite mengenal lima tipe penyimpanan; tiga di antaranya kita pakai terus-menerus:

Program pertama kita membuat file inventaris.db, mendefinisikan tabel produk, lalu mengisinya:

from pathlib import Path
import sqlite3

Path("inventaris.db").unlink(missing_ok=True)
conn = sqlite3.connect("inventaris.db")
conn.execute("""CREATE TABLE produk (
    id INTEGER PRIMARY KEY,
    nama TEXT NOT NULL,
    harga REAL NOT NULL,
    stok INTEGER NOT NULL DEFAULT 0
)""")
conn.executemany(
    "INSERT INTO produk (nama, harga, stok) VALUES (?, ?, ?)",
    [
        ("Kopi Hitam 200g", 18000, 12),
        ("Teh Celup Isi 25", 9500, 30),
        ("Gula Pasir 1kg", 15500, 25),
        ("Beras Premium 5kg", 68000, 8),
        ("Minyak Goreng 1L", 17500, 0),
        ("Susu UHT 1L", 19000, 15),
    ],
)
conn.commit()

for baris in conn.execute("SELECT * FROM produk"):
    print(baris)
conn.close()

Output yang Dihasilkan:

(1, 'Kopi Hitam 200g', 18000.0, 12)
(2, 'Teh Celup Isi 25', 9500.0, 30)
(3, 'Gula Pasir 1kg', 15500.0, 25)
(4, 'Beras Premium 5kg', 68000.0, 8)
(5, 'Minyak Goreng 1L', 17500.0, 0)
(6, 'Susu UHT 1L', 19000.0, 15)

Mari kita telaah baris-baris pentingnya:

1. Baris 4 (Path("inventaris.db").unlink(missing_ok=True)): menghapus file database lama agar contoh selalu dimulai dari kondisi bersih dan dapat diulang.
2. Baris 5 (conn = sqlite3.connect("inventaris.db")): membuka (atau membuat) file database; perintah setelahnya bekerja pada file ini.
3. Baris 6-11 (CREATE TABLE ...): menetapkan nama dan tipe tiap kolom. Kolom id tidak kita isi manual — SQLite menomori baris otomatis karena bertindak sebagai INTEGER PRIMARY KEY.
4. Baris 12-22 (conn.executemany(...)): menjalankan satu pernyataan INSERT enam kali dengan data berbeda. Tanda ? adalah placeholder yang diisi nilai Python; alasan kuat mengapa kita tidak merangkai string SQL dibahas pada Chapter Parameterized Queries.
5. Baris 23 (conn.commit()): mengunci enam baris tersebut secara permanen ke file.
6. Baris 25-26 (for baris in ...): menjalankan SELECT * FROM produk lalu mencetak setiap baris sebagai tuple.

Membaca dengan SELECT #

SELECT membaca baris dengan empat klausa yang saling melengkapi: WHERE menyaring, ORDER BY mengurutkan, dan LIMIT memotong jumlah hasil:

import sqlite3

conn = sqlite3.connect("inventaris.db")
query = """SELECT nama, harga, stok
FROM produk
WHERE harga >= 15000
ORDER BY harga DESC
LIMIT 3"""
for nama, harga, stok in conn.execute(query):
    print(f"{nama:<20} Rp{harga:>7.0f}  stok: {stok}")
conn.close()

Output yang Dihasilkan:

Beras Premium 5kg    Rp  68000  stok: 8
Susu UHT 1L          Rp  19000  stok: 15
Kopi Hitam 200g      Rp  18000  stok: 12

Mesin SQLite membaca keenam baris, membuang yang harganya di bawah 15000 (WHERE), mengurutkan dari termahal (DESC), lalu hanya mengirim tiga baris pertama ke Python (LIMIT). Penyaringan terjadi di dalam database, bukan di kode Python — inilah alamat utama efisiensi database.

UPDATE dan DELETE #

UPDATE mengubah kolom pada baris yang cocok dengan WHERE; DELETE membuangnya. Keduanya melaporkan jumlah baris tersentuh lewat atribut cursor.rowcount:

import sqlite3

conn = sqlite3.connect("inventaris.db")
cursor = conn.execute(
    "UPDATE produk SET harga = harga + 500 WHERE nama = 'Gula Pasir 1kg'"
)
conn.commit()
print("Baris terpengaruh:", cursor.rowcount)

baris = conn.execute(
    "SELECT nama, harga FROM produk WHERE nama = 'Gula Pasir 1kg'"
).fetchone()
print(baris)
conn.close()

Output yang Dihasilkan:

Baris terpengaruh: 1
('Gula Pasir 1kg', 16000.0)

Harga gula naik dari 15500 menjadi 16000. Proses DELETE berpola sama:

import sqlite3

conn = sqlite3.connect("inventaris.db")
cursor = conn.execute("DELETE FROM produk WHERE stok = 0")
conn.commit()
print("Baris terhapus:", cursor.rowcount)
print("Produk tersisa:", conn.execute("SELECT COUNT(*) FROM produk").fetchone()[0])
conn.close()

Output yang Dihasilkan:

Baris terhapus: 1
Produk tersisa: 5

Agregasi dengan COUNT, AVG, SUM, dan GROUP BY #

Fungsi agregat meringkas banyak baris menjadi satu nilai: COUNT menghitung, AVG merata-ratakan, SUM menjumlahkan. Kombinasi dengan GROUP BY, agregasi berjalan per kelompok. Kita ringkas dulu keseluruhan tabel, lalu buat tabel bantu penjualan untuk agregasi per produk:

import sqlite3

conn = sqlite3.connect("inventaris.db")
ringkasan = conn.execute(
    "SELECT COUNT(*), ROUND(AVG(harga), 2), SUM(stok) FROM produk"
).fetchone()
print("Ringkasan (jumlah, rata-rata harga, total stok):")
print(ringkasan)

conn.execute("""CREATE TABLE penjualan (
    id INTEGER PRIMARY KEY,
    produk_id INTEGER NOT NULL,
    jumlah INTEGER NOT NULL
)""")
conn.executemany(
    "INSERT INTO penjualan (produk_id, jumlah) VALUES (?, ?)",
    [(1, 2), (1, 1), (2, 3), (3, 5), (4, 1), (6, 2), (6, 4)],
)
conn.commit()

query = """SELECT produk_id, COUNT(*) AS jumlah_transaksi, SUM(jumlah) AS total_terjual
FROM penjualan
GROUP BY produk_id
ORDER BY total_terjual DESC"""
for baris in conn.execute(query):
    print(baris)
conn.close()

Output yang Dihasilkan:

Ringkasan (jumlah, rata-rata harga, total stok):
(5, 26100.0, 90)
(6, 2, 6)
(3, 1, 5)
(2, 1, 3)
(1, 2, 3)
(4, 1, 1)

Lima produk tersisa memiliki rata-rata harga 26100 dan total stok 90. Pada hasil kedua, mesin SQLite mengelompokkan tujuh transaksi berdasarkan produk_id, lalu menghitung COUNT(*) dan SUM(jumlah) untuk tiap kelompok; produk 6 (Susu UHT) menjadi penjual terbanyak dengan 6 unit dari 2 transaksi. Perhatikan pula bahwa kolom hasil agregat bisa diberi nama alias (AS total_terjual) dan diurutkan seperti kolom biasa.

Dengan lima perintah inti ini — CREATE, INSERT, SELECT, UPDATE, DELETE — kita sudah bisa mengelola data inventaris. Chapter SQLite dengan Python berikutnya membedah lebih dekat module sqlite3 yang selama ini bekerja di belakang layar.