Бази данни

Проблем

Каталог във файл

Каталогът на магазина е CSV файл, както в модула за файловете. Докато продуктите са сто, става. После стават сто хиляди и се появяват проблеми:

• за да намериш един продукт, четеш целия файл;
• „всички под 5 €, подредени по цена“ е цикъл, if и сортиране, писани на ръка;
• две каси пишат във файла едновременно и единият запис се губи или файлът се поврежда;
• токът спира по средата на записа и половината файл го няма.

Затова данните на почти всяка истинска програма са в база данни: програма, чиято работа е да пази данни, да ги търси бързо и да не ги губи.

Идеята

Таблици и SQL

Релационната база пази данните в таблици. Таблицата има колони (име, тип) и редове (по един за всеки продукт). Колоната id е първичен ключ: стойността ѝ е различна за всеки ред, затова по нея се намира точно един.

С базата се говори на езика SQL. Петте най-чести команди:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Кафе', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE прави таблица, INSERT добавя ред, UPDATE променя, DELETE трие.
• SELECT чете: кои колони, от коя таблица, при какво условие (WHERE) и в какъв ред (ORDER BY).

Ти казваш какво искаш, а базата решава как да го намери бързо.

По-точно, за любопитните

• Известни релационни бази са PostgreSQL, MySQL, Oracle и SQL Server. Те работят като отделни сървъри. Тук ползваме SQLite: малка база, която живее във файл или изцяло в паметта на програмата. Модулът sqlite3 е в стандартната библиотека на Python. Адресът ":memory:" е база в паметта: всяко пускане започва с празна.
• Текстът в SQL е в единични кавички: 'Кафе'. В PostgreSQL и MySQL DECIMAL(8, 2) е точно число с 2 знака след запетаята, подходящо за пари. SQLite не пази точни десетични числа: там 7.50 е обикновено число с плаваща запетая.

Виж го

sqlite3: връзка, заявка, резултат

Модулът sqlite3 следва стандарта на Python за бази данни (DB-API, PEP 249): същите connect, execute, commit и close има и при PostgreSQL и MySQL. Връзката е Connection, а резултатът от заявка е курсор. Функцията setup прави таблицата и добавя три продукта:

def setup(conn):
    conn.execute("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))")
    conn.execute("INSERT INTO products VALUES (1, 'Кафе', 7.50), (2, 'Чай', 3.20), (3, 'Шоколад', 2.40)")
    conn.commit()

Четем редовете и правим от всеки обект от dataclass-а Product с полета id, name и price:

@dataclass
class Product:
    id: int
    name: str
    price: float

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Изход

Product(id=3, name='Шоколад', price=2.4)
Product(id=2, name='Чай', price=3.2)
Product(id=1, name='Кафе', price=7.5)

• sqlite3.connect(адрес) отваря връзката. Адресът казва коя база: тук ":memory:", или име на файл като "shop.db".
• conn.execute(sql) праща всяка команда и връща курсор. for по курсора дава ред по ред, всеки като кортеж от колоните в реда на SELECT. Тук кортежът се разпакетира в три променливи.
• Преди INSERT, UPDATE и DELETE sqlite3 сам започва транзакция, а conn.commit() записва промените. Без него промените в база във файл се губят при затваряне.
• conn.close() затваря връзката. (with conn: не я затваря: той прави транзакция, ще го видим по-долу.)
• Грешките са изключения от sqlite3.Error, например OperationalError за грешен SQL и IntegrityError за нарушено правило на таблицата. Проверени изключения в Python няма.

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Стъпка по стъпка, с думи

1. Връзка с базата в паметта. setup прави таблицата products и добавя 3 реда.
2. Празен списък, в който ще влязат обектите.
3. SELECT с ORDER BY price: conn.execute връща курсор към редовете, от най-евтиния.
4. Цикълът взима ред 1 като кортеж и го разпакетира в три променливи, а Product(...) прави от тях обект: Шоколад.
5. Цикълът взима ред 2 като кортеж и го разпакетира в три променливи, а Product(...) прави от тях обект: Чай.
6. Цикълът взима ред 3 като кортеж и го разпакетира в три променливи, а Product(...) прави от тях обект: Кафе.
7. Курсорът няма повече редове и цикълът спира.
8. Списъкът е от обикновени обекти. Печатът показва dataclass-а с полетата му.

По-точно, за любопитните

• С истинска база на сървър адресът е друг и има потребител и парола. Също трябва и драйверът на базата като зависимост в проекта (за MySQL: пакетът mysql-connector-python):

import mysql.connector

conn = mysql.connector.connect(host="localhost", port=3306, database="shop", user="shop_app", password="парола")
print("Връзката с базата е успешна!")
conn.close()

• Паролата не се пише в кода, а се чете от настройките или от променлива на средата (os.environ).

• Цените тук са float, за да е кратко. В истински магазин парите се пазят точно: като DECIMAL в PostgreSQL или MySQL (в Python те идват като decimal.Decimal), а в SQLite като цели стотинки.
• С conn.row_factory = sqlite3.Row всеки ред може да се чете и по име на колона: row["name"].

Пробвай

Кое ще излезе?

Продуктите са Кафе 7,50 €, Чай 3,20 € и Шоколад 2,40 €. Какво ще отпечата програмата?

conn = sqlite3.connect(":memory:")
setup(conn)
for row in conn.execute("SELECT name FROM products WHERE price < 5 ORDER BY price DESC"):
    print(row[0])
conn.close()

Отговор

Чай
Шоколад

Кафе
Чай
Шоколад

Шоколад
Чай

Провери

Проблем

Търсачката, която показва всичко

Сайтът търси продукт по име, което потребителят пише. Заявката се сглобява с f-string:

def search(conn, name):
    sql = f"SELECT name, price FROM products WHERE name = '{name}'"
    for found, price in conn.execute(sql):
        print(f"  {found}: {price} €")

conn = sqlite3.connect(":memory:")
setup(conn)
print("Търся: Чай")
search(conn, "Чай")
print("Търся: x' OR '1'='1")
search(conn, "x' OR '1'='1")
conn.close()

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1
  Кафе: 7.5 €
  Чай: 3.2 €
  Шоколад: 2.4 €

С „Чай“ работи. С x' OR '1'='1 заявката става ... WHERE name = 'x' OR '1'='1'. Условието е винаги вярно и излиза цялата таблица. Входът на потребителя е станал част от SQL. Това е SQL injection, една от най-честите атаки срещу сайтове. Със същия трик може да се влезе без парола или да се изтрие таблица.

Идеята

Параметри с ?

Решението е заявката и стойностите да вървят отделно. Заявката има ? на местата на стойностите, а те се подават като кортеж, вторият аргумент на execute. (name,) е кортеж с една стойност: запетаята е задължителна.

def search(conn, name):
    sql = "SELECT name, price FROM products WHERE name = ?"
    for found, price in conn.execute(sql, (name,)):
        print(f"  {found}: {price} €")

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1

Сега x' OR '1'='1 е просто текст, който се търси като име. Такъв продукт няма и не излиза нищо. Каквото и да напише потребителят, то не може да стане SQL.

Правилото е просто: стойност от потребителя, от файл или от мрежата никога не се долепя в SQL. Винаги ?.

Пробвай

Кафе с апостроф

Функцията преименува продукт. С името Кафе 'Арабика' програмата спира с sqlite3.OperationalError: near "Арабика": syntax error. На кой ред е грешката?

Избери реда с грешката.

1def rename(conn, product_id, new_name):2 conn.execute(f"UPDATE products SET name = '{new_name}' WHERE id = {product_id}")3 conn.commit()

Провери

Идеята

Транзакция: всичко или нищо

Ана превежда 150 € на Борис. Това са две заявки: добави на Борис, извади от Ана. В таблицата има условие CHECK (balance >= 0), затова втората заявка се проваля: Ана има само 100 €.

def open_accounts(conn):
    conn.execute("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))")
    conn.execute("INSERT INTO accounts VALUES (1, 'Ана', 100), (2, 'Борис', 50)")
    conn.commit()


def move(conn, account_id, amount):
    conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, account_id))


def print_balances(conn):
    for owner, balance in conn.execute("SELECT owner, balance FROM accounts ORDER BY id"):
        print(f"  {owner}: {balance} €")

def transfer(conn, source, target, amount):
    move(conn, target, amount)
    conn.commit()
    move(conn, source, -amount)
    conn.commit()

conn = sqlite3.connect(":memory:")
open_accounts(conn)
try:
    transfer(conn, 1, 2, 150)
except sqlite3.Error:
    print("Преводът на 150 € е отказан.")
print_balances(conn)
conn.close()

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 200 €

Преводът е отказан, но Борис вече е получил 150 € от нищото: всяко преместване е записано веднага с commit(). Двете заявки трябва да са транзакция: или минават и двете, или нито една. В Python това прави with conn:.

def transfer(conn, source, target, amount):
    with conn:
        move(conn, target, amount)
        move(conn, source, -amount)

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 50 €

• Първият UPDATE започва транзакция: промените още не са окончателни.
• Ако блокът with conn: свърши без грешка, той вика conn.commit() и записва всичко наведнъж.
• Ако вътре има изключение, той вика conn.rollback() и отменя всичко. Изключението продължава нагоре, за да разбере и викащият.
• open_accounts вика commit() накрая. Без него и създаването на сметките щеше да е в същата транзакция и rollback() щеше да отмени и него.

По-точно, за любопитните

• Изключението при провалената заявка е IntegrityError със съобщение CHECK constraint failed: no_debt. no_debt е името на условието: в таблицата то е balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Базите обещават четири неща за транзакциите, известни като ACID: всичко или нищо (atomicity), данните остават верни според правилата на таблиците (consistency), едновременните транзакции не си пречат (isolation) и записаното не се губи дори при спрял ток (durability).

Идеята

ORM: обекти вместо редове

Със sqlite3 за всеки клас пишеш SQL и превръщаш редовете в обекти на ръка. ORM (object-relational mapping) прави това вместо теб: описваш класа, а библиотеката сама прави таблицата, заявките и обектите. В стандартната библиотека на Python ORM няма. Най-известният е SQLAlchemy (пакетът sqlalchemy), а в уеб проекти с Django се ползва вграденият ORM на Django.

models.py

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    price: Mapped[float]

• Наследник на Base (от DeclarativeBase) е таблица, а __tablename__ дава името ѝ.
• Всяко поле с тип Mapped[...] е колона. primary_key=True е първичният ключ: SQLAlchemy оставя базата да му дава стойност.
• mapped_column задава подробности за колоната: тук String(50). Поле само с Mapped[float] също става колона.
• Конструкторът се прави сам: Product(name="Кафе", price=7.50).

Записът и четенето минават през Session:

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Product(name="Кафе", price=7.50))
    session.add(Product(name="Чай", price=3.20))
    session.commit()

    cheap = session.scalars(select(Product).where(Product.price < 5).order_by(Product.price))
    for p in cheap:
        print(f"{p.name}: {p.price} €")

Изход (с инсталиран SQLAlchemy)

Чай: 3.2 €

create_engine("sqlite://") казва коя база: тук SQLite в паметта. create_all прави таблиците. session.add добавя обекта, а commit() го записва в транзакция, както в sqlite3. Заявката се строи с код: select(Product).where(Product.price < 5) говори за класа и полето му price, не за таблицата. session.scalars връща готови обекти.

SQLAlchemy е пакет и в проверката на сайта го няма, затова този код тук не се пуска. Изходът е от пускане с инсталиран SQLAlchemy 2.1 (pip install sqlalchemy).

По-точно, за любопитните

• Заявката с select е код, затова условията лесно се сглобяват според избора на потребителя. Функция, която търси по име:

def named(session, name):
    query = select(Product).where(Product.name == name)
    return session.scalars(query).all()

• Транзакция с отмяна при грешка: session.begin() в with записва всичко накрая, а при изключение отменя всичко:

with Session(engine) as session, session.begin():
    session.add(Product(name="Шоколад", price=2.40))
    # още промени: при изключение всичко се отменя, иначе се записва

• С create_engine(адрес, echo=True) SQLAlchemy печата SQL-а, който праща. Така се вижда, че отдолу пак е SQL.
• В стария код ще срещнеш session.query(Product).filter(...). Това е стилът от SQLAlchemy 1.x, а select(...) е от 2.0 нататък.

Упражнение

Магазин с база

Реши задачите в редактора си, със sqlite3 и база ":memory:".1. Каталог. Направи таблица products и функции add, set_price, remove и show (list е име на вграден тип), всички с ? освен show. Добави Кафе (7,50 €), Чай (3,20 €) и Шоколад (2,40 €), отпечатай, смени цената на кафето на 6,90 €, изтрий шоколада и отпечатай пак:

Изход

1. Кафе: 7.5 €
2. Чай: 3.2 €
3. Шоколад: 2.4 €
---
1. Кафе: 6.9 €
2. Чай: 3.2 €

2. Търсене по част от името. Продуктите са Кафе, Какао, Чай и Шоколад. Функция find(conn, text) намира всички, чието име съдържа текста, без значение от малки и главни букви. LOWER в SQLite сменя само латинските букви, затова дай на базата своя функция: conn.create_function("lower_text", 1, str.lower), после WHERE lower_text(name) LIKE ? със стойност f"%{text.lower()}%". Пробвай „КА“, „чай“ и опит за injection:

Изход

Търся „КА“:
  Какао: 4.8 €
  Кафе: 7.5 €
Търся „чай“:
  Чай: 3.2 €
Търся „' OR '1'='1“:
  няма такива продукти

3. Превод, който се проваля наполовина. Сметките са Ана (100 €) и Борис (50 €). transfer(conn, source, target, amount) първо вади от source, после добавя на target. Ако cursor.rowcount е 0 (няма такава сметка), хвърли ValueError(f"Няма сметка {account_id}"). Всичко е в with conn:. Преведи 30 € от 1 към 2, после 40 € от 1 към несъществуващата 99:

Изход

Преведени 30 € от 1 към 2
Отказан: Няма сметка 99
  Ана: 70 €
  Борис: 80 €

Вторият превод е извадил 40 € от Ана, преди да се провали, но with conn (с rollback()) ги е върнал.

Тест

Провери се

1. Какво връща conn.execute("SELECT ...")?

Списък от обекти ProductКурсор: редовете от резултата, които обхождаш с forБроя на променените редове

2. Защо f"... WHERE name = '{name}'" е опасно?

Защото текстът от потребителя става част от SQL и може да промени заявкатаЗащото е бавноНе е опасно, ако name е str

3. Преводът е две заявки в with conn:. Първата минава, втората хвърля изключение. Какво прави with conn?

Отменя всички промени от началото на транзакцията, и първата (rollback)Повтаря втората заявкаЗаписва първата и отменя втората

4. Какво прави ORM като SQLAlchemy?

Заменя базата данниСамо ускорява SQL заявкитеПревръща обекти в редове и обратно, без да пишеш SQL за всяко нещо

Провери

Започни урока отначало