SQLite: история вместо перезаписанного файла, `?` вместо склейки

Зачем это нужно

Одиннадцатый урок дал сводке диск, двенадцатый — таблицу в CSV. Оба раза файл писался заново: вчерашние числа исчезали, а «историю» приходилось бы дописывать руками, следя, чтобы одна и та же строка не попала дважды.

База данных придумана ровно для этого. В неё добавляют строку; старые остаются. У строки есть ключ, и по ключу видно, что запись уже была. А выборка — «доллар за январь по дням» — это запрос, а не цикл с условием.

Ставить ничего не нужно: sqlite3 приходит вместе с Python, а вся база — один файл рядом с программой.

Сразу целиком

Файл baza.py. Запуск: python baza.py из окружения.

Обязательное — первые три блока: таблица, повторный запуск и запрос. Четвёртый и пятый показывают два подвоха, шестой связывает строку базы с dataclass.

"""Урок 21: SQLite — куда складывать, чтобы история не переписывалась.

Сводка до сих пор клала данные в файл и каждый раз писала его заново: вчерашние
числа исчезали. База так не умеет — в неё добавляют строку, а старые остаются.
И ставится она ноль раз: sqlite3 приходит вместе с Python.
"""

import sqlite3
from dataclasses import dataclass
from pathlib import Path

HERE = Path(__file__).parent
FILE = HERE / "svodka.db"
FILE.unlink(missing_ok=True)          # учебный файл начинаем с чистого листа


@dataclass
class Rate:
    """Одна строка истории: день, валюта, курс за единицу."""

    day: str
    code: str
    value: float


print("== таблица и первая запись")
conn = sqlite3.connect(FILE)
conn.execute("""
    CREATE TABLE IF NOT EXISTS rates (
        day   TEXT NOT NULL,
        code  TEXT NOT NULL,
        value REAL NOT NULL,
        PRIMARY KEY (day, code)
    )
""")
first = [
    Rate("2026-01-15", "USD", 510.43),
    Rate("2026-01-15", "EUR", 594.86),
    Rate("2026-01-16", "USD", 511.02),
]
conn.executemany("INSERT INTO rates (day, code, value) VALUES (?, ?, ?)",
                 [(r.day, r.code, r.value) for r in first])
conn.commit()
print("строк в базе:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== второй запуск не портит историю")
again = [
    Rate("2026-01-15", "USD", 510.43),    # тот же день — уже есть
    Rate("2026-01-16", "EUR", 596.10),    # новый день — добавится
]
conn.executemany("""
    INSERT INTO rates (day, code, value) VALUES (?, ?, ?)
    ON CONFLICT (day, code) DO UPDATE SET value = excluded.value
""", [(r.day, r.code, r.value) for r in again])
conn.commit()
print("строк после повтора:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== запрос вместо цикла")
conn.row_factory = sqlite3.Row
for row in conn.execute("SELECT day, value FROM rates WHERE code = ? ORDER BY day", ("USD",)):
    print(f"{row['day']}: {row['value']:.2f}")
print("средний курс доллара:",
      round(conn.execute("SELECT avg(value) FROM rates WHERE code = ?", ("USD",)).fetchone()[0], 2))

print()
print("== почему значение подставляют вопросительным знаком")
sneaky = "USD' OR '1'='1"
glued = conn.execute(f"SELECT count(*) FROM rates WHERE code = '{sneaky}'").fetchone()[0]
safe = conn.execute("SELECT count(*) FROM rates WHERE code = ?", (sneaky,)).fetchone()[0]
print("склейкой строк нашлось:", glued, "— вся таблица")
print("через ? нашлось:       ", safe, "— такой валюты нет")

print()
print("== без commit ничего не сохранилось")
conn.execute("INSERT INTO rates (day, code, value) VALUES (?, ?, ?)", ("2026-01-17", "USD", 512.00))
print("в этом соединении:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()                           # закрыли, не сказав commit

conn = sqlite3.connect(FILE)
conn.row_factory = sqlite3.Row
print("после переоткрытия:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== строка базы становится записью")
row = conn.execute("SELECT day, code, value FROM rates ORDER BY day, code LIMIT 1").fetchone()
rate = Rate(row["day"], row["code"], row["value"])
print(rate)
print("это", type(rate).__name__, "— с ним работают как с обычным объектом:", rate.code, rate.value)

conn.close()
FILE.unlink()

Выводит:

== таблица и первая запись
строк в базе: 3

== второй запуск не портит историю
строк после повтора: 4

== запрос вместо цикла
2026-01-15: 510.43
2026-01-16: 511.02
средний курс доллара: 510.73

== почему значение подставляют вопросительным знаком
склейкой строк нашлось: 4 — вся таблица
через ? нашлось:        0 — такой валюты нет

== без commit ничего не сохранилось
в этом соединении: 5
после переоткрытия: 4

== строка базы становится записью
Rate(day='2026-01-15', code='EUR', value=594.86)
это Rate — с ним работают как с обычным объектом: EUR 594.86

Разбор

Таблица — это договор о том, что вы храните

CREATE TABLE IF NOT EXISTS rates (
    day   TEXT NOT NULL,
    code  TEXT NOT NULL,
    value REAL NOT NULL,
    PRIMARY KEY (day, code)
)

Три столбца и одно решение: ключ — это пара «день и валюта». Он говорит, что в один день у одной валюты бывает ровно один курс, и база следит за этим сама.

IF NOT EXISTS делает запуск повторяемым: программу можно запускать хоть каждый день, таблица создастся один раз.

Дата лежит текстом в формате ISO — 2026-01-15. У SQLite нет отдельного типа для дат, и это тот самый случай из четырнадцатого урока: ISO сортируется как строка ровно так же, как даты, поэтому ORDER BY day работает правильно.

Образ. Амбарная книга. В неё дописывают строки, а не переписывают страницу заново; и в ней нельзя дважды записать один и тот же день по одному товару.

Второй запуск не портит историю

строк в базе: 3
строк после повтора: 4

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

INSERT INTO rates (day, code, value) VALUES (?, ?, ?)
ON CONFLICT (day, code) DO UPDATE SET value = excluded.value

Это называют upsert: вставить, а если такой ключ уже есть — обновить. excluded здесь — та строка, которую вы принесли; так база различает старое значение и новое.

Без ключа и без ON CONFLICT повторный запуск удвоил бы историю, и никакой ошибки при этом не случилось бы — просто среднее за январь стало бы считаться по удвоенным данным. Это второй раз за курс, когда молчаливая ошибка дороже громкой.

Запрос вместо цикла

conn.execute("SELECT day, value FROM rates WHERE code = ? ORDER BY day", ("USD",))

Раньше отбор «только доллар, по возрастанию дня» был циклом с if и сортировкой. Теперь это одна строка на языке запросов, и делает её база — она для этого и написана.

conn.row_factory = sqlite3.Row меняет кортежи на строки с именами: row["day"] вместо row[0]. Это та же разница, что между csv.reader и DictReader в двенадцатом уроке, и по той же причине: номер столбца молча поедет, если запрос изменится.

Считать тоже умеет база: avg, count, min, max, sum. Подробнее — в следующем уроке, он весь про запросы.

? — не про удобство

склейкой строк нашлось: 4 — вся таблица
через ? нашлось:        0 — такой валюты нет

Вот измерение, ради которого стоит запомнить одно правило на всю жизнь. Значение USD' OR '1'='1, вклеенное в текст запроса, закрыло кавычку и дописало условие, которое верно всегда, — и запрос вернул всю таблицу вместо одной валюты.

Это называется SQL-инъекцией, и с ней не борются экранированием: значение просто не вклеивают. ? — это место, куда база кладёт значение уже после того, как разобрала запрос; тексту запроса оно навредить не может, потому что запрос к тому моменту уже готов.

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

commit — это и есть «сохранить»

в этом соединении: 5
после переоткрытия: 4

Строка была видна там, где её вставили, и исчезла после переоткрытия файла. Потому что INSERT открыл транзакцию, а close() без commit() её откатил.

Транзакция — это «всё или ничего»: несколько изменений либо попадают в файл вместе, либо не попадают вовсе. Для сводки это ровно та защита, которой не было у файла в одиннадцатом уроке: программу можно прервать на середине, и база останется целой.

Полезно знать и то, что with sqlite3.connect(...) as conn делает commit, но не закрывает соединение — в отличие от with для файлов. Файл закрывают явно.

REAL — а как же Decimal из четвёртого урока

Четвёртый урок сказал прямо: деньги в float не считают, потому что 0.1 + 0.2 не равно 0.3. А здесь курс лежит в столбце REAL, то есть в том же float. Противоречия нет, но границу надо назвать, иначе правило выглядит отменённым.

Правило про Decimal — про суммы, которые кто-то кому-то должен: цену в чеке, остаток на счёте, начисленный налог. Там ошибка в одну сотую — это ошибка в деньгах, и она накапливается при сложении тысяч строк.

Курс валют, инфляция, температура — это измерения. У них своя точность уже на входе (банк даёт два знака), их складывают редко, а средние по ним всё равно приблизительны. Для такого ряда float честен.

Если же в базу ложатся именно деньги, у SQLite нет типа Decimal, и решение принимают заранее — одно из двух:

• целое число тиынов (INTEGER): 1 234,56 ₸ хранится как 123456, и вся арифметика идёт в целых;
• текст (TEXT): Decimal кладут строкой и достают через Decimal(row["value"]).

Оба способа — договор, и его записывают рядом с таблицей. Молчаливое «положим во float, потом разберёмся» — тот самый случай, когда через полгода отчёт не сходится на копейку и никто не помнит почему.

dataclass — запись, у которой есть имена

@dataclass
class Rate:
    day: str
    code: str
    value: float

@dataclass избавляет от рутины: __init__, сравнение и печать он пишет сам. Из базы приходит строка, из строки собирают Rate, и дальше по программе идёт объект с полями, а не кортеж, в котором надо помнить, что на втором месте.

Печатается он тоже сам собой — Rate(day='2026-01-15', code='EUR', value=594.86), — и это не украшение: в отладке видно, что именно лежит в переменной.

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

Но три раза class вам уже встретился, и каждый раз — в одной и той же скромной роли имени. В десятом уроке class BadRow(ValueError) дал имя своей ошибке. В двадцатом классом описан учебный сервер — его вы читали, а не писали. Здесь @dataclass даёт имя записи: Rate — это «день, валюта, курс», а Rate("2026-01-15", "USD", 510.43) — один такой набор, экземпляр. Полей у него столько, сколько вы объявили; методов вы не писали и сегодня не пишете.

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

Карта урока

Скажите своими словами

Не подглядывая, ответьте вслух или на бумаге. Ответы — в конце урока.

1. Что делает ON CONFLICT (day, code) DO UPDATE и что было бы без него?
2. Почему значения подставляют через ?, а не вклеивают в текст запроса?
3. Что происходит с записью, если соединение закрыли без commit?

Разминка

Три коротких шага перед заданием: предсказать, дописать, починить. Ответы — в конце урока, но сначала ответьте сами.

1. Предскажите. Что напечатает эта программа?

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT PRIMARY KEY, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
conn.execute("""
    INSERT INTO t VALUES (?, ?)
    ON CONFLICT (code) DO UPDATE SET value = excluded.value
""", ("USD", 511.0))
print(conn.execute("SELECT count(*), max(value) FROM t").fetchone())

2. Заполните пропуск. Вместо ... поставьте то, чем в запрос подставляют значение.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
code = "USD"
print(conn.execute("SELECT value FROM t WHERE code = ...", (code,)).fetchall())

3. Почините. Программа записывает курс, но при следующем открытии файла его там нет. Найдите пропущенную строку.

import sqlite3
from pathlib import Path

FILE = Path(__file__).parent / "kurs.db"
FILE.unlink(missing_ok=True)

conn = sqlite3.connect(FILE)
conn.execute("CREATE TABLE rates (code TEXT, value REAL)")
conn.execute("INSERT INTO rates VALUES (?, ?)", ("USD", 510.43))
conn.close()

conn = sqlite3.connect(FILE)
print("строк в файле:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()
FILE.unlink()

Задание

Обязательное. Соберите историю курсов, которую не портит повторный запуск. Дано:

rows = [
    Rate("2026-01-15", "USD", 510.43),
    Rate("2026-01-15", "EUR", 594.86),
    Rate("2026-01-16", "USD", 511.02),
    Rate("2026-01-16", "EUR", 596.10),
]

Заведите dataclass Rate (день, валюта, курс) и таблицу rates с ключом из дня и валюты. Напишите функцию save(records): новые записи добавляются, знакомые обновляются. Вызовите её дважды подряд и напечатайте число строк после каждого вызова. Затем выберите доллар по дням, посчитайте его средний курс запросом и достаньте последнюю по дате запись, превратив её обратно в Rate.

Ожидаемый вывод:

после первого запуска: 4
после второго запуска: 4
доллар по дням:
  2026-01-15: 510.43
  2026-01-16: 511.02
средний курс доллара: 510.73
последняя запись: Rate(day='2026-01-16', code='EUR', value=596.1)

Готово, когда: вывод совпадает построчно; второй запуск не добавил ни одной строки; ни одно значение не вклеено в текст запроса; среднее посчитала база, а не Python; после работы файл базы убран за собой.

На своих данных. Возьмите свой ряд из любого прошлого урока — расходы, цены, замеры — и сложите его в базу с ключом из даты. Запустите программу трижды и убедитесь, что строк столько же, сколько дней.

По желанию.

• Добавьте столбец «когда получено» и заполните его при вставке.
• Попробуйте вставить строку с тем же ключом обычным INSERT и прочитайте ошибку.
• Откройте файл базы в любом просмотрщике SQLite и найдите свою таблицу глазами.

Куда это встанет в проекте

У сводки появляется память. Курс за каждый день ложится в базу, повторный запуск ничего не удваивает, а вопрос «как менялся доллар за месяц» перестаёт требовать чтения всех файлов подряд.

Долги. Мы написали SELECT в простейшем виде: следующий урок весь про запросы — группировку, соединение двух таблиц и то, как достать срез, не написав ни одного цикла. И база у нас пока одна на всё; когда таблиц станет несколько, понадобится решить, что с чем связано.

Ответы

(1, 511.0)

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
code = "USD"
print(conn.execute("SELECT value FROM t WHERE code = ?", (code,)).fetchall())

[(510.43,)]

import sqlite3
from pathlib import Path

FILE = Path(__file__).parent / "kurs.db"
FILE.unlink(missing_ok=True)

conn = sqlite3.connect(FILE)
conn.execute("CREATE TABLE rates (code TEXT, value REAL)")
conn.execute("INSERT INTO rates VALUES (?, ?)", ("USD", 510.43))
conn.commit()
conn.close()

conn = sqlite3.connect(FILE)
print("строк в файле:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()
FILE.unlink()

строк в файле: 1

Источники

• Python: модуль sqlite3
• Python: dataclasses
• SQLite: UPSERT
• SQLite: типы данных