Python a MySQL: kompletní praktický průvodce, 1. část

MySQL je v současnosti jedním z nejoblíbenějších systémů pro řízení databází (DBMS) na trhu. V žebříčku DB-Engines Ranking z listopadu 2023 je druhý za systémem Oracle.

V tomto průvodci se podíváme na to, jak pracovat s databází MySQL při vývoji aplikací v jazyce Python. Konkrétně vytvoříme malou databázi s hodnocením filmů a naučíme se s ní pracovat přímo z kódu.

Důležité: Tento průvodce vyžaduje znalost konceptů jazyka Python, jako jsou smyčky for, funkce, zpracování výjimek, vytvoření virtuálního prostředí a instalace balíčků Pythonu pomocí pip. Budeš také potřebovat znalost relačních databází a dotazů SQL (například SELECT, DROP, CREATE a JOIN).

Proč používáme MySQL: výhody této databáze

SQL je zkratka Structured Query Language (strukturovaný dotazovací jazyk). Je široce používán pro správu relačních databází, zejména MySQL.

MySQL je databázový systém s otevřeným zdrojovým kódem, který byl vydán 23. května 1995 a rychle se stal jedním z lídrů na trhu. Základní funkce MySQL jsou poskytovány zdarma, existují však placené doplňky.

MySQL dnes používají všechny významné technologické společnosti včetně Googlu, LinkedIn, Netflixu, X (Twitter) a Uberu.

Za úspěch systému MySQL částečně můžou následující výhody:

• Snadná instalace. MySQL je k dispozici pro všechny běžné operační systémy včetně Windows, MacOS, Linux a Solaris. Snadno se konfiguruje a další nástroje, jako je phpMyAdmin, proces instalace urychlují.
• Vysoká rychlost práce. MySQL je považována za velmi rychlou databázi. Vyžaduje relativně málo prostředků a dlouhodobě se efektivně škáluje.
• Práva přístupu a zabezpečení uživatelů. Skript MySQL umožňuje nastavit úroveň ochrany heslem, přiřadit hesla administrátora a přidávat a odebírat přístupová práva. Tento skript usnadňuje správu uživatelů. Jiné DBMS, například PostgreSQL, používají konfigurační soubory, s nimiž je práce obtížnější.
• Podpora komunity. MySQL je podporována rozsáhlou komunitou vývojářů open source softwaru.

Instalace MySQL Server a MySQL Connector/Python

Pro vytvoření naší aplikace je třeba nainstalovat tři věci:

• MySQL Server
• Python
• MySQL Connector/Python

Veškerou práci s databází provádí server MySQL. Po instalaci a konfiguraci serveru k němu můžeš připojit aplikaci v jazyce Python pomocí MySQL Connector/Python.

Instalace MySQL Server

V oficiální dokumentaci je podrobně popsáno, jak stáhnout a nainstalovat server MySQL. Najdeš zde pokyny pro všechny populární operační systémy včetně Windows, MacOS, Linux a Solaris.

Pro systém Windows je nejlepší stáhnout si MySQL Installer. Můžeš použít webový instalátor nebo si stáhnout plný instalátor. Instalátor tě provede celým procesem.

Na první stránce budeš vyzván*a k výběru typu instalace: pouze server, pouze klient, úplná instalace a výběrová instalace. Pro tento průvodce budeš potřebovat instalaci pouze na server:

Pokud nemáš nainstalovaný balíček Microsoft Visual C++ 2019 Redistributable Package, který je nezbytnou podmínkou pro MySQL Server 8.0, otevře se stránka Check requirements a vyzve tě k instalaci nové kompatibilní verze balíčku. Kliknutím na tlačítko Execute (Spustit) jej nainstaluj:

Otevře se okno instalace balíčku Microsoft Visual C++ 2015-2019 Redistributable Package. Přijmi licenční podmínky a klikni na tlačítko Install:

Po úspěšné instalaci balíčku klikni na tlačítko Close. Poté klikni na tlačítko Next (Další) na stránce Check requirements (Zkontrolovat požadavky) v okně MySQL Installer.

Na stránce Instalace klikni na tlačítko Execute. Po úspěšné instalaci přejdi ke konfiguraci produktu kliknutím na tlačítko Next.

související kurzy:

Python developer

Jan Bubik

Senior Software Engineer, Barclays

O kurzu

Nastavení MySQL Server

Na stránkách Type and Networking and Authentication Method ponech nastavení beze změny a klikni na tlačítko Next.

Na stránce Accounts and Roles (Účty a role) je třeba zadat heslo pro účet root („administrátor“) a můžeš přidat uživatele s různými přístupovými právy:

Přestože je nutné zadat pověření uživatele root během instalace, můžeš je později změnit.

Důležité: Jméno hostitele, uživatelské jméno a heslo si zapamatuj, protože jsou nutné pro připojení k serveru MySQL.

Instalace MySQL Connector/Python

Ovladač databáze je softwarový produkt, který umožňuje aplikaci připojit se k systému správy databází a komunikovat s ním. Programovací jazyky, jako je Python, vyžadují speciální ovladač pro interakci s databází specifickou pro daného dodavatele.

Takové ovladače se obvykle dodávají jako moduly třetích stran. Python Database API (DB-API) definuje standardní rozhraní, kterému musí vyhovovat všechny databázové ovladače pro Python. Podrobný popis najdeš v PEP 249. 

Všechny databázové ovladače Pythonu, jako například sqlite3 pro SQLite, psycopg pro PostgreSQL a MySQL Connector/Python pro MySQL, se řídí těmito implementačními pravidly.

Poznámka: V oficiální dokumentaci MySQL se místo termínu ovladač používá termín konektor. Z technického hlediska konektory zajišťují pouze připojení k databázi, ale ne interakci s ní. Tento termín se však často používá pro označení plnohodnotného modulu pro přístup k databázi, který obsahuje jak konektor, tak ovladač.

Pro zajištění konzistence s dokumentací bude pro MySQL používán termín konektor.

Nejpopulárnějším konektorem MySQL pro Python je MySQL Connector/Python. Můžeš jej nainstalovat pomocí pip (doporučuje se instalovat do prostředí virtuálního projektu):

pip install mysql-connector-python

Pokud se chceš ujistit, že instalace proběhla úspěšně, zadej do terminálu příkaz Python:

import mysql.connector

Pokud tento kód proběhne bez chyb, je mysql.connector nainstalován a připraven k použití. Pokud dojde k chybám, ujisti se, že používáš vhodné virtuální prostředí a správný interpreter jazyka Python.

související kurzy:

Praktické využití umělé inteligence

Tomáš Kubica

Cloud & AI Apps Senior Solution Engineer, Microsoft

O kurzu

Připojení k MySQL Server

MySQL je serverový systém řízení databází. Na jednom serveru může být umístěno několik databází. Než začneš s databází pracovat, musíš navázat spojení se serverem.

Proces spuštění programu v jazyce Python, který komunikuje s databází MySQL, se skládá z následujících kroků:

• vytvořit připojení k serveru MySQL,
• vytvořit databázi,
• připojit se k nově vytvořené nebo existující databázi,
• spustit dotaz SQL a získat výsledky,
• informovat databázi, pokud jsou v tabulce provedeny nějaké změny,
• uzavřít připojení k serveru MySQL.

Jedná se o obecný popis pracovního postupu. V různých aplikacích může být implementován různě, ale v každém případě je třeba nejprve připojit databázi k aplikaci.

Nastavení spojení

Prvním krokem při interakci se serverem MySQL je nastavení připojení. K tomu je třeba se připojit (connect()) k databázi z modulu mysql.connector. Tato funkce přebírá parametry host, user a password a vrací objekt MySQLConnection. Tyto přihlašovací údaje lze získat od uživatele a poté je předat metodě connect():

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Zadejte uživatelské jméno: "),
        password=getpass("Zadejte heslo: "),
    ) as connection:
        print(f"""
Spojení je navázáno:
host: {connection.server_host}
port: {connection.server_port}
user: {connection.user}""")
except Error as e:
    print(f"Chyba připojení k serveru:\n{e}")

Výše uvedený kód požaduje pověření pro připojení k serveru MySQL. Výsledkem je objekt MySQLConnection uložený v proměnné connection. Teď můžeš tuto proměnnou použít k interakci se serverem.

Výsledkem provedení výše uvedeného kódu bude přibližně následující výstup:

V tomto kódu je několik důležitých bodů:

• Výjimky, které mohou nastat při navazování spojení se serverem MySQL, bys měl*a vždy ošetřit. K tomu použij blok try… except. Ten zachytí výjimku a zobrazí její zprávu.
• Po ukončení práce s databází připojení vždy uzavři. Ponechání zbytečně otevřeného připojení může vést k neočekávaným chybám a snížení výkonu. Výše uvedený kód používá blok with k vyčištění paměti od dat, která byla potřebná pro připojení.
• Ve skriptu Pythonu nikdy nezadávej přihlašovací údaje (uživatelské jméno a heslo) natvrdo. Zakódování těchto pověření napevno představuje bezpečnostní riziko. Ve výše uvedeném kódu jsou pověření získána od uživatele a ke skrytí hesla je použit modul getpass. I když je to lepší než kódování natvrdo, existují bezpečnější způsoby ukládání citlivých informací, například v proměnných prostředí.

Vytvořil*a jsi tedy spojení mezi aplikací a serverem MySQL. Teď je třeba vytvořit databázi nebo se připojit k existující databázi na serveru.

související kurzy:

Home Assistant: Průvodce vaší chytrou domácností

Pavel Vejnar

Senior IoT Developer

O kurzu

Vytvoření databáze

Pokud chceš vytvořit databázi, spusť následující příkaz SQL:

CREATE DATABASE movies_db;

Vytvoří databázi s názvem movies_db.

Poznámka. V MySQL je třeba příkaz ukončit středníkem (;), aby byl dotaz ukončen. MySQL Connector/Python však středník na konec dotazu přidává automaticky, takže jej v kódu Pythonu nemusíš používat.

Pokud chceš provést dotaz SQL v jazyce Python, musíš použít objekt kurzoru, který abstrahuje přístup k záznamům databáze. MySQL Connector/Python poskytuje metodu MySQLCursor, jejíž instance jsou objekty, které mohou provádět dotazy MySQL v jazyce Python. Instance metody MySQLCursor se také nazývá cursor.

Objekty cursor používají objekt MySQLConnection pro interakci se serverem MySQL. Cursor vytvoříš pomocí metody .cursor() proměnné connection:

cursor = connection.cursor()

Po provedení tohoto kódu získáš instanci metody MySQLCursor.

Dotaz, který chceš provést, se předá metodě cursor.execute() ve formátu řetězce. V našem konkrétním případě této metodě pošli požadavek CREATE DATABASE. Změníme obsah bloku po deklaraci proměnné connection:

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Zadejte uživatelské jméno: "),
        password=getpass("Zadejte heslo: "),
    ) as connection:
        query = "CREATE DATABASE movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(query)
except Error as e:
    print(e)

Po provedení výše uvedeného kódu se na serveru MySQL objeví databáze s názvem movie_rating.

Dotaz CREATE DATABASE je uložen v řetězcové podobě v proměnné query a poté předán metodě cursor.execute() k provedení. K realizaci procesu čištění používá výše uvedený kód správce kontextu s objektem cursor.

Pokud na serveru již existuje databáze se stejným názvem, může se zobrazit chybová zpráva. Pro kontrolu můžeš zobrazit seznam všech databází na serveru. Pomocí objektu MySQLConnection, který jsme vytvořili, proveď dotaz SHOW DATABASES.

související kurzy:

Kubernetes

Radek Rous

Lead Software Developer v Riganti Software Team Lead v Itixo

O kurzu

Za tímto účelem opět změníme obsah bloku dotazů:

query = "SHOW DATABASES"
with connection.cursor() as cursor:
cursor.execute(query)
for db in cursor:
print(db)

Spusťme aktualizovaný kód a získejme seznam databází:

Příkaz SHOW DATABASES zobrazí nejen databáze, které jsi vytvořil*a, ale také některé další databáze, například information_schema, performance_schema atd. Tyto databáze jsou vytvořeny automaticky serverem MySQL a poskytují přístup k různým metadatům databáze a nastavení serveru MySQL.

Připojení k existující databázi

K hotové databázi se můžeš připojit, jak se to často děje v reálném životě, pomocí stejné metody connect(), jen jí teď musíš předat další parametr – databasei:

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Zadejte uživatelské jméno: "),
        password=getpass("Zadejte heslo: "),
        database="movie_rating",
    ) as connection:
        print(f"""
Spojení je navázáno:
host: {connection.server_host}
port: {connection.server_port}
user: {connection.user}
database: {connection.database}""")
except Error as e:
    print(f"Chyba připojení k serveru:\n{e}")

Při navázání spojení se název databáze uloží do vlastnosti connection.database:

Výše uvedený kód je založen na kódu z prvního příkladu připojení. V předchozím kódu byl přidán řádek pro předání názvu databáze v parametru database a řádek pro zobrazení jejího názvu. Výsledkem provedení tohoto kódu tedy bude navázání spojení s databází movie_rating.

Vytvoření, úprava a odstranění tabulky

V této části budeš používat několik základních dotazů DDL (Data Definition Language), včetně CREATE, DROP a ALTER, z kódu Pythonu. Projdeš si strukturu databáze MySQL, kterou budeš používat v průběhu celého kurzu. Vytvoříš také všechny potřebné tabulky a naučíš se je upravovat.

související články:

Integrace NumPy s Pythonem pro efektivní strojové učení

NumPy v praxi: Reálné praktické příklady pro AI developery

Popis schématu databáze

Začněme vytvářet databázové schéma pro systém hodnocení filmů. Databáze se bude skládat ze tří tabulek:

• movies obsahuje obecné informace o filmech a má následující atributy:

- id
- title
- release_year
- genre
- collection_in_mil

• reviewers obsahuje informace o recenzentech a má následující atributy:

- id
- first_name
- last_name

• ratings obsahuje informace o přidělených hodnoceních a má následující atributy:

- movie_id (vnější klíč)
- reviewer_id (vnější klíč)
- rating

Skutečný systém (jako IMDb) by obsahoval mnohem více informací: e-mailové adresy, seznamy herců atd. Pokud chceš, můžeš do této databáze přidat nové tabulky a atributy, ale pro tento výukový program budou popsané tři tabulky stačit.

Níže je uveden přehled naší databáze:

Tabulky movies a reviewers jsou propojeny vztahem many-to-many, protože stejný film může recenzovat několik recenzentů a jeden recenzent může recenzovat několik filmů.

Tabulka ratings propojuje tabulky movies a reviewers.

Vytváření tabulek pomocí příkazu CREATE TABLE

Pro vytvoření nové tabulky v systému MySQL teď použijeme příkaz CREATE TABLE. Následující dotaz MySQL vytvoří tabulku movies v databázi movie_rating:

CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)

Pokud již znáš příkazy SQL, většině výše uvedeného kódu porozumíš. Syntaxe MySQL je však poněkud odlišná.

MySQL například implementuje mnoho datových typů, včetně YEAR, INT, BIGINT atd. Kromě toho MySQL používá klíčové slovo AUTO_INCREMENT, které označuje, že hodnota sloupce má být po vložení nových řádků automaticky zvětšena.

Jestli chceš vytvořit tabulku, musíš tento dotaz předat metodě cursor.execute(), která přijme dotaz MySQL a provede jej v připojené databázi MySQL:

create_movies_table_query = """
CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)
"""
with connection.cursor() as cursor:
cursor.execute(create_movies_table_query)
connection.commit()

Všimni si také příkazu connection.commit() na konci kódu. Ve výchozím nastavení konektor MySQL automaticky neodepisuje transakce. Změny zadané v transakci se uplatní pouze tehdy, když je na jejím konci zadán příkaz COMMIT. Tuto metodu volej po každé transakci, aby se v tabulce uplatnily skutečné změny.

Stejně jako jsi vytvořil*a tabulku movies, vytvoř podle následujícího skriptu tabulku reviewers:

create_reviewers_table_query = """
CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
)
"""
with connection.cursor() as cursor:
cursor.execute(create_reviewers_table_query)
connection.commit()

V případě potřeby můžeš přidat další informace o osobě, která recenzi zanechala, například její e-mailovou adresu nebo datum narození. Pro naše účely postačí pole first_name (jméno) a last_name (příjmení).

Teď spusť následující skript pro vytvoření tabulky ratings:

create_ratings_table_query = """
CREATE TABLE ratings (
    movie_id INT,
    reviewer_id INT,
    rating DECIMAL(2,1),
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id),
    PRIMARY KEY(movie_id, reviewer_id)
)
"""
with connection.cursor() as cursor:
cursor.execute(create_ratings_table_query)
connection.commit()

Implementace vnějších klíčů v systému MySQL se mírně liší od standardního jazyka SQL. V MySQL musí nadřazený i podřízený vnější klíč používat stejný úložný stroj („storage engine“).

Úložný stroj je softwarová komponenta používaná v DBMS k provádění operací SQL.

MySQL používá dva typy úložných subsystémů:

• Subsystémy transakčního úložiště zajišťují bezpečné transakce a umožňují vracet transakce zpět pomocí jednoduchých příkazů, například rollback. Tento typ zahrnuje mnoho populárních subsystémů úložiště MySQL, například InnoDB a NDB.
• Netransakční subsystémy úložiště používají ručně vytvořený kód pro zrušení operací, jejichž akce jsou zaznamenány v databázi. MyISAM, MEMORY a mnoho dalších subsystémů úložiště MySQL jsou netransakční.

Ve výchozím nastavení se používá InnoDB, která je nejoblíbenější. Zajišťuje integritu dat podporou omezení cizích klíčů. To znamená, že každá operace CRUD nad cizím klíčem je kontrolována, aby se zabránilo nekonzistencím mezi tabulkami.

Všimni si také, že v tabulce ratings jsou sloupce movie_id a reviewer_id, které jsou vnějšími klíči, deklarovány jako primární klíče. To zabraňuje tomu, aby jedna osoba přiřadila hodnocení stejnému filmu dvakrát.

Stejný cursor můžeš použít k provedení více operací. Tímto způsobem budou všechna provedení představovat jednu atomickou transakci, nikoli několik samostatných transakcí. Například můžeš provést všechny příkazy CREATE TABLE pomocí jediného objektu kurzoru a poté transakci jednou odevzdat:

with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    cursor.execute(create_reviewers_table_query)
    cursor.execute(create_ratings_table_query)
    connection.commit()

Výše uvedený kód nejprve provede všechny tři příkazy CREATE. Poté odešli serveru MySQL příkaz COMMIT, který transakci odevzdá. Můžeš také použít metodu .rollback() pro odeslání příkazu ROLLBACK na server MySQL a zrušení všech změn dat, které transakce provedla.

související kurzy:

DevOps engineer

Petr Chloupek

Principal DevOps Engineer, Trask solutions a.s.

O kurzu

Zobrazení schématu tabulky pomocí příkazu DESCRIBE

Po vytvoření všech tří tabulek můžeš zobrazit jejich schémata pomocí následujícího příkazu SQL:

DESCRIBE <table_name>;

Pokud chceš získat výsledky z objektu cursor, použij metodu cursor.fetchall(). Tato metoda vrátí všechny řádky, které byly přijaty po posledním příkazu. Předpokládejme, že v proměnné connection již existuje objekt MySQLConnection. Můžeš zobrazit všechny výsledky vrácené metodou cursor.fetchall():

show_table_query = "DESCRIBE movies"
with connection.cursor() as cursor:
    cursor.execute(show_table_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

Získáme popis tabulky, kde je každý sloupec reprezentován jako tuple, který udává datový typ sloupce, zda se jedná o primární klíč atd.:

Změna rozložení tabulky pomocí příkazu ALTER

Tabulka movies má sloupec collection_in_mil, který obsahuje tržby z pokladen v milionech dolarů. Pomocí následujícího příkazu MySQL změň datový typ atributu collection_in_mil z INT na DECIMAL:

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4;1) znamená desetinné číslo složené z maximálně 4 číslic, z nichž jedna představuje desetinu, například 120,1, 3,4, 38,0 atd. Po provedení příkazu ALTER TABLE můžeš zobrazit aktualizované rozložení tabulky pomocí výše popsaného příkazu DESCRIBE.

Definujme dva dotazy:

alter_table_query = """
ALTER TABLE movies
MODIFY COLUMN collection_in_mil DECIMAL(4,1)
"""
show_table_query = "DESCRIBE movies"

Provedeme je po připojení:

with connection.cursor() as cursor:
    cursor.execute(alter_table_query)
    cursor.execute(show_table_query)
    result = cursor.fetchall()
    print("Schéma tabulky movie po úpravě:")
    for row in result:
        print(row)

Výsledek:

Schéma tabulky movie po úpravě:

Odstranění tabulek pomocí příkazu DROP

Pokud chceš tabulku odstranit, použij příkaz DROP. Odstranění tabulky je nevratný proces.

Pokud provedeš následující kód, budeš muset znovu odeslat požadavek CREATE TABLE, abys mohl*a tabulku hodnocení používat ve zbytku tohoto kurzu.

Pokud chceš tabulku hodnocení odstranit, předej požadavek na odstranění tabulky metodě cursor.execute():

drop_table_query = "DROP TABLE ratings"
with connection.cursor() as cursor:
    cursor.execute(drop_table_query)

Tímto končí první část výukového programu Python a MySQL. V příští části se naučíš, jak naplnit tabulku daty, číst záznamy z jedné nebo více tabulek a upravovat data a mazat záznamy z tabulky.

Autor: Andriy Denysenko