0 dní v řadě · 0 bodů

Profesionální vývoj · 45 min

SQL a sqlite3

Tabulky, dotazy, spojování dat, transakce a indexy. SQL se naučíš přímo z Pythonu na databázi, kterou nemusíš instalovat.

Hloubka
Úkoly
Lidsky řečeno

Představ si půjčovnu nářadí s kartotékou a zkušeným správcem. Neříkáš mu: „Otevři třetí šuplík, projdi kartičky a každou porovnej.“ Řekneš jen: „Dej mi všechny vrtačky s kaucí do tisíce.“ Správce si sám vymyslí, jak to najde nejrychleji. SQL je jazyk, kterým mluvíš se správcem. Popisuješ, co chceš, ne jak to získat.

Proč nestačí soubor

Seznam v JSON nebo CSV stačí, dokud je malý a pracuje s ním jeden program. Potom přijdou potíže: abys našel jeden řádek, čteš celý soubor. Dva zápisy naráz se můžou přepsat. A když program spadne uprostřed ukládání, zůstane napůl hotová změna.

Relační databáze tohle řeší. Data drží v tabulkách: každý řádek je jedna věc, každý sloupec jedna vlastnost. SQLite je databáze uložená v jednom souboru a Python ji umí bez instalace přes modul sqlite3. Adresa ":memory:" znamená dočasnou databázi v paměti, ideální na zkoušení.

import sqlite3

conn = sqlite3.connect(":memory:")

conn.execute("""
    CREATE TABLE naradi (
        id        INTEGER PRIMARY KEY,
        nazev     TEXT NOT NULL,
        kategorie TEXT NOT NULL,
        kauce     INTEGER NOT NULL
    )
""")

conn.execute("INSERT INTO naradi (nazev, kategorie, kauce) VALUES ('Vrtačka', 'elektro', 800)")
conn.execute("INSERT INTO naradi (nazev, kategorie, kauce) VALUES ('Kolečko', 'zahrada', 300)")
conn.commit()

for radek in conn.execute("SELECT id, nazev, kauce FROM naradi"):
    print(radek)
Výstup
(1, 'Vrtačka', 800)
(2, 'Kolečko', 300)

Rozebereme si to:

  • connect otevře spojení s databází.
  • CREATE TABLE založí tabulku. U každého sloupce je typ, NOT NULL zakazuje prázdnou hodnotu.
  • PRIMARY KEY je primární klíč, jednoznačný identifikátor řádku. U INTEGER PRIMARY KEY ho SQLite doplní samo.
  • INSERT přidá řádek a commit změnu trvale potvrdí.
  • SELECT čte. Výsledek jde procházet cyklem a každý řádek je n-tice.

Klíčová slova se v SQL obvykle píšou velkými písmeny. Databázi je to jedno, člověku to pomáhá číst.

SELECT: ptej se přesně

Další ukázky pracují se šesti kusy nářadí. Řádky přes sqlite3.Row umí přístup podle jména sloupce, což se čte mnohem lépe než radek[2].

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.executescript("""
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kategorie TEXT, kauce INTEGER);
    INSERT INTO naradi (nazev, kategorie, kauce) VALUES
        ('Vrtačka', 'elektro', 800), ('Úhlová bruska', 'elektro', 1200),
        ('Kolečko', 'zahrada', 300), ('Nůžky na živý plot', 'zahrada', 900),
        ('Žebřík', 'dílna', 500), ('Svěrák', 'dílna', 250);
""")

dotaz = """
    SELECT nazev, kauce
    FROM naradi
    WHERE kategorie IN ('elektro', 'zahrada') AND kauce < 1000
    ORDER BY kauce DESC
    LIMIT 2
"""
for r in conn.execute(dotaz):
    print(r["nazev"], r["kauce"])
Výstup
Nůžky na živý plot 900
Vrtačka 800
ČástVýznam
SELECT nazev, kaucekteré sloupce chci
FROM naradize které tabulky
WHERE ...které řádky projdou
ORDER BY kauce DESCřazení, DESC je sestupně
LIMIT 2nejvýš kolik řádků

Ve WHERE funguje =, <>, <, >, AND, OR, NOT, IN (...), BETWEEN 100 AND 500 a LIKE 'V%', kde % znamená cokoli. Změny dělají příkazy UPDATE a DELETE.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kauce INTEGER);
    INSERT INTO naradi (nazev, kauce) VALUES ('Vrtačka', 800), ('Kolečko', 300), ('Svěrák', 250);
""")

zmena = conn.execute("UPDATE naradi SET kauce = kauce + 50 WHERE kauce < 500")
print("upraveno řádků:", zmena.rowcount)

smazani = conn.execute("DELETE FROM naradi WHERE nazev = 'Svěrák'")
print("smazáno řádků:", smazani.rowcount)
conn.commit()

print(conn.execute("SELECT nazev, kauce FROM naradi ORDER BY id").fetchall())
Výstup
upraveno řádků: 2
smazáno řádků: 1
[('Vrtačka', 800), ('Kolečko', 350)]

Prázdná hodnota se v SQL jmenuje NULL a znamená „nevím“. Není to nula ani prázdný text. Porovnání s ní nikdy nevyjde jako pravda, takže existuje zvláštní zápis IS NULL.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, vraceno TEXT);
    INSERT INTO vypujcky (vraceno) VALUES ('2026-03-02'), (NULL), (NULL);
""")

print("= NULL:", conn.execute("SELECT COUNT(*) FROM vypujcky WHERE vraceno = NULL").fetchone()[0])
print("IS NULL:", conn.execute("SELECT COUNT(*) FROM vypujcky WHERE vraceno IS NULL").fetchone()[0])
Výstup
= NULL: 0
IS NULL: 2
Kvíz

Jak najdeš řádky, kde ve sloupci email nic není?

Parametry a SQL injection

Tohle je nejdůležitější část lekce. Hodnoty od uživatele nikdy nevkládej do SQL jako text. Podívej se, co udělá hledání, když někdo místo jména napíše kousek SQL.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT, email TEXT);
    INSERT INTO clenove (jmeno, email) VALUES
        ('Eva', 'eva@example.cz'), ('Marek', 'marek@example.cz'), ('Tereza', 'tereza@example.cz');
""")

vstup = "nikdo' OR '1'='1"

spatne = f"SELECT jmeno, email FROM clenove WHERE jmeno = '{vstup}'"
print(spatne)
print("f-string:", conn.execute(spatne).fetchall())

spravne = "SELECT jmeno, email FROM clenove WHERE jmeno = ?"
print("parametr:", conn.execute(spravne, (vstup,)).fetchall())
Výstup
SELECT jmeno, email FROM clenove WHERE jmeno = 'nikdo' OR '1'='1'
f-string: [('Eva', 'eva@example.cz'), ('Marek', 'marek@example.cz'), ('Tereza', 'tereza@example.cz')]
parametr: []

Útočník změnil smysl dotazu a dostal všechny e-maily. Říká se tomu SQL injection a patří k nejstarším a nejdražším chybám na webu. S parametrem ? se dotaz a data posílají odděleně, takže vstup zůstane obyčejnou hodnotou, ať obsahuje cokoli.

Parametry můžou být i pojmenované. Pro hromadné vkládání slouží executemany.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kauce INTEGER)")

nove = [("Vrtačka", 800), ("Kolečko", 300), ("Svěrák", 250)]
with conn:
    conn.executemany("INSERT INTO naradi (nazev, kauce) VALUES (?, ?)", nove)

radek = conn.execute(
    "SELECT nazev FROM naradi WHERE kauce BETWEEN :od AND :do ORDER BY nazev",
    {"od": 200, "do": 500},
).fetchall()
print(radek)
Výstup
[('Kolečko',), ('Svěrák',)]

Pozor na jedno omezení: parametr může nahradit jen hodnotu, ne jméno tabulky nebo sloupce. Výsledek vypadá nevinně, ale nedělá, co čekáš.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE naradi (nazev TEXT, kauce INTEGER);
    INSERT INTO naradi VALUES ('Vrtačka', 800), ('Svěrák', 250), ('Žebřík', 500);
""")

# ORDER BY ? řadí podle stejného textu 'kauce' u všech řádků, tedy vůbec
print(conn.execute("SELECT nazev FROM naradi ORDER BY ?", ("kauce",)).fetchall())

# správně: seznam povolených sloupců
RAZENI = {"cena": "kauce", "nazev": "nazev"}
pozadavek = "cena"
sloupec = RAZENI[pozadavek]
print(conn.execute(f"SELECT nazev FROM naradi ORDER BY {sloupec}").fetchall())
Výstup
[('Vrtačka',), ('Svěrák',), ('Žebřík',)]
[('Svěrák',), ('Žebřík',), ('Vrtačka',)]
Úkol

Bezpečné hledání

Napiš funkci najdi_naradi(conn, kategorie, max_kauce). Vrátí seznam názvů nářadí z dané kategorie s kaucí nejvýš max_kauce, seřazený od nejnižší kauce. Hodnoty předávej jako parametry.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kategorie TEXT, kauce INTEGER);
    INSERT INTO naradi (nazev, kategorie, kauce) VALUES
        ('Vrtačka', 'elektro', 800), ('Úhlová bruska', 'elektro', 1200),
        ('Kolečko', 'zahrada', 300), ('Nůžky na živý plot', 'zahrada', 900),
        ('Žebřík', 'dílna', 500), ('Svěrák', 'dílna', 250);
""")

def najdi_naradi(conn, kategorie, max_kauce):
    return []
Výstup

Souhrny a spojování tabulek

GROUP BY: počítání ve skupinách

Databáze umí počítat přímo. Agregační funkce COUNT, SUM, AVG, MIN a MAX shrnou víc řádků do jedné hodnoty. S GROUP BY to udělají pro každou skupinu zvlášť.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kategorie TEXT, kauce INTEGER);
    INSERT INTO naradi (nazev, kategorie, kauce) VALUES
        ('Vrtačka', 'elektro', 800), ('Úhlová bruska', 'elektro', 1200),
        ('Kolečko', 'zahrada', 300), ('Nůžky na živý plot', 'zahrada', 900),
        ('Žebřík', 'dílna', 500), ('Svěrák', 'dílna', 250);
""")

dotaz = """
    SELECT kategorie, COUNT(*) AS kusu, SUM(kauce) AS celkem, MAX(kauce) AS nejvyssi
    FROM naradi
    WHERE kauce >= 300
    GROUP BY kategorie
    HAVING COUNT(*) >= 2
    ORDER BY celkem DESC
"""
for radek in conn.execute(dotaz):
    print(radek)
Výstup
('elektro', 2, 2000, 1200)
('zahrada', 2, 1200, 900)

dílna zmizela, protože jí po odfiltrování svěráku zůstal jediný kus. WHERE filtruje řádky před seskupením, HAVING celé skupiny po něm. Dotaz se totiž nevyhodnocuje v tom pořadí, v jakém se píše:

text
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
Úkol

Kolik dní v kategorii

Tabulka vypujcky má sloupce kategorie a dnu. Do proměnné vysledek ulož seznam dvojic (kategorie, soucet_dnu), jen pro kategorie se součtem aspoň 5 dní, seřazený podle součtu sestupně. Použij SQL, ne cyklus v Pythonu.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, kategorie TEXT, dnu INTEGER);
    INSERT INTO vypujcky (kategorie, dnu) VALUES
        ('elektro', 2), ('zahrada', 5), ('elektro', 1), ('dílna', 3),
        ('dílna', 7), ('zahrada', 4), ('malování', 1);
""")

dotaz = "SELECT kategorie, dnu FROM vypujcky"
vysledek = conn.execute(dotaz).fetchall()
Výstup

JOIN: data z víc tabulek

Do výpůjčky nebudeme opisovat jméno člena a název nářadí. Kdyby se Eva přejmenovala, musel bys to opravit na stovce míst. Uložíš jen čísla, která odkazují na řádky jiných tabulek. Takovému sloupci se říká cizí klíč a rozdělení dat tímhle způsobem normalizace. JOIN data při čtení zase spojí.

text
clenove               vypujcky                       naradi
id  jmeno             id  clen_id  naradi_id         id  nazev
1   Eva       <-----  1   1        1          ---->  1   Vrtačka
2   Marek             2   1        2          ---->  2   Kolečko
import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT NOT NULL);
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT NOT NULL);
    CREATE TABLE vypujcky (
        id INTEGER PRIMARY KEY,
        clen_id INTEGER NOT NULL REFERENCES clenove(id),
        naradi_id INTEGER NOT NULL REFERENCES naradi(id),
        dnu INTEGER NOT NULL
    );
    INSERT INTO clenove (jmeno) VALUES ('Eva'), ('Marek'), ('Pavel');
    INSERT INTO naradi (nazev) VALUES ('Vrtačka'), ('Kolečko'), ('Žebřík');
    INSERT INTO vypujcky (clen_id, naradi_id, dnu) VALUES (1, 1, 2), (1, 2, 5), (2, 1, 1);
""")

print("INNER JOIN")
dotaz = """
    SELECT c.jmeno, n.nazev, v.dnu
    FROM vypujcky AS v
    JOIN clenove AS c ON c.id = v.clen_id
    JOIN naradi AS n ON n.id = v.naradi_id
    ORDER BY v.id
"""
for r in conn.execute(dotaz):
    print(" ", r)

print("LEFT JOIN")
dotaz = """
    SELECT c.jmeno, COUNT(v.id) AS pocet
    FROM clenove AS c
    LEFT JOIN vypujcky AS v ON v.clen_id = c.id
    GROUP BY c.id
    ORDER BY c.id
"""
for r in conn.execute(dotaz):
    print(" ", r)
Výstup
INNER JOIN
  ('Eva', 'Vrtačka', 2)
  ('Eva', 'Kolečko', 5)
  ('Marek', 'Vrtačka', 1)
LEFT JOIN
  ('Eva', 2)
  ('Marek', 1)
  ('Pavel', 0)
  • JOIN (celým jménem INNER JOIN) vrátí jen dvojice, které k sobě našly protějšek.
  • LEFT JOIN nechá všechny řádky z levé tabulky. Kde protějšek chybí, doplní NULL. Díky tomu se ve výsledku objevil i Pavel s nulou.

SQLite navíc ve výchozím stavu cizí klíče nevynucuje. Musíš o to požádat u každého spojení.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT);
    CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, clen_id INTEGER REFERENCES clenove(id));
""")

conn.execute("INSERT INTO vypujcky (clen_id) VALUES (999)")
print("bez kontroly projde člen 999")
conn.rollback()

conn.execute("PRAGMA foreign_keys = ON")
conn.execute("INSERT INTO vypujcky (clen_id) VALUES (999)")
print("sem se nedostaneme")
Výstup
bez kontroly projde člen 999
Úkol

Kdo má co vrátit

Do proměnné vysledek ulož seznam dvojic (jmeno, pocet) pro všechny členy, kde pocet je počet jejich nevrácených výpůjček (vraceno IS NULL). Členové bez nich mají 0. Seřaď podle jména.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT);
    CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, clen_id INTEGER, vraceno TEXT);
    INSERT INTO clenove (jmeno) VALUES ('Eva'), ('Marek'), ('Pavel'), ('Tereza');
    INSERT INTO vypujcky (clen_id, vraceno) VALUES
        (1, '2026-03-02'), (1, NULL), (1, NULL), (2, NULL), (4, '2026-04-01');
""")

vysledek = conn.execute("SELECT jmeno, 0 FROM clenove").fetchall()
Výstup
Kvíz

Máš tabulku členů a jejich výpůjček. Chceš vidět i členy, kteří si nikdy nic nepůjčili. Co použiješ?

Pod kapotou · NULL a trojhodnotová logika

SQL má tři pravdivostní hodnoty: pravda, nepravda a neznámo. WHERE pustí jen pravdu. Nejzákeřnější důsledek je NOT IN nad množinou obsahující NULL. Výraz x NOT IN (1, NULL) znamená x <> 1 AND x <> NULL a druhá část je vždy neznámá.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT);
    CREATE TABLE vypujcky (clen_id INTEGER);
    INSERT INTO clenove (jmeno) VALUES ('Eva'), ('Pavel');
    INSERT INTO vypujcky VALUES (1), (NULL);
""")

not_in = "SELECT jmeno FROM clenove WHERE id NOT IN (SELECT clen_id FROM vypujcky)"
not_exists = """
    SELECT jmeno FROM clenove AS c
    WHERE NOT EXISTS (SELECT 1 FROM vypujcky AS v WHERE v.clen_id = c.id)
"""
print("NOT IN:    ", conn.execute(not_in).fetchall())
print("NOT EXISTS:", conn.execute(not_exists).fetchall())
Výstup
NOT IN:     []
NOT EXISTS: [('Pavel',)]

Proto u poddotazů preferuj NOT EXISTS. Další důsledky: UNIQUE připouští víc NULL, SUM z prázdné množiny vrací NULL (ošetři přes COALESCE(SUM(x), 0)) a pro porovnání „včetně NULL“ má SQLite operátor IS, ve standardu je to IS NOT DISTINCT FROM.

SQLite dovoluje v SELECT sloupce, které nejsou v GROUP BY ani v agregaci, a vrátí hodnotu z libovolného řádku skupiny. PostgreSQL takový dotaz odmítne, takže kód přenesený z SQLite často spadne až při migraci.

Transakce: všechno, nebo nic

Člen si převádí kredit na kamaráda. Jde o dvě změny: jednomu odečíst, druhému přičíst. Kdyby program spadl mezi nimi, peníze zmizí nebo se objeví z ničeho. Transakce spojí příkazy do jednoho celku, který proběhne celý, nebo vůbec. V Pythonu ji pohodlně obstará with conn:. Při úspěchu udělá commit, při výjimce rollback, tedy návrat zpět.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (
        id INTEGER PRIMARY KEY,
        jmeno TEXT NOT NULL,
        kredit INTEGER NOT NULL CHECK (kredit >= 0)
    );
    INSERT INTO clenove (jmeno, kredit) VALUES ('Eva', 100), ('Marek', 20);
""")

def preved(conn, od, komu, castka):
    with conn:
        conn.execute("UPDATE clenove SET kredit = kredit + ? WHERE id = ?", (castka, komu))
        conn.execute("UPDATE clenove SET kredit = kredit - ? WHERE id = ?", (castka, od))

def stav():
    return conn.execute("SELECT jmeno, kredit FROM clenove ORDER BY id").fetchall()

preved(conn, 1, 2, 30)
print("po převodu:", stav())

try:
    preved(conn, 1, 2, 500)
except sqlite3.IntegrityError as chyba:
    print("odmítnuto:", chyba)

print("po chybě: ", stav())
Výstup
po převodu: [('Eva', 70), ('Marek', 50)]
odmítnuto: CHECK constraint failed: kredit >= 0
po chybě:  [('Eva', 70), ('Marek', 50)]

Při druhém převodu se Markovi kredit nejdřív přičetl. Odečtení pak porušilo pravidlo CHECK (kredit >= 0) a transakce vrátila všechno zpátky. Pravidlo v databázi navíc platí pro každý program, který do ní zapisuje, nejen pro ten tvůj.

Úkol

Výpůjčka jako transakce

Napiš funkci vypujc(conn, clen_id, naradi_id), která v jedné transakci:

  1. ověří, že nářadí není půjčené (v vypujcky není jeho řádek s vraceno IS NULL), jinak vyhodí ValueError,
  2. odečte členovi z kreditu kauci nářadí,
  3. přidá řádek do vypujcky a vrátí jeho id.

Když kredit nestačí, CHECK vyhodí sqlite3.IntegrityError. Výjimku nech projít, ale v databázi nesmí zůstat žádná změna.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE clenove (id INTEGER PRIMARY KEY, jmeno TEXT, kredit INTEGER CHECK (kredit >= 0));
    CREATE TABLE naradi (id INTEGER PRIMARY KEY, nazev TEXT, kauce INTEGER);
    CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, clen_id INTEGER, naradi_id INTEGER, vraceno TEXT);
    INSERT INTO clenove (jmeno, kredit) VALUES ('Eva', 1000), ('Marek', 100);
    INSERT INTO naradi (nazev, kauce) VALUES ('Vrtačka', 800), ('Svěrák', 250);
""")

def vypujc(conn, clen_id, naradi_id):
    pass
Výstup
Pod kapotou · transakce v sqlite3 a Pythonu 3.12

Modul má historicky svérázné chování. Ve výchozím režimu sám otevře transakci před INSERT, UPDATE, DELETE a REPLACE, ale ne před SELECT ani CREATE. Python 3.12 přidal atribut autocommit. Dokumentací doporučené nastavení autocommit=False se chová podle PEP 249: transakce je otevřená pořád a končí jen commit() nebo rollback(). DDL je v SQLite transakční, takže jde vrátit i CREATE TABLE.

import sqlite3

conn = sqlite3.connect(":memory:", autocommit=False)
print("v transakci:", conn.in_transaction)

conn.execute("CREATE TABLE t (x)")
conn.rollback()

pocet = conn.execute("SELECT COUNT(*) FROM sqlite_master WHERE name = 't'").fetchone()[0]
print("tabulek t po rollbacku:", pocet)
Výstup
v transakci: True
tabulek t po rollbacku: 0

Souběh. SQLite má v jednu chvíli jediného zapisujícího. PRAGMA journal_mode = WAL umožní čtenářům běžet souběžně se zápisem a je rozumným výchozím nastavením pro souborovou databázi. Parametr timeout v connect (výchozích 5 s) určuje, jak dlouho se čeká na zámek. Pozor na transakci, která nejdřív čte a pak zapisuje: pokud mezitím zapsal někdo jiný, dostaneš database is locked okamžitě, bez čekání. Řešení je začít zápisovou transakci rovnou přes BEGIN IMMEDIATE.

Vzor „zkontroluj a pak zapiš“ z předchozího úkolu je pod souběhem na serverových databázích závod. Bezpečnější je nechat rozhodnout databázi: částečný unikátní index CREATE UNIQUE INDEX jedna_vypujcka ON vypujcky(naradi_id) WHERE vraceno IS NULL druhou výpůjčku odmítne bez ohledu na časování.

Data a časy. Výchozí adaptéry pro datetime jsou od Pythonu 3.12 zastaralé. Ukládej text v ISO 8601 přes .isoformat(), nejlépe v UTC, a převádění měj ve svém kódu.

Indexy: proč je dotaz pomalý

Bez pomoci musí databáze projít celou tabulku, stejně jako bys v knize hledal slovo stránku po stránce. Index je rejstřík na konci knihy: seřazený seznam hodnot s odkazy na řádky. Co s dotazem databáze zamýšlí, prozradí EXPLAIN QUERY PLAN.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE vypujcky (id INTEGER PRIMARY KEY, clen_id INTEGER, dnu INTEGER)")
conn.executemany(
    "INSERT INTO vypujcky (clen_id, dnu) VALUES (?, ?)",
    ((i % 500, i % 14) for i in range(5000)),
)

def plan(sql):
    for radek in conn.execute("EXPLAIN QUERY PLAN " + sql):
        print("  ", radek[3])

print("bez indexu")
plan("SELECT * FROM vypujcky WHERE clen_id = 7")

conn.execute("CREATE INDEX idx_vypujcky_clen ON vypujcky (clen_id)")

print("s indexem")
plan("SELECT * FROM vypujcky WHERE clen_id = 7")

print("výpočet nad sloupcem")
plan("SELECT * FROM vypujcky WHERE clen_id + 1 = 8")
Výstup
bez indexu
   SCAN vypujcky
s indexem
   SEARCH vypujcky USING INDEX idx_vypujcky_clen (clen_id=?)
výpočet nad sloupcem
   SCAN vypujcky

SCAN znamená průchod celou tabulkou, SEARCH cílené hledání. Poslední dotaz index nepoužil: po výpočtu nad sloupcem už seřazený rejstřík nepomůže. Stejně dopadne WHERE LOWER(jmeno) = ? nebo LIKE '%text'.

Index není zadarmo. Zabírá místo a každý zápis ho musí aktualizovat. Přidávej ho na sloupce, podle kterých opravdu hledáš, spojuješ nebo řadíš, a cizí klíče mezi ně obvykle patří.

Kvíz

Který dotaz nevyužije index na sloupci nazev?

Pod kapotou · plány, stránkování a užitečné SQL

Složený index (clen_id, vraceno) funguje jako telefonní seznam řazený podle příjmení a jména: pomůže hledání podle prvního sloupce i podle obou, ale ne podle samotného druhého. Pokud index obsahuje všechny potřebné sloupce, plán ukáže COVERING INDEX a tabulka se vůbec nečte. Po větších změnách dat spusť ANALYZE, ať má plánovač statistiky.

Stránkování přes OFFSET zpomaluje s každou další stránkou, protože přeskočené řádky se stejně musí projít, a při vkládání se položky posouvají. Lepší je keyset: pamatuješ si poslední klíč. Následující ukázka spojuje několik běžně přehlížených věcí: upsert s RETURNING, okenní funkci, CTE a keyset.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE skore (hrac TEXT PRIMARY KEY, body INTEGER NOT NULL)")

UPSERT = """
    INSERT INTO skore (hrac, body) VALUES (?, ?)
    ON CONFLICT (hrac) DO UPDATE SET body = body + excluded.body
    RETURNING body
"""
celkem = {}
for hrac, body in [("Eva", 10), ("Marek", 7), ("Eva", 5), ("Pavel", 12), ("Tereza", 7)]:
    celkem[hrac] = conn.execute(UPSERT, (hrac, body)).fetchone()[0]
conn.commit()
print("Eva po dvou zápisech:", celkem["Eva"])

ZEBRICEK = """
    WITH poradi AS (
        SELECT hrac, body, RANK() OVER (ORDER BY body DESC) AS misto
        FROM skore
    )
    SELECT misto, hrac, body FROM poradi ORDER BY misto, hrac
"""
print(conn.execute(ZEBRICEK).fetchall())

DALSI = "SELECT hrac FROM skore WHERE hrac > ? ORDER BY hrac LIMIT 2"
print(conn.execute(DALSI, ("",)).fetchall())
print(conn.execute(DALSI, ("Marek",)).fetchall())
Výstup
Eva po dvou zápisech: 15
[(1, 'Eva', 15), (2, 'Pavel', 12), (3, 'Marek', 7), (3, 'Tereza', 7)]
[('Eva',), ('Marek',)]
[('Pavel',), ('Tereza',)]

Typy v SQLite jsou jen doporučení. Sloupec má afinitu a do INTEGER se klidně vejde text. Tabulka STRICT to zakáže:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE volna (x INTEGER)")
conn.execute("CREATE TABLE prisna (x INTEGER) STRICT")

conn.execute("INSERT INTO volna VALUES ('ahoj')")
print(conn.execute("SELECT x, typeof(x) FROM volna").fetchall())

try:
    conn.execute("INSERT INTO prisna VALUES ('ahoj')")
except sqlite3.IntegrityError as chyba:
    print(chyba)
Výstup
[('ahoj', 'text')]
cannot store TEXT value in INTEGER column prisna.x

N+1 dotazů je klasická past ORM: načteš 100 členů a pro každého samostatným dotazem jeho výpůjčky. V SQLite v procesu to skoro nebolí, po síti k PostgreSQL je to 101 síťových cest. Řeší to JOIN nebo WHERE clen_id IN (...).

Přechod na server. Rozhraní DB-API (PEP 249) je podobné, ale liší se zástupné znaky: sqlite3 má ?, psycopg %s. Na serveru přibude pool spojení, úrovně izolace a migrace schématu (v Pythonu typicky Alembic). execute v sqlite3 odmítne více příkazů najednou, ale to před injection nechrání: UNION SELECT se vejde do jediného.

Vyzkoušej si: JOINy na dvou tabulkách

Přepínej INNER, LEFT, RIGHT a FULL a sleduj, které řádky se spojí a které ne.