5. Databáze - SQLite

• seznámení s SQLite databází
• vytvoření vlastní databáze a několika tabulek
• orientace v sql query
• připojení k databázi ve flasku

• SQLite
• Python - sqlite3
• Command Line Shell For SQLite
• Flask-SQLalchemy
• W3Schools - SQL

Databáze jsou nezbytné pro správu a ukládání dat v aplikacích. Bez nich bychom nemohli efektivně ukládat, vyhledávat, aktualizovat a mazat data, což by znamenalo, že bychom museli spravovat data ručně, což by bylo časově a finančně náročné.

Co je to databáze?

Databáze je organizovaný soubor strukturovaných informací neboli dat, které se obvykle ukládají v elektronické podobě v počítačovém systému. Databáze je obvykle řízena systémem pro správu databáze (DBMS – database management system) . Data a systém DBMS společně s přidruženými aplikacemi se označují jako databázový systém, často zkráceně jako databáze.

SQL - Structured Query Language

SQL je programovací jazyk používaný téměř všemi relačními databázemi k vytváření dotazů, manipulaci s daty, definování dat a k řízení přístupu. Jazyk SQL vyvinula v 70. letech společnost IBM ve spolupráci se společností Oracle a vznikla implementace standardu SQL ANSI. Jazyk SQL získal mnoho rozšíření od společností jako například IBM, Oracle a Microsoft. Přestože se jazyk SQL stále hojně používá, začínají se objevovat nové programovací jazyky.

Historie

Databáze od svého vzniku na počátku 60. let zaznamenaly dramatický vývoj. Prvními systémy, které se používaly k ukládání dat a manipulaci s nimi, byly navigační databáze, jako je hierarchická databáze (která se opírala o stromový model a umožňovala pouze vztahy typu one-to-many – „jeden k více“) a síťová databáze (pružnější model, který umožňoval různé vztahy). Tyto první systémy byly jednoduché, ale nepružné. V 80. letech získali popularitu relační databáze a v 90. letech následovaly objektově orientované databáze. V poslední době vznikly databáze NoSQL jako reakce na růst Internetu a potřebu vyšší rychlosti a zpracování nestrukturovaných dat. Pokud se jedná o to, jakým způsobem probíhá shromažďování, ukládaní, správa a využívání dat, stávají se dnes základem cloudové databáze a autonomně řízené databáze.

Databáze vs Tabulkový procesor

Databáze i tabulkové procesory (například Microsoft Excel) představují pohodlný způsob ukládání informací. Hlavní rozdíly mezi nimi jsou:

• Jakým způsobem jsou data uložena a jak probíhá manipulace s nimi
• Kdo má přístup k datům
• Jaké množství dat lze ukládat

Tabulkové procesory byly původně určeny pro jednoho uživatele a jejich charakteristika tomu odpovídá. Jsou vynikající pro samostatnou práci nebo malý počet uživatelů, kteří nepotřebují provádět množství komplikovaných manipulací s daty. Na druhou stranu jsou databáze určeny k tomu, aby uchovávaly mnohem větší soubor organizovaných informací, někdy v masivním objemu. Databáze umožňují více uživatelům současně rychlý a bezpečný přístup k datům a zadávání dotazů s využitím velmi komplexní logiky a jazyka.

Existuje mnoho různých typů databází. Jaká je nejvhodnější databáze pro konkrétní aplikaci závisí na tom, jakým způsobem zamýšlí data používat.

Relační databáze

Relační databáze se staly dominantními v 80. letech 20. století. Položky v relační databázi jsou uspořádány jako sada tabulek s řádky a sloupci. Relační databázová technologie poskytuje nejefektivnější a nejflexibilnější způsob přístupu ke strukturovaným informacím.

Objektově orientované databáze

Informace v objektově orientované databázi jsou reprezentovány ve formě objektů, podobně jako v objektově orientovaném programování.

Distribuované databáze

Distribuovaná databáze se skládá ze dvou nebo více souborů, které se nachází v různých umístěních. Databáze může být uložena na více počítačích, může být ve stejném fyzickém místě nebo distrubuována v odlišných sítích.

Datové sklady

Centrální úložiště dat, datový sklad, je typ databáze speciálně určený pro rychlé zadávání dotazů a analýzu.

NoSQL databáze

NoSQL neboli nerelační databáze umožňuje ukládání a manipulaci s nestrukturovanými a částečně strukturovanými daty (na rozdíl od relační databáze, která definuje formát veškerých dat, která lze do databáze uložit). Databáze NoSQL Database získaly popularitu v souvislosti se zvýšeným zájmem o webové aplikace a jejich větší složitost.

Databázový software se používá k vytváření, úpravám a údržbě databázových souborů a záznamů, což umožňuje snazší vytváření souborů a záznamů, zadávání dat, úpravy dat, aktualizaci a vytváření sestav. Tento software také zpracovává ukládání dat, zálohování a hlášení, řízení více přístupů a zabezpečení. Silné zabezpečení databáze je dnes obzvláště důležité, protože krádeže dat jsou stále častější. Databázový software se někdy označuje jako „systém správy databáze“ (DBMS).

Databázový software zjednodušuje správu dat tím, že umožňuje uživatelům ukládat data ve strukturované formě a poté k nim přistupovat. Obvykle má grafické rozhraní, které pomáhá vytvářet a spravovat data, a v některých případech mohou uživatelé pomocí databázového softwaru budovat své vlastní databáze.

Databáze obvykle vyžaduje komplexní databázový softwarový program označovaný jako systém pro správu databáze (DBMS). DBMS slouží jako rozhraní mezi databází a jejími koncovými uživateli nebo programy. Umožňuje uživatelům získávat, aktualizovat a spravovat databázi a určovat, jakým způsobem jsou v ní uložené informace organizovány a optimalizovány. Systém DBMS také usnadňuje přehled o databázích a jejich řízení, což umožňuje řadu správcovských činností, například monitorování výkonu, ladění a operace zálohování a obnovení.

Jako příklad oblíbeného databázového softwaru neboli DBMS lze uvést MySQL, Microsoft Access, Microsoft SQL Server, FileMaker Pro, Oracle Database a dBASE.

Dnešní velké podnikové databáze často podporují velmi komplexní dotazy a očekává se od nich, že na ně budou reagovat téměř okamžitě. Výsledkem je, že správci databází jsou pod neustálým tlakem, aby používali širokou škálu metod, které pomáhají zlepšit výkon. K výzvám, se kterými se často setkávají, patří tyto:

Reakce na významné zvýšení objemu dat

Explozivní nárůst dat přicházejících ze senzorů, připojených počítačů a desítek dalších zdrojů udržuje správce databází v pohotovosti, aby dokázali efektivně spravovat a organizovat data svých podniků.

Zajištění bezpečnosti dat

Incidenty spojené s narušením dat bývají dnes všudypřítomné a hackeři jsou stále vynalézavější. Dnes je důležitější než kdykoliv jindy, aby byla zajištěna bezpečnost dat, ale musí být také snadno přístupná uživatelům.

Udržení kroku s poptávkou

V dnešním rychle se rozvíjejícím podnikatelském prostředí potřebují podniky přístup ke svým datům v reálném čase, což umožňuje včasné rozhodování a využití nových příležitostí.

Správa a údržba databáze a infrastruktury

Správci databáze musí průběžně sledovat problémy databáze a provádět preventivní údržbu a také nasazovat softwarové aktualizace a opravy. Vzhledem k tomu, že databáze jsou stále složitější a narůstá objem dat, stojí podniky před problémem nárůstu nákladů a získávání odborníků, kteří by dokázali databáze monitorovat a vyladit k lepšímu výkonu.

Odstranění limitů škálovatelnosti

Pokud má podnik přežít, musí růst, stejně jako s ním musí růst správa podnikových dat. Pro správce databází je však velmi obtížné předvídat, jakou kapacitu bude společnost potřebovat, zejména u databází on-premises.

Požadavky na místo uložení, nezávislost a latenci dat

Některé organizace mají případy použití, které je vhodnější provozovat v místním prostředí. V těchto případech jsou ideální optimalizované systémy, které jsou předem konfigurované a optimalizované pro běh databáze.

SQLite je lehká, samostatná, souborová SQL databáze, která je součástí standardní knihovny Pythonu. To znamená, že SQLite je integrována přímo do Pythonu a umožňuje pracovat s databází bez nutnosti instalovat další software. SQLite je ideální pro aplikace, které potřebují malou, lehkou a rychlou databázi, která nevyžaduje komplexní správu dat.

Pro práci s SQLite v Pythonu se používá modul sqlite3. Tento modul poskytuje rozhraní kompatibilní s DB-API 2.0, které umožňuje snadnou interakci s SQLite databázemi. Modul sqlite3 umožňuje vytvářet, číst, aktualizovat a mazat data v SQLite databázi přímo z Pythonu.

Vytvoření databáze

Pro vystvoření nové databáze stačí v adresáři, ve kterém chcete databázi vytvořit zadat příkaz:

  sqlite3 <jméno databáze>.db

Stejný příkaz lze použít pro otevření již existující databáze.

Po zadání příkazu by se měl otevřít Command Line Shell For SQLite, který by měl vypadat nějak takto:

Při spuštění Command Line Shell For SQLite se zobrazí krátká zpráva s bannerem a poté budete vyzváni k zadání SQL příkazu.

  SQLite version 3.43.2 2023-10-10 13:08:14
  Enter ".help" for usage hints.
  sqlite:

Vytvoření tabulky:

  CREATE TABLE DATA (
    id INTEGER PRIMARY KEY,
    time_stamp TEXT NOT NULL,
    temperature REAL NOT NULL
  );

Vložení záznamu do tabulky:

  INSERT INTO DATA (time_stamp, temperature) VALUES ('2024-01-01 10:00:00', 22.5);

Python skript pro vytvoření datasetu:

import sqlite3
from datetime import datetime, timedelta
import random
import os
 
numRecords = 10
dbFile = os.path.join(os.getcwd(), 'database.db')
tableName = 'data'
minTemp = 20.0
maxTemp = 30.0
startDate = '2024-03-17 00:00:00'
period = 300
 
 
conn = sqlite3.connect(dbFile)
cursor = conn.cursor()
 
createQuery = f"""
CREATE TABLE IF NOT EXISTS {tableName} (
    id INTEGER PRIMARY KEY,
    timestamp TEXT NOT NULL,
    temperature REAL NOT NULL
);
"""
cursor.execute(createQuery)
 
countQuery = f"SELECT COUNT(*) FROM {tableName}"
cursor.execute(countQuery)
count = cursor.fetchone()[0]
 
if count == 0:
    startTime = datetime.strptime(startDate, '%Y-%m-%d %H:%M:%S')
else:
    maxTimestampQuery = f"SELECT MAX(timestamp) FROM {tableName}"
    cursor.execute(maxTimestampQuery)
    maxTimestamp = cursor.fetchone()[0]
    maxTimestampDatetime = datetime.strptime(maxTimestamp, '%Y-%m-%d %H:%M:%S')
    startTime = maxTimestampDatetime + timedelta(seconds=period)
 
for i in range(numRecords):
    timestamp = startTime + timedelta(seconds=i*period)
    temperature = random.uniform(minTemp, maxTemp)
    insertQuery = f"INSERT INTO {tableName} (timestamp, temperature) VALUES (?, ?)"
    cursor.execute(insertQuery, (timestamp.strftime('%Y-%m-%d %H:%M:%S'), temperature))
 
conn.commit()
conn.close()

Připojení k databázi:

Připojení k SQLite databázi se provádí pomocí funkce sqlite3.connect(), která vrací objekt Connection. Tento objekt se používá pro interakci s databází.

import sqlite3
connection = sqlite3.connect("aquarium.db")

Kurzor

Pro provádění SQL dotazů a manipulaci s daty v databázi je potřeba vytvořit kurzor pomocí metody cursor() na objektu Connection. Kurzor umožňuje spouštět SQL dotazy a získávat výsledky.

cursor = connection.cursor()

Vykonání SQL příkazu:

Veškerá interakce s databází realizovaná standardními SQL příkazi je v pythonu ralizovatelná pomocí metody execute().

cursor.execute("""
CREATE TABLE movie(
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    year INTEGER,
    score REAL
)
""")
 
##########
 
cursor.execute("INSERT INTO movie (title, year, score) VALUES (?, ?, ?)",
               ('Inception', 2010, 8.8))
 
##########
 
movies = [('The Matrix', 1999, 8.7), ('The Dark Knight', 2008, 9.0)]
cursor.executemany("INSERT INTO movie (title, year, score) VALUES (?, ?, ?)", movies)
 
##########
 
cursor.execute("SELECT * FROM movie")
rows = cursor.fetchall()
for row in rows:
    print(row)
 

Commit:

Po provedení operací vkládání, aktualizace nebo mazání dat je důležité provést commit změn pomocí metody commit() na objektu Connection, aby se změny uložily do databáze:

connection.commit()

Ukončení spojení:

Po dokončení práce s databází je důležité připojení zavřít pomocí metody close() na objektu Connection. To uvolní zdroje a ukončí připojení k databázi.

connection.close()

ORM (Object-Relational Mapping) je technika, která umožňuje dotazovat a manipulovat s daty z databáze pomocí objektově orientovaného paradigmatu. Když se hovoří o ORM, většina lidí se odkazuje na knihovnu, která implementuje tuto techniku. ORM knihovna je běžná knihovna napsaná v jazyce podle vašeho výběru, která zapouzdřuje kód potřebný k manipulaci s daty, takže už nepotřebujete používat SQL; místo toho interagujete přímo s objektem ve stejném jazyce, který používáte.

ORM je užitečný pro vytváření “můstku” mezi objektově orientovanými programy a většinou relativními databázemi. Když pracujete s databází pomocí OOP jazyků, budete muset provádět různé operace, jako je vytváření, čtení, aktualizace a mazání dat z databáze. Navržením je, že pro provádění těchto operací v relačních databázích používáte SQL. Přestože použití SQL pro tento účel není nutně špatné, ORM a ORM nástroje pomáhají zjednodušit interakci mezi relačními databázemi a různými OOP jazyky

Mějme třídu Book, kterou chceme použít k načtení všech knih od autora “Linus”. Manuálně byste to mohli udělat takto:

book_list = new List();
sql = "SELECT book FROM library WHERE author = 'Linus'";
data = query(sql);
while (row = data.next()) {
     book = new Book();
     book.setAuthor(row.get('author'));
     book_list.add(book);
}

Pomocí ORM by to mohlo vypadat takto:

book_list = BookTable.query(author="Linus");

SQLAlchemy je výkonný a flexibilní SQL toolkit a ORM (Object Relational Mapper) pro Python, který umožňuje vývojářům pracovat s SQL databázemi pomocí Pythonic domain language. Tento nástroj poskytuje vývojářům možnost psát dotazy jako řetězce nebo zřetězení Pythonových objektů pro podobné dotazy, což umožňuje vytváření vysokovýkonných SQL-založených aplikací

SQLAlchemy je postaven na dvou základních API, známých jako Core a ORM. SQLAlchemy Core poskytuje nástroje pro správu připojení k databázi, interakci s databázovými dotazy a výsledky, a programovou konstrukci SQL příkazů. ORM API je postaven na Core a umožňuje vývojářům pracovat s databázemi pomocí objektově orientovaného paradigmatu.

### SQLAlchemy Core
from sqlalchemy import create_engine, MetaData, Table, String, Column, Integer, text
 
# Vytvoření databázového engine
engine = create_engine('mysql://user:password@localhost/mydatabase')
 
metadata = MetaData()
 
users = Table('users', metadata,
    Column('id', Integer(), primary_key=True),
    Column('name', String(128))
)
 
# Vytvoření databázové tabulky
metadata.create_all(engine)
 
# Vytvoření nového uživatele pomocí SQLAlchemy Core
ins = users.insert().values(name='Areola Daniel')
 
# Připojení k databázi
conn = engine.connect()
 
# Spuštění funkce insert
conn.execute(ins)
 
################################################################################
### SQLAlchemy ORM
 
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
 
# Vytvoření databázového modelu pomocí SQLAlchemy ORM
Base = declarative_base()
 
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(128))
 
# Vytvoření databázového engine
engine = create_engine('mysql://user:password@localhost/mydatabase')
 
# Vytvoření databázové tabulky
Base.metadata.create_all(engine)
 
# Vytvoření nového uživatele pomocí SQLAlchemy ORM
new_user = User(name='Areola Daniel')
 
# Přidání nového uživatele do databáze pomocí SQLAlchemy ORM session
session = Session(engine)
session.add(new_user)
session.commit()
session.close()

Připojení k databázi v SQLAlchemy se provádí pomocí Enginu, který je zodpovědný za správu připojení k databázi. Engine je konfigurován pomocí URI databáze a dalších parametrů, jako je například typ databáze, jméno uživatele a heslo.

SQLAlchemy umožňuje práci s transakcemi a DBAPI (Database API), což umožňuje vývojářům spravovat databázové transakce a interakci s databázemi na nízké úrovni. To zahrnuje vytváření, čtení, aktualizaci a mazání dat, stejně jako správu databázových schémat.

SQLAlchemy poskytuje silné nástroje pro práci s metadaty databáze a daty. To zahrnuje vytváření a modifikaci tabulek, definování a manipulaci s daty pomocí ORM, a práci s databázovými schématy.

Flask-SQLAlchemy je rozšíření pro Flask, které přidává podporu pro SQLAlchemy.

Instalace a inicializace

Instalace pomocí PIPu: pip install Flask-SQLAlchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

Flask-SQLAlchemy umožňuje konfiguraci pomocí klíčů v konfiguračním slovníku Flask aplikace. Například SQLALCHEMY_DATABASE_URI definuje URI pro připojení k databázi.

Definování modelu

Modely v Flask-SQLAlchemy jsou definovány jako třídy, které dědí z db.Model. Každá třída reprezentuje tabulku v databázi a její atributy reprezentují sloupce v tabulce.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
 
    def __repr__(self):
        return '<User %r>' % self.username

Vytvoření tabulek

Po definování modelů můžete vytvořit tabulky v databázi pomocí metody create_all() na objektu SQLAlchemy.

db.create_all()

Práce s daty

Flask-SQLAlchemy poskytuje metody pro vkládání, čtení, aktualizaci a mazání dat v databázi. Například pro vložení nového uživatele do databáze:

user = User(username='john', email='john@example.com')
db.session.add(user)
db.session.commit()

A pro dotazování dat:

users = User.query.all()

Rozšiřte svou aplikaci o SQLite databázi. Hodnoty místo slovníkového seznamu ukládejte do tabulky v databázi. Dokončete systém přihlašování a registrace uživatelů.

• Unordered List ItemDatabáze bude mít alespoň 2 tabulky: data, users
• V tabulce data se budou nacházet (zatím nasimulované - náhodné) hodnoty teploty (datový typ = REAL) a timesptamp ve standardním formátu.
• V tabulce data se budou nacházet záznamy uživatelů. Heslo uživatele nesmí být ve formě otevřeného textu - použijte libovolný šifrovací mechanizmus (např. sha256)
• Pro práci s databází použijte modul sqlite3 nebo flask-SQLAlchemy
• Nezapomeňte otestovat funkčnost přístupu k databázi jak prostřednictvím webové aplikace, tak i vašeho API

V tuto chvíli by měla být vaše aplikace hotová a pro budoucí úkoly by nemělo být nutné aplikaci nijak upravovat.

Odevzdání:

• Ukázat funkční aplikaci na cvičení,
• Nahrát na fakultní GitLab (commit již existujícího projektu),
• Do brute nahrát url na commit.