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)
(1, 'Vrtačka', 800) (2, 'Kolečko', 300)
Rozebereme si to:
connectotevře spojení s databází.CREATE TABLEzaloží tabulku. U každého sloupce je typ,NOT NULLzakazuje prázdnou hodnotu.PRIMARY KEYje primární klíč, jednoznačný identifikátor řádku. UINTEGER PRIMARY KEYho SQLite doplní samo.INSERTpřidá řádek acommitzmě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"])
Nůžky na živý plot 900 Vrtačka 800
| Část | Význam |
|---|---|
SELECT nazev, kauce | které sloupce chci |
FROM naradi | ze které tabulky |
WHERE ... | které řádky projdou |
ORDER BY kauce DESC | řazení, DESC je sestupně |
LIMIT 2 | nejvýš 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())
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])
= NULL: 0 IS NULL: 2
Jak najdeš řádky, kde ve sloupci email nic není?
Výraz email = NULL má výsledek NULL, tedy „nevím“, a WHERE takový řádek nepustí. Prázdný text je jiná hodnota než NULL.
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())
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)
[('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())
[('Vrtačka',), ('Svěrák',), ('Žebřík',)]
[('Svěrák',), ('Žebřík',), ('Vrtačka',)]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 []
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)
('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:
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMITKolik 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()
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í.
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čkoimport 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)
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énemINNER JOIN) vrátí jen dvojice, které k sobě našly protějšek.LEFT JOINnechá 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")
bez kontroly projde člen 999
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()
Máš tabulku členů a jejich výpůjček. Chceš vidět i členy, kteří si nikdy nic nepůjčili. Co použiješ?
LEFT JOIN zachová všechny řádky levé tabulky i bez protějšku. Obyčejný JOIN by tyto členy vynechal.
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())
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())
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.
Výpůjčka jako transakce
Napiš funkci vypujc(conn, clen_id, naradi_id), která v jedné transakci:
- ověří, že nářadí není půjčené (v
vypujckynení jeho řádek svraceno IS NULL), jinak vyhodíValueError, - odečte členovi z kreditu kauci nářadí,
- přidá řádek do
vypujckya vrátí jehoid.
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
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 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")
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ří.
Který dotaz nevyužije index na sloupci nazev?
Index je seřazený podle původní hodnoty, ne podle výsledku LOWER. Pomohl by až index nad výrazem: CREATE INDEX ... ON naradi (LOWER(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())
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)
[('ahoj', 'text')]
cannot store TEXT value in INTEGER column prisna.xN+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.