Modul 9 Struktur Data: Pengantar database dengan SQLite

Database dengan SQLite

Offline di Departemen Matematika

Kembali ke Struktur Data

Agak berbeda dengan beberapa praktikum sebelumnya, kali ini, kita akan membahas tentang database (terkadang disebut “basis data”) menggunakan yang namanya SQLite.

Sebelum kita mulai, singkat cerita:

• Suatu database adalah tempat menyimpan sekumpulan data yang saling berhubungan

• Database biasanya berupa sekumpulan tabel yang saling berkaitan. Jenis database ini disebut relational database

• Untuk mengelola suatu database, digunakan yang namanya database management system (DBMS). Khusus relational database, ada istilah relational database management system (RDBMS)

• Untuk relational database yang tidak terlalu besar, salah satu RDBMS yang sering digunakan adalah SQLite, dan itulah yang kita bahas di sini. Ada juga Microsoft Access

• Untuk penggunaan relational database di server atau internet, apalagi kalau ukuran datanya besar, contoh RDBMS yang biasa digunakan adalah MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, Oracle Database

• SQLite bisa dioperasikan melalui Python, dengan module/package sqlite3 yang harusnya sudah terinstal bersama Python (kalau belum, bisa diinstal dengan pip install sqlite3, tapi jangan lupa conda install sqlite terlebih dahulu kalau menggunakan Anaconda)

• File extension atau akhiran nama file untuk suatu database SQLite bisa berupa .db atau .sqlite atau lebih spesifiknya .sqlite3 (atau yang jarang digunakan: .db3, .s3db, .sl3)

• Pada tiap database, bisa dilakukan yang namanya querying, yaitu melakukan filtering untuk memperoleh data yang memenuhi kriteria yang kita tentukan

• Sudah ada bahasa bernama SQL (Structured Query Language) yang terstandarisasi untuk melakukan querying (maupun modifikasi) pada relational database, apapun RDBMS yang digunakan

import sqlite3

Membuat database dan tabel

Membuat database baru (atau membuka database yang sudah ada, kalau ada) dengan nama file yang ditentukan

conn = sqlite3.connect("pasar.sqlite3")

cur = conn.cursor()

# Menjalankan perintah SQL
cur.execute("""CREATE TABLE IF NOT EXISTS tabel_produk (
    product_id INTEGER PRIMARY KEY NOT NULL,
    nama TEXT,
    stok INTEGER,
    berat_kg REAL,
    harga_ribu_rp REAL
)""")

# Menyimpan hasil eksekusi SQL
conn.commit()

Storage class atau “kategori tipe data” di SQLite ada lima:

• NULL

• INTEGER

• REAL

• TEXT

• BLOB (untuk data lainnya)

# Menutup koneksi
conn.close()

Menambahkan dan melihat data

Menambahkan data ke database

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("INSERT INTO tabel_produk VALUES (123, 'Apel', 10, 0.2, 3.5)")
conn.commit()

cur.execute("INSERT INTO tabel_produk VALUES (456, 'Jeruk', 15, 0.15, 2.1)")
conn.commit()

cur.execute("INSERT INTO tabel_produk VALUES (789, 'Pisang', 40, 0.05, 1.25)")
conn.commit()

conn.close()

Melihat semua data yang ada di database

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
print(cur.fetchall())

conn.close()

[(123, 'Apel', 10, 0.2, 3.5), (456, 'Jeruk', 15, 0.15, 2.1), (789, 'Pisang', 40, 0.05, 1.25)]

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
print(semua_baris)

conn.close()

[(123, 'Apel', 10, 0.2, 3.5), (456, 'Jeruk', 15, 0.15, 2.1), (789, 'Pisang', 40, 0.05, 1.25)]

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(456, 'Jeruk', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)

Melihat nama kolom

Sayangnya, tidak ada cara langsung untuk memperoleh nama kolom. Namun, kita bisa melihat atribut .description pada cursor, yang berisi nama tiap kolom, disertai dengan beberapa data lainnya yang maknanya tidak perlu kita pahami.

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
print(cur.description)

conn.close()

(('product_id', None, None, None, None, None, None), ('nama', None, None, None, None, None, None), ('stok', None, None, None, None, None, None), ('berat_kg', None, None, None, None, None, None), ('harga_ribu_rp', None, None, None, None, None, None))

Dengan list comprehension, kita ambil bagian pertama saja:

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
nama_kolom = [x[0] for x in cur.description]
print(nama_kolom)

conn.close()

['product_id', 'nama', 'stok', 'berat_kg', 'harga_ribu_rp']

Melihat data di kolom-kolom tertentu saja

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT product_id, nama FROM tabel_produk")
print(cur.fetchall())

conn.close()

[(123, 'Apel'), (456, 'Jeruk'), (789, 'Pisang')]

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT nama, stok FROM tabel_produk")
print(cur.fetchall())

conn.close()

[('Apel', 10), ('Jeruk', 15), ('Pisang', 40)]

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT nama, berat_kg, harga_ribu_rp FROM tabel_produk")
print(cur.fetchall())

conn.close()

[('Apel', 0.2, 3.5), ('Jeruk', 0.15, 2.1), ('Pisang', 0.05, 1.25)]

Menambahkan data

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

data_baru = [
    (987, 'Brokoli', 70, 0.05, 1.5),
    (321, 'Wortel', 30, 0.1, 1.8),
    (135, 'Stroberi', 120, 0.04, 2)
]

cur.executemany("INSERT INTO tabel_produk VALUES (?, ?, ?, ?, ?)", data_baru)
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
print(cur.fetchall())

conn.close()

[(123, 'Apel', 10, 0.2, 3.5), (135, 'Stroberi', 120, 0.04, 2.0), (321, 'Wortel', 30, 0.1, 1.8), (456, 'Jeruk', 15, 0.15, 2.1), (789, 'Pisang', 40, 0.05, 1.25), (987, 'Brokoli', 70, 0.05, 1.5)]

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 1.8)
(456, 'Jeruk', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)
(987, 'Brokoli', 70, 0.05, 1.5)

Querying

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE stok < 50")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(321, 'Wortel', 30, 0.1, 1.8)
(456, 'Jeruk', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE harga_ribu_rp > 2")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(456, 'Jeruk', 15, 0.15, 2.1)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE nama == 'Stroberi'")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(135, 'Stroberi', 120, 0.04, 2.0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE product_id == 987")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE product_id LIKE '45%'")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(456, 'Jeruk', 15, 0.15, 2.1)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE product_id LIKE '%9'")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(789, 'Pisang', 40, 0.05, 1.25)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE product_id LIKE '1%'")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE (stok < 50) AND (product_id LIKE '1%')")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE (stok <= 10) OR (harga_ribu_rp > 2)")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 10, 0.2, 3.5)
(456, 'Jeruk', 15, 0.15, 2.1)

Update data

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("UPDATE tabel_produk SET stok = 200 WHERE product_id == 123")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 1.8)
(456, 'Jeruk', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)
(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("UPDATE tabel_produk SET nama = 'Jeruk nipis' WHERE product_id == 456")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 1.8)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)
(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("UPDATE tabel_produk SET harga_ribu_rp = 2.5 WHERE product_id == 321")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(789, 'Pisang', 40, 0.05, 1.25)
(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("UPDATE tabel_produk SET stok = 10 WHERE product_id == 789")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(789, 'Pisang', 10, 0.05, 1.25)
(987, 'Brokoli', 70, 0.05, 1.5)

Hapus baris

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("DELETE FROM tabel_produk WHERE product_id == 789")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(987, 'Brokoli', 70, 0.05, 1.5)

ORDER BY dan LIMIT

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY nama")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(987, 'Brokoli', 70, 0.05, 1.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY nama DESC")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(321, 'Wortel', 30, 0.1, 2.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(987, 'Brokoli', 70, 0.05, 1.5)
(123, 'Apel', 200, 0.2, 3.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY nama ASC")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(987, 'Brokoli', 70, 0.05, 1.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY berat_kg")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(135, 'Stroberi', 120, 0.04, 2.0)
(987, 'Brokoli', 70, 0.05, 1.5)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(123, 'Apel', 200, 0.2, 3.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY harga_ribu_rp")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(987, 'Brokoli', 70, 0.05, 1.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(321, 'Wortel', 30, 0.1, 2.5)
(123, 'Apel', 200, 0.2, 3.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY harga_ribu_rp DESC")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(135, 'Stroberi', 120, 0.04, 2.0)
(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY harga_ribu_rp DESC LIMIT 3")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY product_id LIMIT 3")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk LIMIT 3")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk ORDER BY product_id")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(987, 'Brokoli', 70, 0.05, 1.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(987, 'Brokoli', 70, 0.05, 1.5)

Tabel baru di database yang sama

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS tabel_pesanan (
    order_id INTEGER PRIMARY KEY NOT NULL,
    tanggal TEXT,
    jam TEXT,
    kode_produk INTEGER,
    jumlah INTEGER,
    sudah_dibayar INTEGER,
    FOREIGN KEY(kode_produk) REFERENCES tabel_produk(product_id)
)
""")
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

data_baru = [
    (1, '2023-11-05', '14:38:27', 123, 10, 0),
    (2, '2023-11-16', '09:01:03', 456, 5, 0),
    (3, '2023-11-17', '23:59:58', 987, 15, 0)
]

cur.executemany("INSERT INTO tabel_pesanan VALUES (?,?,?,?,?,?)", data_baru)
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

data_baru = [
    ('2023-11-16', '00:09:32', 456, 20, 0),
    ('2023-11-15', '12:29:17', 135, 5, 0),
    ('2023-11-17', '15:42:19', 321, 10, 0)
]

cur.executemany("""INSERT INTO tabel_pesanan
                (tanggal, jam, kode_produk, jumlah, sudah_dibayar)
                VALUES (?,?,?,?,?)""", data_baru)
conn.commit()

conn.close()

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan ORDER BY tanggal")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan ORDER BY tanggal, jam")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan ORDER BY tanggal DESC, jam DESC")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(3, '2023-11-17', '23:59:58', 987, 15, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(1, '2023-11-05', '14:38:27', 123, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan WHERE jam > '12:00:00'")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan WHERE jam > '12:00:00' ORDER BY tanggal, jam")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)

Querying dan inner join melalui foreign key

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

print("=== Tabel produk ===")
cur.execute("""SELECT * FROM tabel_produk""")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

print("=== Tabel pesanan ===")
cur.execute("""SELECT * FROM tabel_pesanan""")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

=== Tabel produk ===
(123, 'Apel', 200, 0.2, 3.5)
(135, 'Stroberi', 120, 0.04, 2.0)
(321, 'Wortel', 30, 0.1, 2.5)
(456, 'Jeruk nipis', 15, 0.15, 2.1)
(987, 'Brokoli', 70, 0.05, 1.5)
=== Tabel pesanan ===
(1, '2023-11-05', '14:38:27', 123, 10, 0)
(2, '2023-11-16', '09:01:03', 456, 5, 0)
(3, '2023-11-17', '23:59:58', 987, 15, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)
(5, '2023-11-15', '12:29:17', 135, 5, 0)
(6, '2023-11-17', '15:42:19', 321, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_produk WHERE product_id == 456")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(456, 'Jeruk nipis', 15, 0.15, 2.1)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("SELECT * FROM tabel_pesanan WHERE kode_produk == 456")
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(2, '2023-11-16', '09:01:03', 456, 5, 0)
(4, '2023-11-16', '00:09:32', 456, 20, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("""SELECT *
            FROM tabel_produk INNER JOIN tabel_pesanan
            ON tabel_produk.product_id == tabel_pesanan.kode_produk
            """)
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(123, 'Apel', 200, 0.2, 3.5, 1, '2023-11-05', '14:38:27', 123, 10, 0)
(456, 'Jeruk nipis', 15, 0.15, 2.1, 2, '2023-11-16', '09:01:03', 456, 5, 0)
(987, 'Brokoli', 70, 0.05, 1.5, 3, '2023-11-17', '23:59:58', 987, 15, 0)
(456, 'Jeruk nipis', 15, 0.15, 2.1, 4, '2023-11-16', '00:09:32', 456, 20, 0)
(135, 'Stroberi', 120, 0.04, 2.0, 5, '2023-11-15', '12:29:17', 135, 5, 0)
(321, 'Wortel', 30, 0.1, 2.5, 6, '2023-11-17', '15:42:19', 321, 10, 0)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("""SELECT *
            FROM tabel_pesanan INNER JOIN tabel_produk
            ON tabel_pesanan.kode_produk == tabel_produk.product_id
            """)
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

(1, '2023-11-05', '14:38:27', 123, 10, 0, 123, 'Apel', 200, 0.2, 3.5)
(2, '2023-11-16', '09:01:03', 456, 5, 0, 456, 'Jeruk nipis', 15, 0.15, 2.1)
(3, '2023-11-17', '23:59:58', 987, 15, 0, 987, 'Brokoli', 70, 0.05, 1.5)
(4, '2023-11-16', '00:09:32', 456, 20, 0, 456, 'Jeruk nipis', 15, 0.15, 2.1)
(5, '2023-11-15', '12:29:17', 135, 5, 0, 135, 'Stroberi', 120, 0.04, 2.0)
(6, '2023-11-17', '15:42:19', 321, 10, 0, 321, 'Wortel', 30, 0.1, 2.5)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("""SELECT tabel_pesanan.tanggal, tabel_produk.nama, tabel_pesanan.jumlah
            FROM tabel_pesanan INNER JOIN tabel_produk
            ON tabel_pesanan.kode_produk == tabel_produk.product_id
            """)
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

('2023-11-05', 'Apel', 10)
('2023-11-16', 'Jeruk nipis', 5)
('2023-11-17', 'Brokoli', 15)
('2023-11-16', 'Jeruk nipis', 20)
('2023-11-15', 'Stroberi', 5)
('2023-11-17', 'Wortel', 10)

conn = sqlite3.connect("pasar.sqlite3")
cur = conn.cursor()

cur.execute("""SELECT tabel_pesanan.tanggal, tabel_produk.nama, tabel_pesanan.jumlah
            FROM tabel_pesanan INNER JOIN tabel_produk
            ON tabel_pesanan.kode_produk == tabel_produk.product_id
            WHERE tabel_pesanan.jam > '12:00:00'
            """)
semua_baris = cur.fetchall()
for baris in semua_baris:
    print(baris)

conn.close()

('2023-11-05', 'Apel', 10)
('2023-11-17', 'Brokoli', 15)
('2023-11-15', 'Stroberi', 5)
('2023-11-17', 'Wortel', 10)