Databáze
Obsah:
- Úvod do databází
- Úvod do relačních databází
- Úvod do SQL + Python a Pandas
Úvod do databází
Doteď jste na kurzu zřejmě pracovali se soubory - odněkud soubor stáhli na svůj počítač, a tam ho zpracovali, a pak třeba zase výsledek někam nahráli. Tento postup ale nestačí, pokud s těmito daty potřebuje pracovat více lidí (nebo strojů) najednou a zároveň je i aktualizovat. To se řeší tak, že spustíte databázový systém (DBMS, SŘBD), ten ta data u sebe spravuje (soubory na disku obsahující specializované datové struktury) a vy se na tento systém připojujete přes počítačovou síť. Nebo třeba používáte webovou aplikaci a ta je na tento databázový systém (databázi) připojena.
Např. e-shop bude pracovat s databází, ve které jsou uloženy informace o prodávaném zboží, rozdělení zboží do kategorií, objednávky, reklamace, registrovaní uživatelé... Takovou databázi můžeme označit za "transakční".
Vy jako datoví analytici, inženýři apod. se pravděpodobně budete chtít do takovéto databáze připojit a stáhnout si z ní část dat pro svou další práci. Nebo dokonce může být vaším úkolem vytvořit novou "analytickou" databázi, která obsahuje data v jiné podobě, např. prodávané zboží bez popisků, bez fotografií, ale zato se statistikami návštěvnosti a prodejnosti.
V těchto materiálech budeme pracovat s databází SQLite. Ta je poněkud specifická tím, že je uložena v jednom souboru (s příponou .sqlite nebo .db), není to tedy samostatně běžící server, ke kterému byste se připojovali, jak je tomu u většiny databází. O to snadněji se ale s SQLite pracuje. I SQLite umožňuje pracovat s jednou databází (jedním souborem) z několika programů zároveň, narozdíl od např. CSV nebo XLS sobourů.
Vlastnosti databází
Od databáze (resp. databázového systému) očekáváme několik vlastností:
- Že bude uchovávat data :) Ne, vážně - data musí zůstat beze ztráty uložena i v případě výpadku napájení, když dojde místo na disku... Že existují nástroje pro zálohování a obnovu databáze.
- Práce s daty "z různých úhlů". Např. v e-shopu chceme jednou vypsat zboží podle kategorie, jindy podle stavu skladových zásob, jindy na základě full-textového vyhledávání..
- Podpora uživatelů, rolí, oprávnění a kontroly přístupu k datům.
- Rychlost, výkon, propustnost, nízká latence
- Škálovatelnost - jak do velikosti (gigabajty, terabajty, petabajty), tak do počtu prováděných operací za sekundu
- ACID
- Atomicity: celá transakce (série několika databázových operací) se provede celá, nebo vůbec
- Consistency: nesmí být narušena integrita dat, tj. podmínek definových v databázovém schématu (např. unikátní hodnoty, reference řádků mezi tabulkami...)
- Isolation: vícero současně probíhajících transakcí se nesmí navzájem ovlivnit
- Durability: jakmile je transakce dokončena, je zaznamenána trvalým způsobem, nesmaže ji např. výpadek napájení
Když se nad tím zamyslíte, jedná se o protichůdné požadavky. Tvůrci databázových systémů tak musí zvolit nějaký kompomis. Někdy lze databáze nakonfigurovat, zda má např. preferovat izolaci, nebo propustnost (viz PostgreSQL Transaction Isolation). Žádná databáze není "nejlepší" a vhodná na všechno. Zrovna co se týče rozdílu mezi "transakční" a "analytickou" databází, tak často pro tyto oblasti jsou vhodné odlišné systémy a přístupy.
Relační databáze
Jde o typickou, klasickou, nejčastěji používanou kategorii databázových systémů. Používají se od 70. let, existuje okolo nich široký ekosystém se spoustou nástrojů a také lidí, kteří s nimi umí pracovat.
Co relační databáze obsahuje:
- "databáze" (jeden databázový systém může spravovat několik databází)
- uživatelé (users)
- tabulky
- sloupce
- indexy
- constraints
- cizí klíče (foreign keys)
- triggery
- pohledy (views)
- procedury
- sekvence
Data se ukládají v podobě řádek a sloupců do jednotlivých tabulek:
| title | year | director_name | director_birthdate |
|---|---|---|---|
| Pelíšky | 1999 | Jan Hřebejk | 1967-06-27 |
| Forrest Gump | 1994 | Robert Zemeckis | 1951-05-14 |
| Návrat do budoucnosti | 1985 | Robert Zemeckis | 1951-05-14 |
Každý řádek je označen svým unikátním identifikátorem, a duplicitní data se normalizují:
| movie_id | title | year | director_id |
|---|---|---|---|
| 1 | Pelíšky | 1999 | 1 |
| 2 | Forrest Gump | 1994 | 2 |
| 3 | Návrat do budoucnosti | 1985 | 2 |
| director_id | name | birthdate |
|---|---|---|
| 1 | Jan Hřebejk | 1967-06-27 |
| 2 | Robert Zemeckis | 1951-05-14 |
Vztah mezi režiséry a filmy je zde 1:N. Každý jeden režisér je provázán s několika filmy. Každý jeden film je provázán s jedním režisérem (a nebo žádným, pokud jeho director_id není vyplněno).
Co když chceme vyjádřit možnost, že jeden film může mít více než jednoho režiséra?
| movie_id | title | year |
|---|---|---|
| 1 | Pelíšky | 1999 |
| 2 | Forrest Gump | 1994 |
| 3 | Návrat do budoucnosti | 1985 |
| director_id | name | birthdate |
|---|---|---|
| 1 | Jan Hřebejk | 1967-06-27 |
| 2 | Robert Zemeckis | 1951-05-14 |
| 3 | Joe Smith | 1950-01-01 |
| movie_id | director_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 3 | 3 |
SQL
SQL je jazyk, kterým komunikujeme s relační databází. Umí vyjádřit různé operace nad tabulkami - výběr sloupců, řádků, vztahy mezi více tabulkami, agregace hodnot z více řádků... (Nepřípomíná vám to něco? Pandas DataFrame? :) )
Příklad:
SELECT title, year FROM movies;
UPDATE directors SET birthdate = "1950-01-02" WHERE director_id = 3;Dokonce i jiný software, než jsou relační databáze, rozumí SQL. Často právě analytické nástroje, data lakes, Amazon S3... Pak existuje spousta databází a jazyků, které se jazykem SQL inspirovaly - např. databáze Cassandra a její jazyk CQL.
Instalace a import SQLAlchemy
Každá databáze se z Pythonu používá trochu jinak - liší se název modulu, který je potřeba importovat, mírně se liší způsob předávání dat použitých v SQL dotazech, liší se forma dat vrácených ve výsledku... Použijeme proto knihovnu SQLAlchemy, která tyto detaily abstrahuje a poskytuje rozhraní stejné pro všechny databáze (které SQLAlchemy podporuje).
SQLAlchemy se skládá ze dvou částí:
- Core - to je právě ta zmíněná abstrakce základních operací s databází, tuto část budeme používat
- engine, connections, transactions
- table metadata
- SQL expression language
- ORM - umožňuje pracovat s daty v databázi prostřednistvím mapování na objekty
- zajímavé hlavně při psaní větších aplikací (webových aplikací apod.)
Instalaci knihovny SQLAlchemy provedeme přes pip (nezapomeňte mít aktivovaný venv, do kterého to chcete instalovat):
pip install sqlalchemyPřípadně lze instalaci spustit přímo z Jupyteru:
%pip install sqlalchemy
Knihovnu SQLAlchemy naimportujeme:
import sqlalchemy
Jako vždy, naimportujeme i Pandas:
import pandas as pd
Stažení filmové databáze
Pro ukázku práce s databází použijeme data od Míry Brabence. Jde o databázi typu SQLite - skládá se z jediného souboru, ve kterém jsou všecha data. To znamená, že není žádný velký server, ke kterému byste se připojovaly jménem a heslem, jako u "klasických" databází (PostgreSQL, Oracle, MS SQL...). Databáze v podobě SQLite souboru má výhodu v tom, že když něco rozbijete, tak ten soubor prostě smažete a uděláte si nový :)
Soubor s databází stáhneme knihovnou Requests, kterou už máte nainstalovanou (instrukce byly v první hodině) a uložíme ho do souboru v pracovním adresáři, tj. tam, odkud jste spustili Jupyter Notebook.
from pathlib import Path
import requests
db_data = requests.get('https://github.com/messa/movie-db-2020/raw/master/movies.sqlite').content
Path('movies.sqlite').write_bytes(db_data)
Otevření databáze, průzkum struktury
engine = sqlalchemy.create_engine('sqlite:///movies.sqlite')
Objekt engine obsahuje připojení do databáze a skrz něj budeme provádět operace s danou databází.
Začneme tím, že se podíváme, jaká je struktura té databáze - jaké tabulky obsahuje a co obsahují ty tabulky. Každá databáze na to má jiné finty, jak toto zjistit (někdy funguje SQL příkaz SHOW TABLES, jindy je zase potřeba SELECT z metadat), naštěstí SQLAlchemy nám s tím pomůže.
sqlalchemy.inspect(engine).get_table_names()
def prozkoumat(engine):
inspector = sqlalchemy.inspect(engine)
for schema in inspector.get_schema_names():
print('Schema:', schema)
for table_name in inspector.get_table_names(schema=schema):
print()
print(' Table:', table_name)
print()
for column in inspector.get_columns(table_name, schema=schema):
print(' Column:', column['name'].ljust(12), column['type'])
prozkoumat(engine)
Na prozkoumání struktury a obsahu databáze můžete použít nějaký specializovaný program - DB Browser, DBeaver, MySQL Workbench, ... je jich hodně, typicky se specializují na konkrétní druh databáze, některé programy jsou zdarma a některé placené. Existují i webové aplikace, nejznámější asi český Adminer. Zde jsem vám chtěl ukázat, že toho samého se dá dosáhnout i takto z Pythonu.
Poznámka: struktura této databáze je trochu jiná, než byl motivační příklad na začátku.
Nemáme tu directors, máme tu actors.
SELECT
Ukážeme si několik způsobů, jak data z databáze "vytáhnout" do Pythonu.
engine.execute('SELECT id, name, birth_date FROM actors')
Výsledkem volání engine.execute(...) je objekt, který umí reprezentuje výsledek SQL dotazu. V případě SELECT se skrz tento objekt dají procházet získané řádky, v případě operací INSERT, UPDATE, DELETE tento objekt obsahuje např. počet změněných řádek.
Zkusíme si zobrazit jednotlivé řádky. LIMIT slouží k omezení počtu vracených záznamů.
result = engine.execute('SELECT id, name, birth_date FROM actors LIMIT 5')
for row in result:
print(row)
Zdálo by se, že procházení result vrací n-tice (tuple), ale není tomu tak.
result = engine.execute('SELECT id, name, birth_date FROM actors LIMIT 5')
for row in result:
print(type(row))
Jde o SQLAlchemy objekt RowProxy, který umí pár zajímavých věcí:
- vrátit hodnotu daného sloupce podle pořadí – např.
row[2] - vrátit hodnotu daného sloupce podle názvu – např.
row["name"] - vrátit dict s hodnotami – např.
dict(row)
result = engine.execute('SELECT id, name, csfd_url FROM actors LIMIT 5')
for row in result:
print(row[0], row[1])
Poznámka: sloupce vyjmenované za SELECT mohou být v jakémkoli pořadí. Stejně tak vytahávat si je z toho RowProxy objektu také můžeme libovolně v jakémkoli pořadí.
result = engine.execute('SELECT name, id, csfd_url FROM actors LIMIT 5')
for row in result:
print(row[2], row[0])
Pomocí SELECT * si vytáhneme všechny dostupné sloupce:
result = engine.execute('SELECT * FROM actors LIMIT 5')
for row in result:
print(row)
Asi bude lepší místo row[2] používat např. row["name"],
nebo si to převést celé do slovníku přes dict(row):
result = engine.execute('SELECT * FROM actors LIMIT 5')
for row in result:
print(row['id'], row['name'], row['birth_date'])
result = engine.execute('SELECT * FROM actors LIMIT 5')
for row in result:
print(dict(row))
Z dat lze vytvořit i Pandas DataFrame:
pd.read_sql_query('SELECT * FROM actors', engine)
Toto je zkratka pro načtení celé tabulky:
pd.read_sql_table('actors', engine)
Průzkum dat ve filmové databázi
Data z jednotlivých tabulek si nahrajeme do Pandas DataFramů, se kterými budeme dále pracovat.
Pro přehlednost si zde vypíšeme ukázku obsahu těch DataFramů, abychom věděli, s čím pracujeme.
sqlalchemy.inspect(engine).get_table_names()
actors = pd.read_sql_table('actors', engine)
movies = pd.read_sql_table('movies', engine)
movie_to_actor = pd.read_sql_table('movie_to_actor', engine)
actors.head()
movies.head()
movie_to_actor.head()
SELECT ... WHERE ... AND/OR
list(engine.execute('''
SELECT * FROM actors
WHERE birth_date > "2000-01-01" AND birth_date < "2001-01-01"
'''))
actors[(actors['birth_date'] > '2000-01-01') & (actors['birth_date'] < '2001-01-01')]
SELECT ... ORDER BY
list(engine.execute('''
SELECT * FROM actors
WHERE birth_date > "2005-01-01"
ORDER BY birth_date ASC'''))
actors[(actors['birth_date'] > '2005-01-01')].sort_values('birth_date')
Při řazení podle více sloupců pozor na pořadí :)
pd.read_sql_query('''
SELECT title, year, rating FROM movies
WHERE rating > 90 AND year >= 2000
ORDER BY year ASC, title DESC
''', engine)
movies[(movies['rating'] > 90) & (movies['year'] >= 2000)] \
.sort_values('rating').sort_values('year')
SELECT ... GROUP BY
GROUP BY umožňuje "shluknout" řádky do skupin, a nad těmito skupinami pak provádět agregační funkce (MIN, MAX, AVG, COUNT apod.).
Tyto skupiny pak lze dále filtrovat pomocí HAVING (podobně jako WHERE filtruje řádky, a to předtím, než se z nich vytvářejí ty skupiny).
list(engine.execute('''
SELECT year, COUNT(*) FROM movies GROUP BY year
HAVING COUNT(*) > 5 ORDER BY COUNT(*) DESC '''))
pd.read_sql_query('''
SELECT year, COUNT(id), MIN(rating), AVG(rating), MAX(rating)
FROM movies
WHERE rating > 10
GROUP BY year
HAVING COUNT(id) > 7
ORDER BY year DESC
''', engine)
To GROUP BY je potřeba, protože bez toho se bude celá tabulka považovat za jedinou skupinu a použití agregačních funkcí způsobí to, že se vám vrátí jen jeden řádek výsledků:
pd.read_sql_query('''
SELECT COUNT(*), MIN(birth_date), MAX(birth_date)
FROM actors
''', engine)
Jaký je rozdíl mezi COUNT(*), COUNT(id) a COUNT(něco)?
COUNT(*) vrací počet řádků. COUNT(sloupec) vrací počet řádků, ve kterých je v daném sloupci nějaká hodnota (tj. není tam NULL). Pokud tím sloupcem je id, který je téměř vždy NON-NULL, tak je COUNT(id) a COUNT(*) vlastně to samé :)
pd.read_sql_query('''
SELECT COUNT(*), COUNT(id), COUNT(birth_date)
FROM actors
''', engine)
Jak je vidět, u některých herců není vyplněno birth_date.
Struktura SQL dotazu
Ještě rekapitulace, jak se probíhá provádění SQL dotazu :)
- databáze vezme tabulky vyjmenované ve FROM
- databáze čte řádky (jeden po druhém)
- ty řádky se filtrují podle uvedených WHERE výrazů
- přefiltrované řádky se seskupí podle GROUP BY výrazů do skupin
- ty skupiny se filtrují pode uvedených HAVING výrazů
- výsledek se seřadí podle ORDER BY
- výsledek se ořízne podle LIMIT
Probrali jsme jen to nejdůležitější. Pro představu, jaké jsou možnosti zápisu SELECT dotazu (převzato z dokumentace SQLite):
Úkol: který herec účinkoval v největším počtu filmů
Najděte v tabulce movie_to_actor id herce, u kterého je v té tabulce nejvíce id filmů.
Postup:
- Pomocí GROUP BY si vypište vždy actor_id a k tomu počet řádků s tímto actor_id
- Pomocí ORDER BY a LIMIT zjistěte actor_id, u kterého je tento počet nejvyšší
- Bonus: zjistěte jméno tohoto herce
JOIN
JOIN je v SQL to samé, co je merge v DataFrame (viz některá předchozí hodina).
pd.read_sql_query('''
SELECT movie_id, title, actor_id, name, a.id, m.id AS idecko_filmu
FROM actors AS a
LEFT JOIN movie_to_actor AS ma ON a.id = ma.actor_id
LEFT JOIN movies AS m ON m.id = ma.movie_id
''', engine)
CREATE TABLE
Pokud budeme chtít vytvořit novou tabulku, používá se k tomu příkaz CREATE TABLE. Musíme vyjmenovat názvy a typy sloupců, které v tabulce chceme mít.
try:
engine.execute('''
CREATE TABLE phonebook (
id INT PRIMARY KEY,
name VARCHAR,
phone_number VARCHAR
)
''')
except OperationalError as e:
if str(e) == 'database is locked':
print('Zkus zabit kernel a znovu spustit primo tento prikaz :/ Me')