Integrasi Python

Modul sqlite3 bawaan, query berparameter, dan kontrol transaksinya.

Daftar isi

Python membawa modul sqlite3 dalam distribusi bawaannya, sehingga program Python dapat membuka dan mengelola basis data SQLite tanpa pemasangan tambahan. Topik ini membedah alur kerjanya secara berurutan, mulai dari membuat koneksi dan cursor, menyusun query berparameter, mengambil baris hasil dengan tiga metode fetch, mengendalikan transaksi, sampai menyatukan semuanya dalam satu skrip katalog utuh yang dapat dijalankan dan diperiksa keluarannya.

Koneksi dan cursor#

Fungsi sqlite3.connect menerima nama berkas basis data dan mengembalikan objek koneksi. Apabila berkasnya belum ada, SQLite membuatnya sekaligus dengan strukturnya, sedangkan nilai khusus :memory: membuat basis data di memori yang hilang ketika koneksi ditutup. Eksekusi SQL dilakukan melalui cursor, yaitu objek kerja yang menjalankan pernyataan dan menyimpan posisi hasil pembacaannya. Koneksi juga menyediakan metode execute yang membuat cursor sesaat, menjalankan pernyataannya, lalu mengembalikan cursor itu.

python

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute(
    "CREATE TABLE buku ("
    " id      INTEGER PRIMARY KEY,"
    " judul   TEXT NOT NULL,"
    " penulis TEXT NOT NULL)"
)
kur = koneksi.execute(
    "INSERT INTO buku (judul, penulis) VALUES (?, ?)",
    ("Kisah Kota Tua", "Sari Wulandari"),
)
print("id baris baru    :", kur.lastrowid)
print("baris terdampak  :", kur.rowcount)
print("transaksi terbuka:", koneksi.in_transaction)
koneksi.commit()
print("transaksi terbuka:", koneksi.in_transaction)
koneksi.close()

text Keluaran

id baris baru    : 1
baris terdampak  : 1
transaksi terbuka: True
transaksi terbuka: False

Cursor hasil INSERT membawa dua atribut yang sering dipakai. Atribut lastrowid menyimpan nilai kolom INTEGER PRIMARY KEY dari baris yang baru disisipkan, sedangkan rowcount menyatakan jumlah baris yang terdampak pernyataan tersebut. Program yang lebih panjang dapat menetapkan cursor sendiri lewat koneksi.cursor(), dan objek yang dihasilkan berantarmuka sama dengan hasil koneksi.execute pada contoh, sehingga keduanya dapat dipertukarkan sesuai kerapian kode. Keluaran di atas juga memperlihatkan perilaku penting lainnya, yaitu transaksi yang terbuka sendiri sebelum commit dipanggil. Mekanisme ini dibahas lebih lanjut pada bagian transaksi di akhir topik.

Query berparameter#

Nilai yang dikirim bersama query sebaiknya tidak ditempel langsung ke teksnya, melainkan lewat placeholder, yaitu tanda khusus pada teks SQL yang menggantikan posisi sebuah nilai. Modul sqlite3 mendukung dua gaya, yaitu tanda tanya ? untuk penempatan posisional dan :nama untuk penamaan eksplisit. Gaya posisional memasok nilai berupa tuple yang urutannya sama dengan urutan tanda tanya, sedangkan gaya bernama memasok nilai berupa dictionary yang kuncinya cocok dengan nama placeholder.

python

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute("CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, penulis TEXT, tahun INTEGER)")
buku = [
    ("Kisah Kota Tua", "Sari Wulandari", 2018),
    ("Anak Sungai", "Rahmat Hidayat", 2021),
    ("Musim di Ujung Jalan", "Lina Marlina", 2019),
]
koneksi.executemany("INSERT INTO buku (judul, penulis, tahun) VALUES (?, ?, ?)", buku)
koneksi.commit()

kur = koneksi.execute("SELECT judul FROM buku WHERE tahun = ?", (2019,))
print("posisional :", kur.fetchone()[0])

kur = koneksi.execute(
    "SELECT judul FROM buku WHERE penulis = :penulis AND tahun > :tahun",
    {"penulis": "Sari Wulandari", "tahun": 2000},
)
print("bernama    :", kur.fetchone()[0])
koneksi.close()

text Keluaran

posisional : Musim di Ujung Jalan
bernama    : Kisah Kota Tua

Perhatikan bahwa tuple untuk satu nilai harus tetap ditulis sebagai tuple, sehingga (2019,) memakai koma penutup. Kedua gaya tidak boleh dicampur dalam satu pernyataan, dan satu pernyataan hanya memakai satu gaya dari awal hingga akhir. Untuk menyisipkan banyak baris sekaligus, metode executemany menerima query yang sama beserta daftar tuple atau daftar dictionary nilai, dan contoh di atas mengisi tiga buku dengan satu pemanggilan. Query pada executemany dikompilasi sekali saja sebelum seluruh baris diterapkan, sehingga penyisipan puluhan baris tetap efisien. Selain lebih ringkas, pendekatan berparameter ini menutup celah SQL injection yang dibahas pada topik tentang SQL aman dari kode.

Mengambil baris hasil#

Tiga metode fetch membaca baris dari cursor dengan cara yang berbeda. Metode fetchone mengembalikan satu baris atau None bila hasilnya habis, fetchmany(n) mengembalikan paling banyak n baris, dan fetchall mengembalikan seluruh baris yang tersisa. Cursor berperilaku seperti iterator, sehingga baris yang sudah diambil tidak dapat diambil lagi dari cursor yang sama. Secara bawaan setiap baris berupa tuple yang hanya dirujuk lewat posisi, dan pengaturan row_factory=sqlite3.Row mengubahnya menjadi baris yang kolomnya dapat dirujuk lewat nama.

python

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.row_factory = sqlite3.Row
koneksi.execute("CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, penulis TEXT, tahun INTEGER)")
buku = [
    ("Kisah Kota Tua", "Sari Wulandari", 2018),
    ("Anak Sungai", "Rahmat Hidayat", 2021),
    ("Musim di Ujung Jalan", "Lina Marlina", 2019),
]
koneksi.executemany(
    "INSERT INTO buku (judul, penulis, tahun) VALUES (?, ?, ?)", buku
)
koneksi.commit()

kur = koneksi.execute("SELECT id, judul, tahun FROM buku ORDER BY id")
satu = kur.fetchone()
print(f'fetchone : {satu["judul"]} ({satu["tahun"]})')
banyak = kur.fetchmany(1)
print("fetchmany:", [baris["judul"] for baris in banyak])
sisa = kur.fetchall()
print("fetchall :", len(sisa), "baris tersisa")
koneksi.close()

text Keluaran

fetchone : Kisah Kota Tua (2018)
fetchmany: ['Anak Sungai']
fetchall : 1 baris tersisa

Urutan pemanggilan di atas menghabiskan hasil secara bertahap, yaitu satu baris lewat fetchone, satu baris lewat fetchmany, dan satu baris sisanya lewat fetchall. Hasil kosong tidak memicu error, karena fetchone hanya mengembalikan None dan fetchall mengembalikan daftar kosong, sehingga kedua kasusnya cukup diuji dengan struktur data biasa. Objek sqlite3.Row tetap mendukung rujukan indeks seperti satu[0], tetapi rujukan lewat nama jauh lebih tahan terhadap perubahan urutan kolom pada query. Untuk membaca hasil yang sangat besar, pola perulangan langsung di atas cursor, sebagaimana dilakukan pada fungsi laporan di bagian akhir, menghindari seluruh hasil dimuat ke memori sekaligus.

Transaksi implisit, commit, dan rollback#

Koneksi sqlite3 membuka transaksi implisit secara otomatis sebelum pernyataan yang mengubah data dijalankan, sehingga perubahan belum tetap tersimpan sampai commit() dipanggil. Metode rollback() membatalkan seluruh perubahan sejak transaksi dibuka. Contoh berikut menjalankan dua UPDATE dalam satu transaksi, di mana pernyataan keduanya melanggar batasan CHECK dan memicu error.

python

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute(
    "CREATE TABLE anggota ("
    " id    INTEGER PRIMARY KEY,"
    " nama  TEXT NOT NULL,"
    " denda REAL NOT NULL DEFAULT 0 CHECK (denda >= 0))"
)
koneksi.execute("INSERT INTO anggota (nama) VALUES ('Ali')")
koneksi.commit()

try:
    koneksi.execute("UPDATE anggota SET denda = 5000 WHERE nama = 'Ali'")
    koneksi.execute("UPDATE anggota SET denda = -1000 WHERE nama = 'Ali'")
except sqlite3.Error as e:
    print("error :", e)
    koneksi.rollback()

kur = koneksi.execute("SELECT nama, denda FROM anggota")
print("denda :", kur.fetchone()[1])
koneksi.close()

text Keluaran

error : CHECK constraint failed: denda >= 0
denda : 0.0

Nilai denda kembali 0.0 karena rollback membatalkan UPDATE pertama juga, bukan hanya pernyataan yang gagal. Selama transaksi terbuka, perubahan hanya terlihat oleh koneksi yang membukanya, sedangkan koneksi lain tetap membaca keadaan sebelum perubahan itu. Sifat inilah yang membuat rollback dapat memulihkan seluruh rangkaian pernyataan, bukan hanya yang terakhir. Cara yang lebih ringkas adalah pernyataan with pada objek koneksi, yaitu with koneksi: yang memanggil commit bila blok selesai tanpa error dan rollback bila terjadi error di tengah blok. Perlu dicatat bahwa with pada koneksi hanya mengurus transaksi, tidak menutup koneksi, sehingga pemanggilan close() tetap diperlukan atau penutupannya diambil alih oleh contextlib.closing sebagaimana pada skrip katalog di bawah ini.

Skrip utuh katalog.py#

Skrip berikut menyatukan seluruh mekanisme di atas ke dalam satu program katalog perpustakaan. Tabel dibangun kembali dari awal agar skrip dapat dijalankan berulang kali, data diisi lewat executemany dengan placeholder bernama, laporan disusun dengan JOIN dan dibaca baris demi baris melalui sqlite3.Row, sedangkan koneksi ditutup otomatis oleh contextlib.closing.

python katalog.py

"""Katalog peminjaman buku sederhana dengan modul sqlite3 bawaan."""
import contextlib
import sqlite3


def siapkan_tabel(koneksi):
    koneksi.execute("DROP TABLE IF EXISTS peminjaman")
    koneksi.execute("DROP TABLE IF EXISTS buku")
    koneksi.execute("DROP TABLE IF EXISTS anggota")
    koneksi.execute(
        "CREATE TABLE buku ("
        "  id      INTEGER PRIMARY KEY,"
        "  judul   TEXT NOT NULL,"
        "  penulis TEXT NOT NULL,"
        "  tahun   INTEGER)"
    )
    koneksi.execute(
        "CREATE TABLE anggota ("
        "  id   INTEGER PRIMARY KEY,"
        "  nama TEXT NOT NULL)"
    )
    koneksi.execute(
        "CREATE TABLE peminjaman ("
        "  id         INTEGER PRIMARY KEY,"
        "  buku_id    INTEGER NOT NULL REFERENCES buku(id),"
        "  anggota_id INTEGER NOT NULL REFERENCES anggota(id),"
        "  pinjam     TEXT NOT NULL)"
    )


def isi_data(koneksi):
    buku = [
        {"judul": "Kisah Kota Tua", "penulis": "Sari Wulandari", "tahun": 2018},
        {"judul": "Anak Sungai", "penulis": "Rahmat Hidayat", "tahun": 2021},
        {"judul": "Musim di Ujung Jalan", "penulis": "Lina Marlina", "tahun": 2019},
    ]
    koneksi.executemany(
        "INSERT INTO buku (judul, penulis, tahun)"
        " VALUES (:judul, :penulis, :tahun)",
        buku,
    )
    koneksi.executemany(
        "INSERT INTO anggota (nama) VALUES (?)",
        [("Ali",), ("Budi",)],
    )
    peminjaman = [
        {"buku_id": 1, "anggota_id": 1, "pinjam": "2026-02-10"},
        {"buku_id": 2, "anggota_id": 2, "pinjam": "2026-02-14"},
    ]
    koneksi.executemany(
        "INSERT INTO peminjaman (buku_id, anggota_id, pinjam)"
        " VALUES (:buku_id, :anggota_id, :pinjam)",
        peminjaman,
    )


def laporan(koneksi):
    koneksi.row_factory = sqlite3.Row
    kur = koneksi.execute(
        "SELECT b.judul, b.tahun, a.nama"
        " FROM peminjaman p"
        " JOIN buku b ON b.id = p.buku_id"
        " JOIN anggota a ON a.id = p.anggota_id"
        " ORDER BY p.id"
    )
    for baris in kur:
        print(f'{baris["judul"]} ({baris["tahun"]}) dipinjam oleh {baris["nama"]}')


with contextlib.closing(sqlite3.connect("perpustakaan.db")) as koneksi:
    siapkan_tabel(koneksi)
    isi_data(koneksi)
    koneksi.commit()
    laporan(koneksi)

text Keluaran

Kisah Kota Tua (2018) dipinjam oleh Ali
Anak Sungai (2021) dipinjam oleh Budi

Skrip ini dijalankan dengan perintah python3 katalog.py dari direktori tempat berkasnya berada, dan hasilnya berupa satu baris laporan untuk setiap peminjaman. Eksekusinya juga membuat berkas perpustakaan.db pada direktori kerja, sehingga skrip berulang kali dijalankan tetap menghasilkan data yang sama karena tabelnya dihapus dan dibangun ulang. Urutan DROP pada siapkan_tabel menyebut peminjaman lebih dahulu karena tabel itu mereferensikan dua tabel lainnya, sehingga skrip tetap benar bila penegakan foreign key diaktifkan. Laporan tersebut menggabungkan tiga tabel lewat foreign key yang sudah didefinisikan, kemudian mencetak judul, tahun terbit, dan nama peminjamnya. Apabila salah satu penyisipan pada isi_data gagal, blok pemanggil dapat diperluas dengan try dan rollback sebagaimana pola pada bagian transaksi.

Ringkasan#

sqlite3.connect membuka koneksi ke berkas atau ke :memory:, sedangkan cursor menjalankan SQL dan menyimpan posisi hasil pembacaannya.
Placeholder ? dan :nama mengirim nilai terpisah dari teks query, dan executemany menerapkan query yang sama ke banyak baris sekaligus.
fetchone, fetchmany, dan fetchall membaca hasil secara bertahap, sedangkan row_factory=sqlite3.Row membuat kolom dirujuk lewat nama.
Koneksi membuka transaksi implisit sebelum perubahan data, sehingga commit menetapkan dan rollback membatalkan seluruh perubahannya.

Referensi#

sqlite3 — DB-API 2.0 interface for SQLite databases — Python Software Foundation — Dokumentasi resmi modul sqlite3 yang mencakup placeholder, metode fetch, row_factory, dan kontrol transaksi.