sqlite3 ile Python

Derinleş: sqlite3 ile gerçek uygulama kalıpları

Tablo kurmayı, parametreli sorguyu ve transaction'ı öğrendin. Gerçek bir stok ya da müşteri uygulamasında ise şu sorular gelir: "Eklediğim satırın id'si ne?", "Ürün varsa stoğu artır, yoksa ekle", "Kaç satır güncellendi?", "Sipariş silinmiş müşteriye bağlı kalmasın", "Liste uzunluğu belli olmayan IN sorgusu nasıl yazılır?", "Sorgu neden yavaş?". Bu bölüm bunları yanıtlar. SQL dilinin kendisi (JOIN, GROUP BY, pencere fonksiyonları) için Kesit SQL'e; ORM için sqlalchemy-orm, DataFrame'e okumak için pandas-giris dersine bakabilirsin.

İmlecin verdiği bilgiler: lastrowid, rowcount, fetchmany

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE musteri (id INTEGER PRIMARY KEY, ad TEXT, sehir TEXT)")
cur = con.execute("INSERT INTO musteri (ad, sehir) VALUES (?, ?)", ("Ayşe", "İzmir"))
print("yeni id:", cur.lastrowid)
con.executemany("INSERT INTO musteri (ad, sehir) VALUES (?, ?)", [("Can", "Van"), ("Ece", "Van"), ("Ali", "Bursa")])

cur = con.execute("UPDATE musteri SET sehir = ? WHERE sehir = ?", ("Van Merkez", "Van"))
print("güncellenen satır:", cur.rowcount)

cur = con.execute("SELECT id, ad FROM musteri ORDER BY id")
while parca := cur.fetchmany(2):      # büyük sonuçları parça parça çek
    print("parça:", parca)
print("sütunlar:", [k[0] for k in con.execute("SELECT * FROM musteri").description])

UPSERT: varsa güncelle, yoksa ekle

Depodan gelen stok dosyasında bazı ürünler yeni, bazıları mevcut. Önce SELECT ile bakıp sonra INSERT ya da UPDATE yazmak hem yavaş hem de iki işlem arasında yarış durumuna (race condition) açıktır. SQLite 3.24+ INSERT ... ON CONFLICT ... DO UPDATE sözdizimini destekler; excluded eklenmeye çalışılan satırı temsil eder.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE stok (sku TEXT PRIMARY KEY, ad TEXT, adet INTEGER NOT NULL)")
con.execute("INSERT INTO stok VALUES ('LP-1', 'Laptop', 5)")

gelen = [("LP-1", "Laptop", 3), ("MS-7", "Mouse", 40)]
with con:
    con.executemany("""
        INSERT INTO stok (sku, ad, adet) VALUES (?, ?, ?)
        ON CONFLICT(sku) DO UPDATE SET adet = adet + excluded.adet
    """, gelen)
print(con.execute("SELECT * FROM stok ORDER BY sku").fetchall())

# Yalnızca yoksa ekle, varsa dokunma:
con.execute("INSERT OR IGNORE INTO stok VALUES ('MS-7', 'Mouse', 999)")
print(con.execute("SELECT adet FROM stok WHERE sku = 'MS-7'").fetchone()[0])

Yabancı anahtar, kısıt ve IntegrityError

SQLite geriye uyumluluk için yabancı anahtar (foreign key) denetimini varsayılan olarak kapalı tutar. Her bağlantıda PRAGMA foreign_keys = ON çalıştırmazsan var olmayan müşteriye sipariş yazabilirsin. Kısıt ihlallerinin hepsi sqlite3.IntegrityError olarak gelir; yakalayıp anlamlı mesaja çevir.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
    CREATE TABLE musteri (id INTEGER PRIMARY KEY, eposta TEXT UNIQUE NOT NULL);
    CREATE TABLE siparis (
        id INTEGER PRIMARY KEY,
        musteri_id INTEGER NOT NULL REFERENCES musteri(id) ON DELETE CASCADE,
        tutar REAL CHECK (tutar > 0)
    );
    INSERT INTO musteri (eposta) VALUES ('ayse@ornek.com');
    INSERT INTO siparis (musteri_id, tutar) VALUES (1, 120);
""")
for sql, p in [("INSERT INTO musteri (eposta) VALUES (?)", ("ayse@ornek.com",)),
               ("INSERT INTO siparis (musteri_id, tutar) VALUES (?, ?)", (99, 10)),
               ("INSERT INTO siparis (musteri_id, tutar) VALUES (?, ?)", (1, -5))]:
    try:
        con.execute(sql, p)
    except sqlite3.IntegrityError as e:
        print("Reddedildi:", e)
con.execute("DELETE FROM musteri WHERE id = 1")
print("kalan sipariş:", con.execute("SELECT COUNT(*) FROM siparis").fetchone()[0])

Dinamik IN listesi, indeks ve sorgu planı

Kullanıcı birden çok şehir seçtiğinde IN (?, ?, ?) yer tutucularını listenin uzunluğu kadar üretirsin; değerleri yine parametre olarak verirsin. Sık filtrelenen sütuna indeks eklemek sorguyu tam tablo taramasından (SCAN) indeks aramasına (SEARCH) çevirir; EXPLAIN QUERY PLAN bunu gösterir.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE siparis (id INTEGER PRIMARY KEY, sehir TEXT, tutar REAL)")
con.executemany("INSERT INTO siparis (sehir, tutar) VALUES (?, ?)",
                [("Ankara", 10), ("Van", 20), ("İzmir", 30), ("Ankara", 5)])

secilen = ["Ankara", "İzmir"]
yer = ", ".join("?" * len(secilen))
sql = f"SELECT sehir, SUM(tutar) FROM siparis WHERE sehir IN ({yer}) GROUP BY sehir ORDER BY sehir"
print(sql)
print(con.execute(sql, secilen).fetchall())

plan = lambda: con.execute("EXPLAIN QUERY PLAN SELECT * FROM siparis WHERE sehir = ?", ("Van",)).fetchall()[0][-1]
print("indeks yok:", plan())
con.execute("CREATE INDEX ix_siparis_sehir ON siparis(sehir)")
print("indeks var:", plan())

# Python fonksiyonunu SQL içinde kullan
con.create_function("kdvli", 1, lambda t: round(t * 1.2, 2), deterministic=True)
print(con.execute("SELECT id, kdvli(tutar) FROM siparis ORDER BY id LIMIT 2").fetchall())

Tarihleri saklamak ve dosya veritabanı

SQLite'ın ayrı bir tarih tipi yoktur. Tarihi ISO 8601 metni (2026-03-15, 2026-03-15T14:30:00) olarak sakla: alfabetik sıralama kronolojik sıralamayla aynı olur ve date(), strftime() SQL fonksiyonları çalışır. Python 3.12'de sqlite3'ün varsayılan tarih dönüştürücüleri kullanımdan kaldırıldı (deprecated); dönüşümü kendin açıkça yap. Kendi bilgisayarında gerçek dosyayla çalışırken şu iskeleti kullan:

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("anadolu.db", timeout=10)) as con:
    con.execute("PRAGMA foreign_keys = ON")
    con.execute("PRAGMA journal_mode = WAL")
    with con:                       # transaction: başarıda commit, hatada rollback
        con.execute("INSERT INTO log (zaman, mesaj) VALUES (?, ?)", ("2026-03-15T09:00:00", "başladı"))
    with closing(sqlite3.connect("yedek.db")) as hedef:
        con.backup(hedef)

Komut satırından incelemek için sqlite3 anadolu.db (Windows'ta sqlite.org'dan sqlite-tools indir), içinde .tables, .schema siparis, .mode box komutlarını kullan. Görsel araç istersen "DB Browser for SQLite" ücretsizdir. database is locked hatası alırsan başka bir program (ya da kapanmamış bir bağlantı) yazma kilidini tutuyordur: timeout ver, bağlantıları closing ile kapat, WAL modunu aç.

Derinleş: pencere fonksiyonları ve CTE ile analitik SQL

Derste GROUP BY ile özet çıkarmayı gördün. GROUP BY satırları daraltır: 910 sipariş, kanal başına 3 satıra iner. Analist sorularının çoğu ise satırları koruyup yanına bir özet eklemeyi ister: "her siparişin, müşterinin kaçıncı siparişi olduğu", "her ayın bir önceki aya göre değişimi", "her şehirde en çok harcayan müşteri". Bunun aracı pencere fonksiyonlarıdır (window functions). Uzun sorguları okunur adımlara bölmek için de CTE (common table expression, WITH ... AS) kullanılır. SQLite 3.25'ten beri ikisini de destekler; tarayıcıdaki ve Python'la gelen sqlite3 bu sürümlerin çok üstündedir. Bu bölümde gerçek Anadolu Market verisini veri.baglan ile yükleyeceğiz. Konunun tam hâli Kesit SQL'in analitik SQL derslerindedir; burada Python tarafından nasıl kullanılacağına odaklanıyoruz.

CTE: sorguyu adımlara bölmek

Sipariş tutarı kalemlerden hesaplanır. Bunu her sorguda alt sorgu olarak tekrar yazmak yerine bir kez sip adlı CTE olarak tanımlarız; sonraki adımlar onu tablo gibi kullanır. Birden çok CTE virgülle zincirlenir ve her biri öncekileri görebilir.

import veri

con = veri.baglan("anadolu_market", tablolar=["orders", "order_items", "customers", "products"])
SQL = """
WITH sip AS (
    SELECT o.order_id, o.customer_id, o.order_date, o.channel,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS tutar
    FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'delivered'
    GROUP BY o.order_id
),
aylik AS (
    SELECT substr(order_date, 1, 7) AS ay, ROUND(SUM(tutar), 2) AS ciro
    FROM sip WHERE order_date >= '2024-01-01'
    GROUP BY ay
)
SELECT ay, ciro FROM aylik ORDER BY ciro DESC LIMIT 3
"""
for satir in con.execute(SQL):
    print(satir)

Pencere fonksiyonunun anatomisi

fonksiyon() OVER (PARTITION BY ... ORDER BY ... çerçeve). PARTITION BY pencereyi gruplara böler (GROUP BY gibi ama satırları daraltmadan), ORDER BY pencere içindeki sırayı, çerçeve (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ise hesaba hangi satırların gireceğini belirler.

import veri

con = veri.baglan("anadolu_market", tablolar=["orders", "order_items", "customers", "products"])
SQL = """
WITH sip AS (
    SELECT o.order_id, o.customer_id, o.order_date, o.channel,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS tutar
    FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'delivered'
    GROUP BY o.order_id
),
aylik AS (
    SELECT substr(order_date, 1, 7) AS ay, SUM(tutar) AS ciro
    FROM sip WHERE order_date >= '2024-01-01' GROUP BY ay
)
SELECT ay, ROUND(ciro, 0) AS ciro,
       ROUND(100.0 * (ciro - LAG(ciro) OVER (ORDER BY ay)) / LAG(ciro) OVER (ORDER BY ay), 1) AS degisim,
       ROUND(AVG(ciro) OVER (ORDER BY ay ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS ort3
FROM aylik ORDER BY ay
"""
for satir in con.execute(SQL).fetchall()[-4:]:
    print(satir)

Grup içinde ilk N: CTE + ROW_NUMBER

Pencere fonksiyonları WHERE'den sonra hesaplanır; bu yüzden WHERE sira <= 3 aynı SELECT içinde yazılamaz. Kalıp her zaman aynıdır: sırayı bir CTE'de hesapla, dış sorguda filtrele.

import veri

con = veri.baglan("anadolu_market", tablolar=["orders", "order_items", "customers", "products"])
SQL = """
WITH sip AS (
    SELECT o.order_id, o.customer_id, o.order_date, o.channel,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS tutar
    FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'delivered'
    GROUP BY o.order_id
),
siralama AS (
    SELECT c.city, c.customer_id, ROUND(SUM(s.tutar), 2) AS harcama,
           ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY SUM(s.tutar) DESC) AS sira
    FROM sip s JOIN customers c ON c.customer_id = s.customer_id
    WHERE c.city IS NOT NULL
    GROUP BY c.city, c.customer_id
)
SELECT city, customer_id, harcama FROM siralama WHERE sira = 1
ORDER BY harcama DESC LIMIT 4
"""
print(con.execute(SQL).fetchall())