Relační Databáze
Většina aplikací, zejména těch webových, potřebuje manipulovat s daty. Může se jednat třeba o správu objednávek, produktů na eshopu nebo bankovních transakcí.
Vzhledem k tomu, že databáze jsou velice komplexní téma, tak si v této lekci projedeme jen základy. Pokud vás ale databáze zaujmou, tak je dobré se podívat po dalších materiálech, například na W3Schools.
Nad daty provádíme čtyři druhy operací - vytváření, čtení, úpravu a mazání. Tyto operace se sdružují do zkratky CRUD (Create, Read, Update, Delete).
Existují různé možnosti, kam data ukládat. Mohli bychom například vše zapisovat do jednoho souboru. Brzy bychom ale zjistili, že se jedná o velice neefektivní způsob - operace nad daty by s rostoucím počtem záznamů začaly být pomalé. V těchto situacích nastupují na scénu databáze (resp. databázové systémy).
Jedná se o vysoce sofistikovaná řešení různých výrobců (zmínit můžeme třeba Oracle nebo MySQL). Výhodou je, že tyto systémy mají společný způsob, jakým se s nimi komunikuje (API) - prostřednictvím jazyka SQL. Nemusí nás tedy zajímat, jak databáze funguje uvnitř.
Základem relačních databází jsou tabulky. Můžeme si je představit jako jeden list sešitu MS Excel (celý sešit by pak odpovídal databázi). Jednotlivé řádky tabulky jsou záznamy. Ty se skládají z několika atributů (sloupců). Řádky a sloupce odpovídají Excelovým řádkům a sloupcům.
Můžeme mít například tabulku OBJEDNAVKA. Bude tvořena sloupci ID_OBJEDNAVKY,
ID_ZAKAZNIKA, DATUM, CENA. Jednotlivé záznamy představují jednotlivé
objednávky - víme tedy kdy si kdo udělal objednávku, a kolik za ni zaplatil.
SQL
SQL (Structured Querying Language - Strukturovaný dotazovací jazyk) nám umožňuje komunikovat s databází. Výrobci jednolivých databází obvykle jazyk v některých oblastech rozšiřují, základ je ale společný a standardzivaný. Kromě práci s daty (CRUD) se jazyk používá pro vytváření tabulek a dalších objektů, pro správu uživatelů databáze apod.
SQL příkazy mohou být do databáze posílány z webové aplikace (Python, Javascript, ...),
z terminálu, nebo třeba z aplikace přímo určené pro správu databáze.
Je zvykem jednotlivé příkazy jazyka psát VELKÝMI PÍSMENY. Příkazy oddělujeme
středníkem ;. Jednořádkové komentáře uvozujeme dvěma spojovníky --
a víceřádkové komentáře píšeme mezi /* a */.
Pojďme se podívat na některé z příkazů, které SQL podporuje.
Tabulky
Pro vytvoření tabulky použijeme příkaz CREATE TABLE, následovaný jménem tabulky
a definicí jednotlivých sloupců. U sloupců definuje jméno, typ a případně další
vlastnosti.
/* Vytvoří tabulku ROBOT se sloupci ID, NAME a TYPE.
ID je celé číslo, NAME a TYPE jsou řetězce.
*/
CREATE TABLE ROBOT (
ID INT PRIMARY KEY,
NAME TEXT,
TYPE TEXT
)
Význam PRIMARY_KEY si vysvětlíme později. Pokud bychom chtěli tabulku smazat,
tak použijeme DROP TABLE JMENO_TABULKY. Jen pozor - spolu s tabulkou se
smažou všechny záznamy v ní obsažené.
INSERT
Jakmile máme vytvořenou tabulku, můžeme do ní vkládat záznamy. Děláme tak
pomocí příkazu INSERT. Uvedeme jméno tabulky a pak hodnoty, představující
záznam (nebo záznamy), které se mají vložit.
-- Vloží do tabulky ROBOT nového Robota s ID 1, jménem Jim a typem AGGRESIVE
INSERT INTO ROBOT (ID, NAME, TYPE) VALUES (1, "Jim", "AGGRESSIVE");
-- Vloží do tabulky ROBOT další dva Roboty
INSERT INTO ROBOT (ID, NAME, TYPE) VALUES (2, "John", "DEFENSIVE"),(3, "Jack", "DEFENSIVE");
SELECT
Když už máme i nějaké zaznámy, tak je dobré mít způsob, jak je číst. Slouží
k tomu mocný příkaz SELECT, který v nejzákladnější podobě očekává seznam sloupců,
který se má pro jednotlivé záznamy vypsat, a jméno tabulky, ze které se mají
data číst. Seznam sloupců můžeme nahradit hvězdičkou *, pokud chceme vypsat
hodnoty všech sloupců.
-- vypíše hodnoty všech sloupců pro všechny roboty
SELECT * FROM ROBOT;
-- vypíše jen jména všech robotů
SELECT NAME FROM ROBOT;
Příkaz SELECT podporuje třeba také filtrování dat (ukážeme si později),
řazení (ORDER BY) a seskupování dat (průměr - AVG, suma - SUM, ...)
UPDATE
V případě, že chceme data upravit (například změnit jméno robota),
tak použijeme příkaz UPDATE. I ten očekává jméno tabulky, jejíž záznamy se
mají upravit. Kromě toho také zadáme nové hodnoty pro jednotlivé sloupce.
Hodnoty sloupců, které neuvedeme, zůstanou nezměněné.
-- nastaví všem robotům typ na AGGRESSIVE
UPDATE ROBOT SET TYPE = "AGGRESSIVE";
DELETE
A konečně poslední oprací v CRUD je mazání. V SQL se záznamy mažou pomocí příkazu
DELETE. I zde, stejně jako u ostatních CRUD příkazů, uvádíme jméno tabulky.
-- smaže všechny roboty
DELETE FROM ROBOT;
WHERE
Nejspíš vás napadalo, že pokud bychom vždy četli, upravovali nebo mazali všechna
data v tabulce, tak by systém nebyl moc dobře použitelný. Naštěstí ale můžeme
pomocí příkazu WHERE ovlivnit, nad jakými záznamy se bude operace provádět.
Za WHERE se píší podmínky (spojované pomocí AND nebo OR), které musí
platit, aby se záznam přidal do množiny, nad kterou se bude operace
provádět. V podmínkách je možné použít různé operátory:
- rovnost:
WHERE SLOUPEC = HODNOTA - nerovnost:
WHERE SLOUPEC > HODNOTA(>,<,<>, ...) - jedna z hodnot:
WHERE SLOUPEC IN (HODNOTA_1, HODNOTA_2) - podřetězec:
WHERE SLOUPEC LIKE %HODNOTA%
-- vypíše všechny řádky o robotech, které mají typ AGGRESSIVE
SELECT * FROM ROBOT WHERE TYPE = "AGGRESSIVE";
-- přejmenuje roboty, jejichž jména začínají na J, na Jimmy
UPDATE ROBOT SET NAME = "Jimmy" WHERE NAME LIKE "J%";
-- smaže robota s ID větším než 1
DELETE FROM ROBOT WHERE ID > 1;
Primární klíče
Každá tabulka by měla mít soupec, který jednoznačně identifikuje jednotlivé řádky.
Ve sloupci musí být unikátní hodnoty. Může se jednat o uměle vytvořené číslo
(nejčastěji nazývané ID), nebo může jít o unikátní identifikátor z reálného
světa. Pozor ale na to, že ne každý na první pohled unikátní identifikátor je
skutečně unikátní - je například možné, aby dvě osoby měly stejné rodné číslo.
Je tedy obecně lepší používat uměle vytvořené primární klíče.
Primární klíče se používají proto, že zrychlují operace nad daty (rychle se podle
nich vyhledává) a také proto, že snižují riziko duplicitních dat v databázi.
Při vytváření tabulky zadáme u sloupce, který má být primárním klíčem, vlastnost
PRIMARY KEY.
Cizí klíče
Cizí klíče - FOREIGN KEY - se využívají pro zachycení vazby mezi tabulkami.
V dceřiné tabulce máme sloupec představující cizí klíč, jehož hodnoty se
odkazují na primární klíč jiné tabulky.
Například tabulka OBJEDNAVKA má cizí klíč ID_ZAKAZNIKA. Tento cizí klíč
se odkazuje na primární klíč tabulky ZAKAZNIK. Tím zaručíme, že každá objednávka
musí být "napojená" na existujícího zákazníka.
Spojování tabulek
Často potřebujeme v jednom dotazu číst data z více tabulek. Používáme k tomu
příkaz JOIN, který je součástí příkazu SELECT. V základní verzi vezme
JOIN záznamy z jedné tabulky a připojí k nim odpovídající záznamy z druhé
tabulky. Na takto nově vzniklý řádek je možné udělat JOIN s další tabulkou.
To, co považujeme za "odpovídající záznam" zapíšeme jako součást JOIN
(za klíčové slovo ON). Nejdříve ale musíme zadat jméno tabulky,
kterou chceme připojit.
-- vypíše jména a příjmení zákazníků, kteří udělali objednávku za více než 500 Kč
SELECT ZAKAZNIK.JMENO, ZAKAZNIK.PRIJMENI
FROM ZAKAZNIK
JOIN OBJEDNAVKA ON OBJEDNAVKA.ZAKAZNIK_ID = ZAKAZNIK.ID
WHERE OBJEDNAVKA.CENA > 500;
Transakce
Transakce jsou skupiny příkazů (SELECT, INSERT, ...), která se buď provede
celá, nebo vůbec. To má velkou výhodu v tom, že pokud některý z příkazů vyvolá
chybu, tak se databáze vrátí do původního stavu, my můžeme příkaz opravit
a celou transakci spustit znovu.
O transakcích se ale bavíme především proto, že je důležité je ukončit,
a to buď příkazem COMMIT nebo ROLLBACK. COMMIT uloží námi provedené
změny trvale do databáze, takže je uvidíme i v budoucích transakcích.
ROLLBACK vrátí databázi do původního stavu, což se může hodit v případě,
že jsme provedli jiné změny, než jsme chtěli.
-- vložení dat
INSERT INTO ROBOT (ID, NAME, TYPE) VALUES(1, "Jim", "AGGRESSIVE");
-- v této chvíli ještě nejsou data trvale uložena
-- uložení dat
COMMIT;
SQLite
Celé povídání o databázích by nemělo moc smysl, pokud bychom si neukázali, jak je využít z prostředí Pythonu. Jak jsme už zmínili, existují různé databázové systémy, my se ale zaměříme na jeden z nejjednodušších na "rozjetí", a to SQLite.
Tento systém ukládá celou databázi do jednoho binárního souboru.
Můžeme si na něm jednoduše vyzkoušet jednotlivé SQL příkazy,
a to prostřednictvím Python balíčku sqlite3. Ukažme si využití tohoto balíčku
na příkladech.
import sqlite3
# Připojíme se k databázi (v souboru)
connection = sqlite3.connect('pyladies_example_1.db')
# Získáme instanci třídy `Cursor`, pomocí které bude do databáze posílat příkazy
cursor = connection.cursor()
# Pokud tabulka už existuje, tak ji odstraníme,
# abychom mohli skript spouštět opakovaně
cursor.execute("""DROP TABLE IF EXISTS ROBOT""")
# Vytvoříme jednoduchou tabulku
cursor.execute("""CREATE TABLE ROBOT (NAME TEXT, TYPE TEXT)""")
# Vložíme do tabulky data
cursor.execute("""
INSERT INTO ROBOT (NAME, TYPE)
VALUES ("JIM", "DEFENSIVE"), ("JACK", "OFFENSIVE")
""")
# Dotážeme se na všechny roboty, výsledky vypíšeme
robots = cursor.execute("SELECT * FROM ROBOT")
for robot in robots:
print(robot)
# Uložíme změny a uzavřeme spojení
connection.commit()
connection.close()
# Složitejší příklad, který pracuje s primárními a cizími klíči
# a se spojováním tabulek
import sqlite3
# Připojíme se k databázi (v souboru)
connection = sqlite3.connect('pyladies_example_2.db')
# Získáme instanci třídy `Cursor`, pomocí které bude do databáze posílat příkazy
cursor = connection.cursor()
# Pokud tabulky už existují, tak ji odstraníme,
# abychom mohli skript spouštět opakovaně
cursor.execute("""DROP TABLE IF EXISTS ROBOT""")
cursor.execute("""DROP TABLE IF EXISTS BATTLE""")
# Vytvoříme tabulku s roboty a tabulky s výsledky bitev
cursor.execute("""
-- u jednotlivých roborů si ukládáme ID, jméno a typ
CREATE TABLE ROBOT (
ROBOT_ID INT PRIMARY KEY,
NAME TEXT,
TYPE TEXT)
""")
cursor.execute("""
-- bitva se skládá z ID bitvy, ID vítěze a poraženého (odpovídají ID v tabulce ROBOT)
-- a z bodů pro vítěze a poraženého
CREATE TABLE BATTLE (
BATTLE_ID INT PRIMARY KEY,
WINNER_ID INT,
LOSER_ID INT,
WINNER_POINTS INT,
LOSER_POINTS INT,
FOREIGN KEY(WINNER_ID) REFERENCES ROBOT(ROBOT_ID),
FOREIGN KEY(LOSER_ID) REFERENCES ROBOT(ROBOT_ID)
)
""")
# Vložíme do tabulkek data
cursor.execute("""
INSERT INTO ROBOT (ROBOT_ID, NAME, TYPE) VALUES
(1, "JIM", "DEFENSIVE"), (2, "JACK", "OFFENSIVE"), (3, "JIMMY", "OFFESIVE")
""")
cursor.execute("""
INSERT INTO BATTLE (BATTLE_ID, WINNER_ID, LOSER_ID, WINNER_POINTS, LOSER_POINTS) VALUES
(1, 1, 2, 10, 8), -- robot 1 porazil robota 2 se skóre 10:8 (v bitvě 1)
(2, 2, 1, 6, 9),
(3, 2, 3, 10, 9),
(4, 1, 3, 5, 4),
(5, 3, 2, 2, 0),
(6, 1, 2, 9, 6)
""")
# Dotážeme se na výsledky bitev, které vyhrál robot se jménem "JIM"
scores = cursor.execute("""
SELECT BATTLE.WINNER_POINTS, BATTLE.LOSER_POINTS
FROM BATTLE
JOIN ROBOT ON ROBOT.ROBOT_ID = BATTLE.WINNER_ID
WHERE ROBOT.NAME = "JIM"
""")
for score in scores:
print(score)
# Uložíme změny a uzavřeme spojení
connection.commit()
connection.close()
ORM
V Pythonu jsme se naučili data a logiku sdružovat do tříd. V databázích se data sdružují do tabulek. O propojení těchto konceptů se stará ORM - Objektově Relační Mapování. Pomocí ORM Frameworku (v Pythonu např. SQLAlchemy) vytváříme Python třídy, pro které existují odpovídající tabulky v databázi.
Například Python třída Kocka bude mít odpovídající tabulku KOCKA.
Atributy třídy (Vek, Barva) budou v databázi existovat jako sloupce.
Jednotlivé řádky tabulky bude možné načíst do aplikace jako instance třídy Kocka.
Jednotlivé kočky samozřejmě bude možné upravovat, mazat, nebo vytvářet nové.
Ukázku ORM najdete na Wikipedii
{
"data": {
"sessionMaterial": {
"id": "session-material:2019/brno-jaro-2019-ut:github-api:2",
"title": "Úvod do Relačních databází",
"html": "\n \n \n\n <h1>Relační Databáze</h1>\n<p>Většina aplikací, zejména těch webových, potřebuje manipulovat s daty.\nMůže se jednat třeba o správu objednávek, produktů na eshopu nebo bankovních transakcí.</p>\n<p>Vzhledem k tomu, že databáze jsou velice komplexní téma, tak si v této lekci\nprojedeme jen základy. Pokud vás ale databáze zaujmou, tak je dobré se podívat\npo dalších materiálech, například na <a href=\"https://www.w3schools.com/sql/\">W3Schools</a>.</p>\n<p>Nad daty provádíme čtyři druhy operací - vytváření, čtení, úpravu a mazání.\nTyto operace se sdružují do zkratky <strong>CRUD</strong> (Create, Read, Update, Delete).</p>\n<p>Existují různé možnosti, kam data ukládat. Mohli bychom například vše zapisovat\ndo jednoho souboru. Brzy bychom ale zjistili, že se jedná o velice neefektivní\nzpůsob - operace nad daty by s rostoucím počtem záznamů začaly být pomalé.\nV těchto situacích nastupují na scénu databáze (resp. databázové systémy).</p>\n<p>Jedná se o vysoce sofistikovaná řešení různých výrobců (zmínit můžeme třeba\n<em>Oracle</em> nebo <em>MySQL</em>). Výhodou je, že tyto systémy mají společný způsob,\njakým se s nimi komunikuje (API) - prostřednictvím jazyka <strong>SQL</strong>. Nemusí nás tedy\nzajímat, jak databáze funguje uvnitř.</p>\n<p>Základem relačních databází jsou <strong>tabulky</strong>. Můžeme si je představit jako jeden\nlist sešitu MS Excel (celý sešit by pak odpovídal databázi). Jednotlivé řádky\ntabulky jsou <strong>záznamy</strong>. Ty se skládají z několika <strong>atributů</strong> (sloupců).\nŘádky a sloupce odpovídají Excelovým řádkům a sloupcům.</p>\n<p>Můžeme mít například tabulku <code>OBJEDNAVKA</code>. Bude tvořena sloupci <code>ID_OBJEDNAVKY</code>,\n<code>ID_ZAKAZNIKA</code>, <code>DATUM</code>, <code>CENA</code>. Jednotlivé záznamy představují jednotlivé\nobjednávky - víme tedy kdy si kdo udělal objednávku, a kolik za ni zaplatil.</p>\n<h2>SQL</h2>\n<p>SQL (Structured Querying Language - Strukturovaný dotazovací jazyk) nám umožňuje\nkomunikovat s databází. Výrobci jednolivých databází obvykle jazyk v některých\noblastech rozšiřují, základ je ale společný a standardzivaný. Kromě práci s daty\n(CRUD) se jazyk používá pro vytváření tabulek a dalších objektů, pro správu\nuživatelů databáze apod.</p>\n<p>SQL příkazy mohou být do databáze posílány z webové aplikace (Python, Javascript, ...),\nz terminálu, nebo třeba z aplikace přímo určené pro správu databáze.\nJe zvykem jednotlivé příkazy jazyka psát <code>VELKÝMI PÍSMENY</code>. Příkazy oddělujeme\nstředníkem <code>;</code>. Jednořádkové komentáře uvozujeme dvěma spojovníky <code>--</code>\na víceřádkové komentáře píšeme mezi <code>/*</code> a <code>*/</code>.</p>\n<p>Pojďme se podívat na některé z příkazů, které SQL podporuje.</p>\n<h3>Tabulky</h3>\n<p>Pro vytvoření tabulky použijeme příkaz <code>CREATE TABLE</code>, následovaný jménem tabulky\na definicí jednotlivých sloupců. U sloupců definuje jméno, typ a případně další\nvlastnosti.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"cm\">/* Vytvoří tabulku ROBOT se sloupci ID, NAME a TYPE.</span>\n<span class=\"cm\"> ID je celé číslo, NAME a TYPE jsou řetězce.</span>\n<span class=\"cm\">*/</span>\n<span class=\"k\">CREATE</span> <span class=\"k\">TABLE</span> <span class=\"n\">ROBOT</span> <span class=\"p\">(</span>\n <span class=\"n\">ID</span> <span class=\"nb\">INT</span> <span class=\"k\">PRIMARY</span> <span class=\"k\">KEY</span><span class=\"p\">,</span>\n <span class=\"n\">NAME</span> <span class=\"nb\">TEXT</span><span class=\"p\">,</span>\n <span class=\"k\">TYPE</span> <span class=\"nb\">TEXT</span>\n<span class=\"p\">)</span>\n</pre></div><p>Význam <code>PRIMARY_KEY</code> si vysvětlíme později. Pokud bychom chtěli tabulku smazat,\ntak použijeme <code>DROP TABLE JMENO_TABULKY</code>. Jen pozor - spolu s tabulkou se\n<strong>smažou všechny záznamy</strong> v ní obsažené.</p>\n<h3>INSERT</h3>\n<p>Jakmile máme vytvořenou tabulku, můžeme do ní vkládat záznamy. Děláme tak\npomocí příkazu <code>INSERT</code>. Uvedeme jméno tabulky a pak hodnoty, představující\nzáznam (nebo záznamy), které se mají vložit.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- Vloží do tabulky ROBOT nového Robota s ID 1, jménem Jim a typem AGGRESIVE</span>\n<span class=\"k\">INSERT</span> <span class=\"k\">INTO</span> <span class=\"n\">ROBOT</span> <span class=\"p\">(</span><span class=\"n\">ID</span><span class=\"p\">,</span> <span class=\"n\">NAME</span><span class=\"p\">,</span> <span class=\"k\">TYPE</span><span class=\"p\">)</span> <span class=\"k\">VALUES</span> <span class=\"p\">(</span><span class=\"mi\">1</span><span class=\"p\">,</span> <span class=\"ss\">"Jim"</span><span class=\"p\">,</span> <span class=\"ss\">"AGGRESSIVE"</span><span class=\"p\">);</span>\n\n<span class=\"c1\">-- Vloží do tabulky ROBOT další dva Roboty</span>\n<span class=\"k\">INSERT</span> <span class=\"k\">INTO</span> <span class=\"n\">ROBOT</span> <span class=\"p\">(</span><span class=\"n\">ID</span><span class=\"p\">,</span> <span class=\"n\">NAME</span><span class=\"p\">,</span> <span class=\"k\">TYPE</span><span class=\"p\">)</span> <span class=\"k\">VALUES</span> <span class=\"p\">(</span><span class=\"mi\">2</span><span class=\"p\">,</span> <span class=\"ss\">"John"</span><span class=\"p\">,</span> <span class=\"ss\">"DEFENSIVE"</span><span class=\"p\">),(</span><span class=\"mi\">3</span><span class=\"p\">,</span> <span class=\"ss\">"Jack"</span><span class=\"p\">,</span> <span class=\"ss\">"DEFENSIVE"</span><span class=\"p\">);</span>\n</pre></div><h3>SELECT</h3>\n<p>Když už máme i nějaké zaznámy, tak je dobré mít způsob, jak je číst. Slouží\nk tomu mocný příkaz <code>SELECT</code>, který v nejzákladnější podobě očekává seznam sloupců,\nkterý se má pro jednotlivé záznamy vypsat, a jméno tabulky, ze které se mají\ndata číst. Seznam sloupců můžeme nahradit hvězdičkou <code>*</code>, pokud chceme vypsat\nhodnoty všech sloupců.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- vypíše hodnoty všech sloupců pro všechny roboty</span>\n<span class=\"k\">SELECT</span> <span class=\"o\">*</span> <span class=\"k\">FROM</span> <span class=\"n\">ROBOT</span><span class=\"p\">;</span>\n\n<span class=\"c1\">-- vypíše jen jména všech robotů</span>\n<span class=\"k\">SELECT</span> <span class=\"n\">NAME</span> <span class=\"k\">FROM</span> <span class=\"n\">ROBOT</span><span class=\"p\">;</span>\n</pre></div><p>Příkaz <code>SELECT</code> podporuje třeba také filtrování dat (ukážeme si později),\nřazení (<code>ORDER BY</code>) a seskupování dat (průměr - <code>AVG</code>, suma - <code>SUM</code>, ...)</p>\n<h3>UPDATE</h3>\n<p>V případě, že chceme data upravit (například změnit jméno robota),\ntak použijeme příkaz <code>UPDATE</code>. I ten očekává jméno tabulky, jejíž záznamy se\nmají upravit. Kromě toho také zadáme nové hodnoty pro jednotlivé sloupce.\nHodnoty sloupců, které neuvedeme, zůstanou nezměněné.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- nastaví všem robotům typ na AGGRESSIVE</span>\n<span class=\"k\">UPDATE</span> <span class=\"n\">ROBOT</span> <span class=\"k\">SET</span> <span class=\"k\">TYPE</span> <span class=\"o\">=</span> <span class=\"ss\">"AGGRESSIVE"</span><span class=\"p\">;</span>\n</pre></div><h3>DELETE</h3>\n<p>A konečně poslední oprací v CRUD je mazání. V SQL se záznamy mažou pomocí příkazu\n<code>DELETE</code>. I zde, stejně jako u ostatních CRUD příkazů, uvádíme jméno tabulky.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- smaže všechny roboty</span>\n<span class=\"k\">DELETE</span> <span class=\"k\">FROM</span> <span class=\"n\">ROBOT</span><span class=\"p\">;</span>\n</pre></div><h3>WHERE</h3>\n<p>Nejspíš vás napadalo, že pokud bychom vždy četli, upravovali nebo mazali všechna\ndata v tabulce, tak by systém nebyl moc dobře použitelný. Naštěstí ale můžeme\npomocí příkazu <code>WHERE</code> ovlivnit, nad jakými záznamy se bude operace provádět.</p>\n<p>Za <code>WHERE</code> se píší podmínky (spojované pomocí <code>AND</code> nebo <code>OR</code>), které musí\nplatit, aby se záznam přidal do množiny, nad kterou se bude operace\nprovádět. V podmínkách je možné použít různé operátory:</p>\n<ul>\n<li>rovnost: <code>WHERE SLOUPEC = HODNOTA</code></li>\n<li>nerovnost: <code>WHERE SLOUPEC > HODNOTA</code> (<code>></code>, <code><</code>, <code><></code>, ...)</li>\n<li>jedna z hodnot: <code>WHERE SLOUPEC IN (HODNOTA_1, HODNOTA_2)</code></li>\n<li>podřetězec: <code>WHERE SLOUPEC LIKE %HODNOTA%</code></li>\n</ul>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- vypíše všechny řádky o robotech, které mají typ AGGRESSIVE</span>\n<span class=\"k\">SELECT</span> <span class=\"o\">*</span> <span class=\"k\">FROM</span> <span class=\"n\">ROBOT</span> <span class=\"k\">WHERE</span> <span class=\"k\">TYPE</span> <span class=\"o\">=</span> <span class=\"ss\">"AGGRESSIVE"</span><span class=\"p\">;</span>\n\n<span class=\"c1\">-- přejmenuje roboty, jejichž jména začínají na J, na Jimmy</span>\n<span class=\"k\">UPDATE</span> <span class=\"n\">ROBOT</span> <span class=\"k\">SET</span> <span class=\"n\">NAME</span> <span class=\"o\">=</span> <span class=\"ss\">"Jimmy"</span> <span class=\"k\">WHERE</span> <span class=\"n\">NAME</span> <span class=\"k\">LIKE</span> <span class=\"ss\">"J%"</span><span class=\"p\">;</span>\n\n<span class=\"c1\">-- smaže robota s ID větším než 1</span>\n<span class=\"k\">DELETE</span> <span class=\"k\">FROM</span> <span class=\"n\">ROBOT</span> <span class=\"k\">WHERE</span> <span class=\"n\">ID</span> <span class=\"o\">></span> <span class=\"mi\">1</span><span class=\"p\">;</span>\n</pre></div><h2>Primární klíče</h2>\n<p>Každá tabulka by měla mít soupec, který jednoznačně identifikuje jednotlivé řádky.\nVe sloupci musí být unikátní hodnoty. Může se jednat o uměle vytvořené číslo\n(nejčastěji nazývané <code>ID</code>), nebo může jít o unikátní identifikátor z reálného\nsvěta. Pozor ale na to, že ne každý na první pohled unikátní identifikátor je\n<em>skutečně</em> unikátní - je například možné, aby dvě osoby měly stejné rodné číslo.\nJe tedy obecně lepší používat uměle vytvořené primární klíče.</p>\n<p>Primární klíče se používají proto, že zrychlují operace nad daty (rychle se podle\nnich vyhledává) a také proto, že snižují riziko duplicitních dat v databázi.\nPři vytváření tabulky zadáme u sloupce, který má být primárním klíčem, vlastnost\n<code>PRIMARY KEY</code>.</p>\n<h2>Cizí klíče</h2>\n<p>Cizí klíče - <code>FOREIGN KEY</code> - se využívají pro zachycení vazby mezi tabulkami.\nV dceřiné tabulce máme sloupec představující cizí klíč, jehož hodnoty se\nodkazují na primární klíč jiné tabulky.</p>\n<p>Například tabulka <code>OBJEDNAVKA</code> má cizí klíč <code>ID_ZAKAZNIKA</code>. Tento cizí klíč\nse odkazuje na primární klíč tabulky <code>ZAKAZNIK</code>. Tím zaručíme, že každá objednávka\nmusí být "napojená" na existujícího zákazníka.</p>\n<p><span class=\"figure\"><a href=\"/2019/brno-jaro-2019-ut/beginners/database/static/fk.png\"><img src=\"/2019/brno-jaro-2019-ut/beginners/database/static/fk.png\" alt=\"Cizí klíče\"></a></span></p>\n<p><a href=\"https://cdn-images-1.medium.com/max/1600/1*yW_ha3z8Mp6fUn9m6qWwNw.png\">zdroj obrázku</a></p>\n<h2>Spojování tabulek</h2>\n<p>Často potřebujeme v jednom dotazu číst data z více tabulek. Používáme k tomu\npříkaz <code>JOIN</code>, který je součástí příkazu <code>SELECT</code>. V základní verzi vezme\n<code>JOIN</code> záznamy z jedné tabulky a připojí k nim odpovídající záznamy z druhé\ntabulky. Na takto nově vzniklý řádek je možné udělat <code>JOIN</code> s další tabulkou.</p>\n<p>To, co považujeme za "odpovídající záznam" zapíšeme jako součást <code>JOIN</code>\n(za klíčové slovo <code>ON</code>). Nejdříve ale musíme zadat jméno tabulky,\nkterou chceme připojit.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- vypíše jména a příjmení zákazníků, kteří udělali objednávku za více než 500 Kč</span>\n\n<span class=\"k\">SELECT</span> <span class=\"n\">ZAKAZNIK</span><span class=\"p\">.</span><span class=\"n\">JMENO</span><span class=\"p\">,</span> <span class=\"n\">ZAKAZNIK</span><span class=\"p\">.</span><span class=\"n\">PRIJMENI</span>\n<span class=\"k\">FROM</span> <span class=\"n\">ZAKAZNIK</span>\n<span class=\"k\">JOIN</span> <span class=\"n\">OBJEDNAVKA</span> <span class=\"k\">ON</span> <span class=\"n\">OBJEDNAVKA</span><span class=\"p\">.</span><span class=\"n\">ZAKAZNIK_ID</span> <span class=\"o\">=</span> <span class=\"n\">ZAKAZNIK</span><span class=\"p\">.</span><span class=\"n\">ID</span>\n<span class=\"k\">WHERE</span> <span class=\"n\">OBJEDNAVKA</span><span class=\"p\">.</span><span class=\"n\">CENA</span> <span class=\"o\">></span> <span class=\"mi\">500</span><span class=\"p\">;</span>\n</pre></div><h2>Transakce</h2>\n<p>Transakce jsou skupiny příkazů (<code>SELECT</code>, <code>INSERT</code>, ...), která se buď provede\ncelá, nebo vůbec. To má velkou výhodu v tom, že pokud některý z příkazů vyvolá\nchybu, tak se databáze vrátí do původního stavu, my můžeme příkaz opravit\na celou transakci spustit znovu.</p>\n<p>O transakcích se ale bavíme především proto, že je důležité je <strong>ukončit</strong>,\na to buď příkazem <code>COMMIT</code> nebo <code>ROLLBACK</code>. <code>COMMIT</code> uloží námi provedené\nzměny trvale do databáze, takže je uvidíme i v budoucích transakcích.\n<code>ROLLBACK</code> vrátí databázi do původního stavu, což se může hodit v případě,\nže jsme provedli jiné změny, než jsme chtěli.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"c1\">-- vložení dat</span>\n<span class=\"k\">INSERT</span> <span class=\"k\">INTO</span> <span class=\"n\">ROBOT</span> <span class=\"p\">(</span><span class=\"n\">ID</span><span class=\"p\">,</span> <span class=\"n\">NAME</span><span class=\"p\">,</span> <span class=\"k\">TYPE</span><span class=\"p\">)</span> <span class=\"k\">VALUES</span><span class=\"p\">(</span><span class=\"mi\">1</span><span class=\"p\">,</span> <span class=\"ss\">"Jim"</span><span class=\"p\">,</span> <span class=\"ss\">"AGGRESSIVE"</span><span class=\"p\">);</span>\n<span class=\"c1\">-- v této chvíli ještě nejsou data trvale uložena</span>\n\n<span class=\"c1\">-- uložení dat</span>\n<span class=\"k\">COMMIT</span><span class=\"p\">;</span>\n</pre></div><h2>SQLite</h2>\n<p>Celé povídání o databázích by nemělo moc smysl, pokud bychom si neukázali,\njak je využít z prostředí Pythonu. Jak jsme už zmínili, existují různé databázové\nsystémy, my se ale zaměříme na jeden z nejjednodušších na "rozjetí", a to <strong>SQLite</strong>.</p>\n<p>Tento systém ukládá celou databázi do jednoho binárního souboru.\nMůžeme si na něm jednoduše vyzkoušet jednotlivé SQL příkazy,\na to prostřednictvím Python balíčku <code>sqlite3</code>. Ukažme si využití tohoto balíčku\nna příkladech.</p>\n<div class=\"highlight\"><pre><span></span><span class=\"n\">import</span> <span class=\"n\">sqlite3</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Připojíme</span> <span class=\"n\">se</span> <span class=\"n\">k</span> <span class=\"n\">databázi</span> <span class=\"p\">(</span><span class=\"n\">v</span> <span class=\"n\">souboru</span><span class=\"p\">)</span>\n<span class=\"k\">connection</span> <span class=\"o\">=</span> <span class=\"n\">sqlite3</span><span class=\"p\">.</span><span class=\"k\">connect</span><span class=\"p\">(</span><span class=\"s1\">'pyladies_example_1.db'</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Získáme</span> <span class=\"n\">instanci</span> <span class=\"n\">třídy</span> <span class=\"o\">`</span><span class=\"k\">Cursor</span><span class=\"o\">`</span><span class=\"p\">,</span> <span class=\"n\">pomocí</span> <span class=\"n\">které</span> <span class=\"n\">bude</span> <span class=\"k\">do</span> <span class=\"n\">databáze</span> <span class=\"n\">posílat</span> <span class=\"n\">příkazy</span>\n<span class=\"k\">cursor</span> <span class=\"o\">=</span> <span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">cursor</span><span class=\"p\">()</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Pokud</span> <span class=\"n\">tabulka</span> <span class=\"n\">už</span> <span class=\"n\">existuje</span><span class=\"p\">,</span> <span class=\"n\">tak</span> <span class=\"n\">ji</span> <span class=\"n\">odstraníme</span><span class=\"p\">,</span>\n<span class=\"o\">#</span> <span class=\"n\">abychom</span> <span class=\"n\">mohli</span> <span class=\"n\">skript</span> <span class=\"n\">spouštět</span> <span class=\"n\">opakovaně</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""DROP TABLE IF EXISTS ROBOT"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Vytvoříme</span> <span class=\"n\">jednoduchou</span> <span class=\"n\">tabulku</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""CREATE TABLE ROBOT (NAME TEXT, TYPE TEXT)"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Vložíme</span> <span class=\"k\">do</span> <span class=\"n\">tabulky</span> <span class=\"k\">data</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\"> INSERT INTO ROBOT (NAME, TYPE)</span>\n<span class=\"ss\"> VALUES ("</span><span class=\"n\">JIM</span><span class=\"ss\">", "</span><span class=\"n\">DEFENSIVE</span><span class=\"ss\">"), ("</span><span class=\"n\">JACK</span><span class=\"ss\">", "</span><span class=\"n\">OFFENSIVE</span><span class=\"ss\">")</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Dotážeme</span> <span class=\"n\">se</span> <span class=\"n\">na</span> <span class=\"n\">všechny</span> <span class=\"n\">roboty</span><span class=\"p\">,</span> <span class=\"n\">výsledky</span> <span class=\"n\">vypíšeme</span>\n<span class=\"n\">robots</span> <span class=\"o\">=</span> <span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"SELECT * FROM ROBOT"</span><span class=\"p\">)</span>\n<span class=\"k\">for</span> <span class=\"n\">robot</span> <span class=\"k\">in</span> <span class=\"n\">robots</span><span class=\"p\">:</span>\n <span class=\"n\">print</span><span class=\"p\">(</span><span class=\"n\">robot</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Uložíme</span> <span class=\"n\">změny</span> <span class=\"n\">a</span> <span class=\"n\">uzavřeme</span> <span class=\"n\">spojení</span>\n<span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">commit</span><span class=\"p\">()</span>\n<span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">close</span><span class=\"p\">()</span>\n</pre></div><div class=\"highlight\"><pre><span></span><span class=\"o\">#</span> <span class=\"n\">Složitejší</span> <span class=\"n\">příklad</span><span class=\"p\">,</span> <span class=\"n\">který</span> <span class=\"n\">pracuje</span> <span class=\"n\">s</span> <span class=\"n\">primárními</span> <span class=\"n\">a</span> <span class=\"n\">cizími</span> <span class=\"n\">klíči</span>\n<span class=\"o\">#</span> <span class=\"n\">a</span> <span class=\"n\">se</span> <span class=\"n\">spojováním</span> <span class=\"n\">tabulek</span>\n\n<span class=\"n\">import</span> <span class=\"n\">sqlite3</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Připojíme</span> <span class=\"n\">se</span> <span class=\"n\">k</span> <span class=\"n\">databázi</span> <span class=\"p\">(</span><span class=\"n\">v</span> <span class=\"n\">souboru</span><span class=\"p\">)</span>\n<span class=\"k\">connection</span> <span class=\"o\">=</span> <span class=\"n\">sqlite3</span><span class=\"p\">.</span><span class=\"k\">connect</span><span class=\"p\">(</span><span class=\"s1\">'pyladies_example_2.db'</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Získáme</span> <span class=\"n\">instanci</span> <span class=\"n\">třídy</span> <span class=\"o\">`</span><span class=\"k\">Cursor</span><span class=\"o\">`</span><span class=\"p\">,</span> <span class=\"n\">pomocí</span> <span class=\"n\">které</span> <span class=\"n\">bude</span> <span class=\"k\">do</span> <span class=\"n\">databáze</span> <span class=\"n\">posílat</span> <span class=\"n\">příkazy</span>\n<span class=\"k\">cursor</span> <span class=\"o\">=</span> <span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">cursor</span><span class=\"p\">()</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Pokud</span> <span class=\"n\">tabulky</span> <span class=\"n\">už</span> <span class=\"n\">existují</span><span class=\"p\">,</span> <span class=\"n\">tak</span> <span class=\"n\">ji</span> <span class=\"n\">odstraníme</span><span class=\"p\">,</span>\n<span class=\"o\">#</span> <span class=\"n\">abychom</span> <span class=\"n\">mohli</span> <span class=\"n\">skript</span> <span class=\"n\">spouštět</span> <span class=\"n\">opakovaně</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""DROP TABLE IF EXISTS ROBOT"""</span><span class=\"p\">)</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""DROP TABLE IF EXISTS BATTLE"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Vytvoříme</span> <span class=\"n\">tabulku</span> <span class=\"n\">s</span> <span class=\"n\">roboty</span> <span class=\"n\">a</span> <span class=\"n\">tabulky</span> <span class=\"n\">s</span> <span class=\"n\">výsledky</span> <span class=\"n\">bitev</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\">-- u jednotlivých roborů si ukládáme ID, jméno a typ</span>\n<span class=\"ss\">CREATE TABLE ROBOT (</span>\n<span class=\"ss\"> ROBOT_ID INT PRIMARY KEY,</span>\n<span class=\"ss\"> NAME TEXT,</span>\n<span class=\"ss\"> TYPE TEXT)</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\">-- bitva se skládá z ID bitvy, ID vítěze a poraženého (odpovídají ID v tabulce ROBOT)</span>\n<span class=\"ss\">-- a z bodů pro vítěze a poraženého</span>\n<span class=\"ss\">CREATE TABLE BATTLE (</span>\n<span class=\"ss\"> BATTLE_ID INT PRIMARY KEY,</span>\n<span class=\"ss\"> WINNER_ID INT,</span>\n<span class=\"ss\"> LOSER_ID INT,</span>\n<span class=\"ss\"> WINNER_POINTS INT,</span>\n<span class=\"ss\"> LOSER_POINTS INT,</span>\n<span class=\"ss\"> FOREIGN KEY(WINNER_ID) REFERENCES ROBOT(ROBOT_ID),</span>\n<span class=\"ss\"> FOREIGN KEY(LOSER_ID) REFERENCES ROBOT(ROBOT_ID)</span>\n<span class=\"ss\"> )</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Vložíme</span> <span class=\"k\">do</span> <span class=\"n\">tabulkek</span> <span class=\"k\">data</span>\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\"> INSERT INTO ROBOT (ROBOT_ID, NAME, TYPE) VALUES</span>\n<span class=\"ss\"> (1, "</span><span class=\"n\">JIM</span><span class=\"ss\">", "</span><span class=\"n\">DEFENSIVE</span><span class=\"ss\">"), (2, "</span><span class=\"n\">JACK</span><span class=\"ss\">", "</span><span class=\"n\">OFFENSIVE</span><span class=\"ss\">"), (3, "</span><span class=\"n\">JIMMY</span><span class=\"ss\">", "</span><span class=\"n\">OFFESIVE</span><span class=\"ss\">")</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\"> INSERT INTO BATTLE (BATTLE_ID, WINNER_ID, LOSER_ID, WINNER_POINTS, LOSER_POINTS) VALUES</span>\n<span class=\"ss\"> (1, 1, 2, 10, 8), -- robot 1 porazil robota 2 se skóre 10:8 (v bitvě 1)</span>\n<span class=\"ss\"> (2, 2, 1, 6, 9),</span>\n<span class=\"ss\"> (3, 2, 3, 10, 9),</span>\n<span class=\"ss\"> (4, 1, 3, 5, 4),</span>\n<span class=\"ss\"> (5, 3, 2, 2, 0),</span>\n<span class=\"ss\"> (6, 1, 2, 9, 6)</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Dotážeme</span> <span class=\"n\">se</span> <span class=\"n\">na</span> <span class=\"n\">výsledky</span> <span class=\"n\">bitev</span><span class=\"p\">,</span> <span class=\"n\">které</span> <span class=\"n\">vyhrál</span> <span class=\"n\">robot</span> <span class=\"n\">se</span> <span class=\"n\">jménem</span> <span class=\"ss\">"JIM"</span>\n<span class=\"n\">scores</span> <span class=\"o\">=</span> <span class=\"k\">cursor</span><span class=\"p\">.</span><span class=\"k\">execute</span><span class=\"p\">(</span><span class=\"ss\">"""</span>\n<span class=\"ss\"> SELECT BATTLE.WINNER_POINTS, BATTLE.LOSER_POINTS</span>\n<span class=\"ss\"> FROM BATTLE</span>\n<span class=\"ss\"> JOIN ROBOT ON ROBOT.ROBOT_ID = BATTLE.WINNER_ID</span>\n<span class=\"ss\"> WHERE ROBOT.NAME = "</span><span class=\"n\">JIM</span><span class=\"ss\">"</span>\n<span class=\"ss\">"""</span><span class=\"p\">)</span>\n\n<span class=\"k\">for</span> <span class=\"n\">score</span> <span class=\"k\">in</span> <span class=\"n\">scores</span><span class=\"p\">:</span>\n <span class=\"n\">print</span><span class=\"p\">(</span><span class=\"n\">score</span><span class=\"p\">)</span>\n\n<span class=\"o\">#</span> <span class=\"n\">Uložíme</span> <span class=\"n\">změny</span> <span class=\"n\">a</span> <span class=\"n\">uzavřeme</span> <span class=\"n\">spojení</span>\n<span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">commit</span><span class=\"p\">()</span>\n<span class=\"k\">connection</span><span class=\"p\">.</span><span class=\"k\">close</span><span class=\"p\">()</span>\n</pre></div><h2>ORM</h2>\n<p>V Pythonu jsme se naučili data a logiku sdružovat do <strong>tříd</strong>. V databázích\nse data sdružují do <strong>tabulek</strong>. O propojení těchto konceptů se stará ORM - Objektově\nRelační Mapování. Pomocí ORM Frameworku (v Pythonu např.\n<a href=\"https://en.wikipedia.org/wiki/SQLAlchemy\">SQLAlchemy</a>) vytváříme Python třídy,\npro které existují odpovídající tabulky v databázi.</p>\n<p>Například Python třída <code>Kocka</code> bude mít odpovídající tabulku <code>KOCKA</code>.\nAtributy třídy (<code>Vek</code>, <code>Barva</code>) budou v databázi existovat jako sloupce.\nJednotlivé řádky tabulky bude možné načíst do aplikace jako instance třídy <code>Kocka</code>.\nJednotlivé kočky samozřejmě bude možné upravovat, mazat, nebo vytvářet nové.</p>\n<p>Ukázku ORM najdete na <a href=\"https://en.wikipedia.org/wiki/SQLAlchemy#Schema_definition\">Wikipedii</a></p>\n\n\n "
}
}
}