База данных

таблицы, связи и SQL, которого хватает для чтения

Что это

База — место, где данные переживают перезапуск. Писать SQL самому не придётся, а вот читать — обязательно: запрос решает, какие данные получит приложение, и ошибка здесь не видна ни при запуске, ни в трейсбеке.

Для чтения достаточно понимать таблицы, связи и четыре вида запросов.

Как выглядит в коде

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL
);

CREATE TABLE subs (
    id       INTEGER PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(id),
    city     TEXT NOT NULL,
    UNIQUE (user_id, city)
);

Построчно:

• PRIMARY KEY — главный ключ, уникальный номер записи.
• REFERENCES users(id) — связь: в user_id может лежать только существующий id из users. База не даст записать подписку несуществующему пользователю.
• NOT NULL — поле обязательно.
• UNIQUE (user_id, city) — пара уникальна. Это и есть правило «нельзя подписаться дважды», но выраженное так, что его невозможно обойти из кода.

Четыре вида запросов

SELECT city FROM subs WHERE user_id = 7;          -- прочитать
INSERT INTO subs (user_id, city) VALUES (7, 'Oslo');  -- добавить
UPDATE subs SET city = 'Bergen' WHERE id = 3;     -- изменить
DELETE FROM subs WHERE id = 3;                     -- удалить

Главное при чтении — WHERE. Он решает, сколько строк затронет запрос. UPDATE без WHERE меняет всю таблицу; DELETE без WHERE очищает её. Это самая дорогая опечатка в бэкенде.

JOIN — данные из двух таблиц

SELECT u.name, s.city
FROM users u
LEFT JOIN subs s ON s.user_id = u.id;

Читается так: взять пользователей, к каждому подставить его подписки, связывая по user_id = id.

Разница важна: INNER JOIN молча потеряет пользователей без подписок. Если в отчёте не хватает строк — первым делом смотри на вид JOIN.

Параметры, а не склейка строк

# так нельзя — дыра в безопасности
conn.execute(f"SELECT city FROM subs WHERE user_id = {user_id}")

# так правильно
conn.execute("SELECT city FROM subs WHERE user_id = ?", (user_id,))

Вопросительный знак — место для значения. База подставит его сама, и что бы ни пришло от пользователя, оно останется значением, а не превратится в часть запроса.

Это защита от SQL-инъекции: без неё строка 7; DROP TABLE subs в адресе удалит таблицу. ИИ обычно пишет параметры правильно, но при сборке запроса из кусков («если фильтр задан, добавить условие») легко скатывается к f-строкам.

Транзакции

with conn:
    conn.execute("INSERT INTO subs ...")
    conn.execute("UPDATE users SET sub_count = sub_count + 1 ...")

Либо выполнятся обе строки, либо ни одной. Нужно везде, где несколько изменений должны произойти вместе: иначе при ошибке на втором запросе данные останутся наполовину изменёнными.

Как сказать ИИ

Объясни этот запрос по шагам: из каких таблиц берутся данные,
по какому условию связываются, сколько строк вернётся.

Схема такая: <CREATE TABLE>. Напиши запрос: все города,
на которые подписано больше трёх пользователей, с числом
подписчиков. Параметры через плейсхолдеры.

В этом UPDATE нет WHERE. Так задумано или это ошибка?

Последний вопрос стоит задавать всегда, когда видишь UPDATE или DELETE в диффе.

На что смотреть

UPDATE или DELETE без WHERE. Проверяй в каждом диффе отдельно.

f-строка в запросе. Инъекция. Исключений нет.

INNER JOIN там, где нужен LEFT. Молча теряет строки.

Нет NOT NULL и UNIQUE. Правила перенесены в код и будут обойдены.

SELECT *. Вернёт все поля, включая те, что добавят завтра, — и они уедут клиенту (статья 7.3).

Несколько изменений без транзакции. При ошибке посередине данные останутся несогласованными.

Шпаргалка