✧ Связываем таблицы и защищаем изменения ✧

🚀 Запускаем

Оружие героя можно записать в ту же таблицу, но тогда у десяти героев с одним мечом название придётся повторять десять раз, а при исправлении опечатки менять все копии. Правильнее хранить героев и оружие в двух таблицах и связать их номером. Запусти:

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
    CREATE TABLE weapons (id INTEGER PRIMARY KEY, title TEXT);
    CREATE TABLE heroes  (name TEXT, weapon_id INTEGER REFERENCES weapons(id));

    INSERT INTO weapons VALUES (1, 'Меч'), (2, 'Лук');
    INSERT INTO heroes  VALUES ('Эмбер', 2), ('Лиза', NULL), ('Кли', 1);
""")

query = """
    SELECT heroes.name, weapons.title
    FROM heroes
    LEFT JOIN weapons ON weapons.id = heroes.weapon_id
    ORDER BY heroes.name
"""
for name, title in con.execute(query):
    print(name, "—", title or "без оружия")
Кли — Меч
Лиза — без оружия
Эмбер — Лук

JOIN «склеивает» строки двух таблиц по условию ON: у героя берётся номер оружия, а по нему находится название. У Лизы оружия нет (NULL), но благодаря LEFT JOIN она всё равно попала в результат, а значение из второй таблицы пустое (None в Python). Замени LEFT JOIN на JOIN и посмотри, что случится с Лизой.

📜 Теория: связи, транзакции, миграции

Связи между таблицами

Столбец, в котором лежит номер строки из другой таблицы, называют внешним ключом (weapon_id INTEGER REFERENCES weapons(id)). Один раз записанное оружие можно использовать в любом числе строк, а исправить название достаточно в одном месте. Чаще всего встречается связь «один ко многим»: у одного оружия много героев, а у героя одно оружие.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")      # по умолчанию SQLite связи не проверяет
con.execute("CREATE TABLE weapons (id INTEGER PRIMARY KEY)")
con.execute("CREATE TABLE heroes (name TEXT, weapon_id INTEGER REFERENCES weapons(id))")

try:
    con.execute("INSERT INTO heroes VALUES ('Эмбер', 7)")     # оружия с номером 7 нет
except sqlite3.IntegrityError as error:
    print(error)                                               # → FOREIGN KEY constraint failed

С этой строкой (её выполняют сразу после connect) база будет отказываться вставить героя с несуществующим номером оружия, и данные не рассыпятся.

JOIN: INNER и LEFT

  • JOIN (он же INNER JOIN) оставляет только строки, у которых нашлась пара в другой таблице;
  • LEFT JOIN оставляет все строки левой таблицы, а недостающее заполняет NULL;
  • если у столбцов в таблицах одинаковые имена, их пишут с названием таблицы: heroes.name, weapons.title.
query = """
    SELECT weapons.title, COUNT(heroes.name)
    FROM weapons
    LEFT JOIN heroes ON heroes.weapon_id = weapons.id
    GROUP BY weapons.title
"""

Связь можно соединять с группировкой: этот запрос посчитает для каждого оружия, сколько героев им пользуется (у неиспользуемого будет 0).

Транзакции: всё или ничего

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE wallet (owner TEXT PRIMARY KEY, mora INTEGER)")
con.executemany("INSERT INTO wallet VALUES (?, ?)", [("Эмбер", 500), ("Лиза", 100)])
con.commit()


def transfer(source, target, amount):
    with con:                                           # успех → commit, исключение → откат
        con.execute("UPDATE wallet SET mora = mora - ? WHERE owner = ?", (amount, source))
        left = con.execute("SELECT mora FROM wallet WHERE owner = ?", (source,)).fetchone()[0]
        if left < 0:
            raise ValueError("Не хватает моры")
        con.execute("UPDATE wallet SET mora = mora + ? WHERE owner = ?", (amount, target))


transfer("Эмбер", "Лиза", 200)
try:
    transfer("Эмбер", "Лиза", 1000)
except ValueError as error:
    print(error)

print(con.execute("SELECT * FROM wallet ORDER BY owner").fetchall())
# → [('Лиза', 300), ('Эмбер', 300)]

Перевод — это два изменения, и они должны произойти вместе: если после списания у Эмбер вдруг пошло что-то не так, деньги не должны «повиснуть» между кошельками. Транзакция объединяет несколько запросов в один неделимый шаг. В sqlite3 её открывает конструкция with con:: при нормальном выходе изменения сохраняются, при исключении всё откатывается, как будто ничего не было. Во втором переводе у Эмбер списалось бы 1000, но исключение отменило списание, и в кошельках остались 300 и 300.

Файл базы между запусками

import sqlite3

con = sqlite3.connect("game.db")
con.execute("CREATE TABLE IF NOT EXISTS log (moment TEXT, note TEXT)")
con.execute("INSERT INTO log VALUES (datetime('now'), ?)", ("запуск",))
con.commit()
print(con.execute("SELECT COUNT(*) FROM log").fetchone()[0])      # растёт с каждым запуском

В отличие от ":memory:", файловая база переживает запуск программы: данные лежат на диске. Поэтому структуру создают через CREATE TABLE IF NOT EXISTS (первый запуск создаёт таблицу, следующие используют готовую). Когда структура меняется (нужен новый столбец), добавляют его командой ALTER TABLE heroes ADD COLUMN rarity INTEGER — такие правки называют миграциями, и без них старые файлы пользователей перестанут подходить к новой версии программы.

JSON-файл или база?

  • JSON подходит для небольших данных, которые читают и пишут целиком: настройки, сохранение одного игрока.
  • SQLite нужна, когда данных много, их нужно искать и считать по условиям, а менять часто и по частям. Плюс транзакции и защита от повреждения при сбое.
  • Для больших серверов с тысячами одновременных пользователей берут другие базы (PostgreSQL, MySQL), но язык SQL у них почти тот же.

🧪 Попробуй сам

★★★

🎯 Задача: Герои и их оружие

10 очков

Герои и оружие хранятся в двух таблицах: weapons (id, title) и heroes (name, weapon_id, ссылка на weapons.id). У героя может не быть оружия (weapon_id пуст).

Ввод: число оружий N, затем N строк номер название (название может состоять из нескольких слов), затем число героев M, затем M строк имя номер_оружия; если оружия нет, вместо номера стоит -.

Создай обе таблицы в базе в памяти, заполни их и одним запросом с JOIN выведи героев по алфавиту: Имя: Название или Имя: без оружия. Последней строкой выведи Без оружия: <сколько>.

Пример. Ввод:

2
1 Меч Тёмной ночи
2 Лук Бури
3
Эмбер 2
Лиза -
Кли 1

Вывод:

Кли: Меч Тёмной ночи
Лиза: без оружия
Эмбер: Лук Бури
Без оружия: 1

Заготовка (кнопка у блока отправит её в песочницу):

import sqlite3

# 1. Создай таблицы weapons и heroes (weapon_id ссылается на weapons.id)

# 2. Прочитай оружие и героев и добавь их (значения через ?; «-» значит NULL)

# 3. Одним запросом с LEFT JOIN получи героев с названием оружия (по алфавиту) и выведи
★★★

🎯 Задача: Перевод моры в одной транзакции

25 очков

В базе есть таблица кошельков wallet (owner, mora); база запрещает отрицательный остаток (CHECK (mora >= 0)). В файле bank.py напиши функцию transfer(con, source, target, amount), которая переводит amount моры от source к target и возвращает строку:

  • ok — перевод выполнен;
  • не хватает моры — у отправителя недостаточно (в этом случае никакие изменения не должны остаться);
  • нет такого кошелька — отправителя или получателя нет в таблице (и тоже ни одного изменения).

Перевод делай одной транзакцией (with con:): если что-то пошло не так, изменения откатываются. Подсказка про порядок: попробуй сначала зачислить получателю, потом списать у отправителя, и убедись, что откат срабатывает.

Файл main.py менять не нужно: он создаёт таблицу, читает переводы из ввода, вызывает твою функцию и в конце печатает остатки.

Пример. Ввод:

2
Эмбер 500
Лиза 100
2
Эмбер Лиза 200
Эмбер Лиза 1000

Вывод:

ok
не хватает моры
Лиза: 300
Эмбер: 300

Заготовка (кнопка у блока отправит её в песочницу):

import sqlite3

from bank import transfer

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE wallet (owner TEXT PRIMARY KEY, mora INTEGER NOT NULL CHECK (mora >= 0))")
for _ in range(int(input())):
    owner, mora = input().split()
    con.execute("INSERT INTO wallet VALUES (?, ?)", (owner, int(mora)))
con.commit()

for _ in range(int(input())):
    source, target, amount = input().split()
    print(transfer(con, source, target, int(amount)))

for owner, mora in con.execute("SELECT owner, mora FROM wallet ORDER BY owner"):
    print(f"{owner}: {mora}")

✅ Что мы узнали

Данные раскладывают по таблицам и связывают номерами (внешний ключ, PRAGMA foreign_keys = ON); JOIN соединяет таблицы в запросе, LEFT JOIN не теряет строки без пары. Транзакция with con: делает несколько изменений неделимыми: при ошибке откатывается всё. Файловая база живёт между запусками, поэтому таблицы создают через IF NOT EXISTS, а структуру меняют миграциями.

Теперь соберём всё вместе: небольшую утилиту, которая запускается из терминала и помнит данные между запусками.

⟵ Складываем данные в базу Собираем утилиту целиком ⟶