Дані, у яких можна запитати

У попередній темі ми записали меню кавʼярні у CSV і замовлення у JSON. Дані пережили завершення програми — рівно те, чого ми хотіли. Тепер кавʼярня попрацювала рік, і той самий файл ставить нове питання: як із нього щось дістати?

Наскрізний приклад той самий — меню кавʼярні, яке ми виростимо до семи напоїв, і замовлення до них. Змінюється лише місце, де воно лежить: замість тексту у файлі меню переїде в базу даних. А щоб перехід не виглядав примхою, почнемо з трьох конкретних поломок файлу, кожну з яких видно на числах.

МежаДе ламається файл

Я згенерував CSV того самого вигляду, що й наше меню — id, name, price, category — на два з половиною мільйони рядків і 130,6 МБ. І поставив йому найпростіше питання: знайди рядок із заданою назвою.

# так виглядає пошук у файлі
with open("big.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        if row["name"] == target:
            break
# переглянуто 2 500 000 рядків, 12,14 с

Дванадцять секунд заради одного рядка. Причина не в Python: у файлі немає способу дізнатися, де починається потрібний рядок, тож доводиться прочитати все, що лежить перед ним. Це повний перегляд (full scan). Найнеприємніше — коли рядка у файлі немає: тоді програма теж прочитає всі 130 МБ, щоб відповісти «нічого».

Друга поломка — часткове оновлення. Подорожчав латте, і «замінити 50 на 55» у файлі не можна: новий текст може виявитися довшим і затре сусідній рядок. Лишається прочитати весь файл, змінити один запис і записати все назад:

Змінити одну ціну	Файл CSV, 200 000 рядків	База, 200 000 рядків
час	326 мс	0,14 мс
що сталося з диском	переписано весь файл	змінено одну сторінку

Третя поломка найтихіша. Дві програми пишуть у той самий файл: кожна читає весь вміст, змінює свою частину, записує назад. Я запустив дві такі паралельно, кожна мала додати 300 рядків:

# два записувачі, кожен додає 300 рядків: очікуємо 600
CSV → у файлі 365 рядків, 235 втрачено
SQLite → у базі 600 рядків

Жодної помилки, жодного попередження. Поки перша програма тримала вміст файлу в памʼяті, друга записала свою версію — і перша її затерла. Це гонитва (race condition), і акуратним кодом вона не лікується: файл не має способу сказати «зачекай, я пишу». База має.

Скільки рядків довелося переглянути

Один і той самий пошук за назвою. Ліворуч — файл, праворуч — база з індексом.

рядків у даних 2 500 000

файл: рядків—

база: порівнянь—

Звідки друге число. Індекс — це дерево, і кожен крок униз відкидає половину рядків, що лишилися. Тому кількість порівнянь росте не разом із даними, а як логарифм: подвоївши таблицю, ти додаєш до пошуку рівно один крок. Заміряний час на 2,5 млн рядків: файл — 12,14 с, база з індексом — 0,020 мс.

БазаЩо таке база даних

База даних — це програма, яка зберігає дані так, щоб їй можна було ставити питання, і сама стежить, щоб дані лишалися узгодженими. Перша половина дає швидкий пошук: база тримає не лише рядки, а й службові структури, які підказують, де шукати. Друга дає порядок при одночасному доступі: доки одна програма змінює дані, інша не побачить половину зміни.

Питання ставлять мовою SQL (Structured Query Language, «мова структурованих запитів»). Головна її риса — декларативність: ти пишеш, що хочеш отримати, а не як це знайти:

# Python: ми описуємо ЯК
result = [d for d in menu if d["category"] == "кава"]
result.sort(key=lambda d: d["price"])

-- SQL: ми описуємо ЩО
select * from drinks where category = 'кава' order by price

Різниця не косметична: у другому випадку рішення, читати таблицю підряд чи скористатися індексом, ухвалює планувальник запитів усередині бази — він знає розміри таблиць і наявні індекси, а ти ні. У розділі про індекси ми цей план побачимо на власні очі.

Три домовленості чужої мови — щоб далі не спотикатись. SQL старший за Python і має власні дрібні правила, які ніде не оголошують уголос. Коментар у ньому починається не з #, а з двох дефісів: -- це коментар до кінця рядка. Регістр слів мови не важить: select, SELECT і SeLeCt — те саме слово, і в чужому коді ти найчастіше побачиш саме великі літери (SELECT * FROM drinks). Ми пишемо малими, щоб команда не перекрикувала назви таблиць і стовпців, — а от самі назви й значення в лапках регістр таки розрізняють. І крапка з комою в кінці оператора потрібна там, де операторів кілька підряд — у консолі бази або у файлі зі скриптом; у execute() оператор завжди один, тому крапка з комою там необовʼязкова.

Чому саме SQLite

Більшість баз — окремі серверні програми: їх треба встановити, запустити, налаштувати користувачів і пароль. Для першого знайомства це три вечори до першого рядка коду. SQLite влаштована інакше:

вся база — один файл. Скопіював файл — скопіював базу;
немає сервера. Це бібліотека всередині твоєї програми, а не окремий процес, з яким треба спілкуватись;
вона вже стоїть. Модуль sqlite3 входить у стандартну бібліотеку — на відміну від усього, що ми ставили у темі про pip;
вона всюди. SQLite лежить у кожному телефоні й браузері — найімовірніше, це найпоширеніша база даних на планеті.

Ціна простоти теж є, і ми до неї повернемось у останньому розділі: SQLite чудова, доки з базою працює одна програма на одному компʼютері.

Дрібниця з версіями. Спокуса надрукувати sqlite3.version природна — і хибна: це версія драйвера, вона застаріла й у Python 3.14 зникне. На 3.12 виклик уже дає DeprecationWarning: version is deprecated and will be removed in Python 3.14. Версія самої бібліотеки лежить у sqlite3.sqlite_version — на машині, де писалася ця лекція, вона дорівнює 3.42.0.

ЗʼєднанняПерше зʼєднання

Робота з базою в Python завжди має ту саму форму: відкрити зʼєднання, виконати запити, зафіксувати зміни, закрити зʼєднання.

import sqlite3

con = sqlite3.connect("cafe.db")  # файла ще немає — він створиться
cur = con.cursor()            # курсор — той, хто виконує й тримає результат

cur.execute("create table drinks(name text, price integer)")
cur.execute("insert into drinks values ('Еспресо', 25)")

con.commit()  # без цього рядка запис не переживе close()
con.close()

Зʼєднання (Connection) — це відкрита база. Аргумент connect — шлях до файлу; якщо файлу немає, він створиться порожнім. Особливе значення ":memory:" дає базу, яка живе лише в оперативній памʼяті й зникає разом із програмою: для експериментів і тестів це ідеально.

Курсор (Cursor) — виконавець запиту й водночас закладка в результаті: результат select може містити мільйон рядків, і курсор видає їх порціями, коли ти просиш. Просити можна трьома способами: fetchone() — наступний рядок або None; fetchall() — усі рядки списком; або пройтися по курсору циклом for, як по файлу.

commit() — фіксація змін. Усе, що виконано після попереднього commit, база тримає окремо й на диск остаточно не записує. Це транзакція — набір змін, який застосовується цілком або ніяк. Забути commit — найчастіша помилка новачка, і вона мовчазна:

con.execute("insert into drinks values ('Тест', 1)")
con.close() # commit не викликали

con = sqlite3.connect("cafe.db")
con.execute("select count(*) from drinks").fetchone()
(0,) # рядка немає: незафіксовану транзакцію відкотили

Помилки не буде: програма відпрацює, надрукує «збережено» — і нічого не збереже.

Пастка: `with con:` не закриває зʼєднання

У темі про файли ми звикли, що with open(...) сам закриває файл. Із базою рефлекс підводить — тут with означає інше:

con = sqlite3.connect("cafe.db")
with con:
con.execute("insert into drinks values ('Латте', 50)")
# вихід із with викликав commit — і все

con.execute("select 1")
<sqlite3.Cursor object> # зʼєднання ЖИВЕ

con.close()
con.execute("select 1")
ProgrammingError: Cannot operate on a closed database.

Після виходу з with зʼєднання спокійно виконує наступний запит, і лише явний close() його вимикає. with con: — це межа транзакції, а не файлу: успішний вихід робить commit, виняток усередині — rollback (до цього ми повернемось у розділі про зміни). А закривати зʼєднання доводиться самому.

ТаблицяCREATE TABLE і типи

Таблиця в базі — та сама таблиця з CSV: рядки й стовпці. Різниця в тому, що стовпці тут оголошені заздалегідь, разом із типом і правилами.

create table drinks (
    id            integer primary key,
    name          text    not null,
    price         integer not null,
    category    text    not null,
    description text
)

primary key — первинний ключ, стовпець із унікальним значенням: другий рядок із тим самим id дасть помилку, а не мовчазний дублікат. Випадок integer primary key SQLite розуміє особливо — цей стовпець стає внутрішнім номером рядка, і при вставці база підставить наступне вільне число сама.

not null — заборона порожнього значення: ціна напою мусить бути, опис ні. Цього у CSV не було в принципі — файл дозволяє порожню клітинку де завгодно.

Типів у SQLite всього пʼять

Тип у SQLite	Що це	Що приходить у Python
NULL	значення відсутнє	`None`
INTEGER	ціле число	`int`
REAL	число з рухомою крапкою	`float`
TEXT	текст	`str`
BLOB	сирі байти	`bytes`

Окремого типу для дати тут немає — наслідки цього розбиратиме наступна тема. Немає й типу «логічне значення»: замість True й False у базі лежать 1 і 0.

Динамічна типізація: текст пройде в стовпець `integer`

А тепер найважливіше, і воно суперечить інтуїції всіх, хто бачив інші бази. У SQLite тип належить значенню, а не стовпцю: оголошений тип — лише побажання (офіційно «спорідненість», type affinity). База спробує привести значення до нього, а якщо не вийде — збереже як є й нічого не скаже. Ось що сталося з пʼятьма значеннями в стовпці price integer:

Вставили з Python	Лежить у базі	`typeof()`
25	25	integer
"25"	25	integer
"двадцять пʼять"	"двадцять пʼять"	text
25.5	25.5	real
None	None	null

Рядок "25" база мовчки перетворила на число — цього нам так бракувало у CSV. А от "двадцять пʼять" перетворити не вдалося, і воно лягло в числовий стовпець текстом. Наступний select поверне звідти str, і арифметика над цінами впаде з TypeError десь зовсім в іншому місці програми.

Що станеться зі значенням у стовпці price integer

Обери, що вставляємо. Дивись, що ляже в базу і що потім повернеться в Python.

таблиця STRICT

typeof—

тип у Python—

Про прапорець. Із SQLite 3.37 таблицю можна оголосити словом STRICT у кінці — тоді оголошені типи стають правилом, а не побажанням, і невідповідне значення дає

IntegrityError: cannot store TEXT value in INTEGER
    column drinks.price

— з назвою таблиці й стовпця в кінці. Це рекомендований спосіб для нового коду, але за замовчуванням його немає, і мільйони наявних баз живуть із мовчазним перетворенням.

ЗаписINSERT і executemany

Рядки додають командою insert. Стовпці варто перелічувати явно — тоді запит переживе появу нового стовпця в таблиці:

cur.execute(
    "insert into drinks(name, price, category, description) values (?, ?, ?, ?)",
    ("Еспресо", 25, "кава", "міцна")
)
print(cur.lastrowid)  # id, який база призначила сама
1

Знаки питання — параметри; замість них підставляться значення з кортежу, по порядку. Це не косметика: наступний розділ показує, що буває без них. А lastrowid — спосіб дізнатися номер щойно вставленого рядка, коли база призначає його сама.

Коли рядків багато, є executemany: один запит і послідовність кортежів до нього.

rows = [
    ("Еспресо", 25, "кава", "міцна"),
    ("Капучино", 45, "кава", "молоко, кориця"),
    ("Латте", 50, "кава", "багато молока"),
    # решта чотири рядки — так само
]
cur.executemany(
    "insert into drinks(name, price, category, description) values (?, ?, ?, ?)",
    rows)
con.commit()

executemany розбирає текст запиту один раз замість семи й виконує всю вставку в одній транзакції: на двохстах тисячах рядків це різниця між хвилинами й секундами. Ось меню, з яким працюватиме решта лекції:

id	name	price	category
1	Еспресо	25	кава
2	Капучино	45	кава
3	Латте	50	кава
4	Раф	55	кава
5	Чай	30	чай
6	Матча	60	чай
7	Какао	40	какао

ЗапитSELECT: питання до таблиці

Уся мова питань тримається на одному операторі. Мінімальна форма — select * from drinks, «дай усі стовпці з таблиці». Далі до неї додають уточнення, і порядок їх суворий:

select name, price            -- які стовпці показати
from drinks                  -- з якої таблиці
where category = 'кава'      -- які рядки лишити
order by price desc          -- як упорядкувати
limit 3                      -- скільки віддати

У where живуть порівняння =, <, >, <> («не дорівнює»), сполучники and й or, входження in ('кава', 'чай'), діапазон between 30 and 50 і пошук за зразком like 'Ка%', де % — «будь-які символи». Окремо стоїть порожнє значення: = null писати не можна ніколи, бо null не дорівнює навіть самому собі — тільки is null та is not null.

order by сортує за зростанням, desc перевертає порядок, limit 3 віддає перші три рядки. Разом це готова відповідь на «три найдорожчі позиції», яку в Python довелося б збирати із sorted і зрізу.

Конструктор запиту

Вмикай частини запиту й дивись, які рядки лишаються від нашого меню.

WHERE

ORDER BY

limit 3

рядків у меню7

у відповіді—

Порядок частин не довільний. Спершу база бере рядки, потім відкидає зайві через where, потім сортує через order by і аж наприкінці обрізає через limit. Тому limit без order by — майже завжди помилка: без сортування «перші три» означає «три випадкові».

БезпекаПараметри й SQL-інʼєкція

Запит — це текст, і найприродніше зібрати його f-рядком із того, що ввела людина:

# НІКОЛИ так не роби
login = input("логін: ")
con.execute(f"select login, role from users where login='{login}'")

У темі про рядки вже стояло попередження про це — розберемо, що саме тут стається. Нехай у таблиці users три рядки: olena й bohdan із роллю user та admin із роллю admin. І нехай замість логіна людина вводить:

неіснуючий' OR '1'='1

У f-рядку виходить такий текст:

select login, role from users where login='неіснуючий' OR '1'='1'

Апостроф із введення закрив рядковий літерал, і все, що йшло далі, база прочитала не як дані, а як частину запиту. Умова стала «логін дорівнює „неіснуючий“ або одиниця дорівнює одиниці», а друга половина істинна завжди:

con.execute(f"... where login='{evil}'") → 3 рядки, разом з (admin, admin)
con.execute("... where login=?", (evil,)) → 0 рядків

Запит шукав одного користувача — і віддав усіх, разом із адміністратором. Це і є SQL-інʼєкція (SQL injection): дані, які втрутились у запит і змінили його зміст. Заміни users на таблицю з телефонами клієнтів — і матимеш готовий витік.

Найвідоміший приклад інʼєкції не спрацює — і це важливо знати. Із коміксів усі знають рядок '; drop table users; --, який нібито видаляє таблицю. У Python він дає sqlite3.ProgrammingError: You can only execute one statement at a time.: метод execute виконує рівно один оператор і другий не пропускає. Таблиця зникає лише через executescript, який навмисно виконує кілька операторів. Побачивши цю помилку, легко зробити хибний висновок «інʼєкцій не буває». Вони бувають — просто справжня інʼєкція не стирає дані, а краде їх, і виглядає рівно так, як приклад вище.

Інʼєкція наживо

Введи будь-що в поле логіна й перемкни спосіб підстановки.

що ввела людина

як підставляємо

рядків—

витік—

Спробуй звичайне прізвище. Введи O'Brien при f-рядку — і отримаєш OperationalError: near "Brien": syntax error. Параметри потрібні не лише проти зловмисників: апостроф у прізвищі, лапка в назві кавʼярні чи слово l'espresso ламають зібраний вручну запит нізащо.

Правильний спосіб — параметризований запит: замість значення в тексті стоїть ?, а значення їде окремим кортежем.

con.execute("select login, role from users where login=?", (login,))

Текст запиту й дані база отримує окремо: структуру вона розбирає до того, як побачить значення, і далі вже нізащо не сприйме дані як команду. Тому вгорі й вийшло 0 рядків — користувача з логіном неіснуючий' OR '1'='1 у таблиці справді немає.

Є й іменована форма для випадків, коли параметрів багато: у запиті пишуть :cat, а передають словник {"cat": "кава"}.

Чого параметр не вміє. Через ? підставляють значення — і тільки: select * from ? не працює в принципі. Якщо назва стовпця для сортування приходить ззовні, її не «екранують», а звіряють зі списком дозволених: if column not in ("name", "price"): raise ValueError — саме той випадок, для якого ми вчили винятки.

ПідсумкиАгрегація й GROUP BY

Досі ми діставали рядки, але частіше потрібне число: скільки позицій у меню, яка середня ціна, скільки коштує вся кава разом. Для цього є агрегатні функції — вони беруть багато рядків і повертають один результат.

select count(*), avg(price), min(price), max(price), sum(price)
from drinks
(7, 43.57142857142857, 25, 60, 305)

count(*) рахує рядки, avg — середнє, sum — суму, min і max — межі: уся таблиця стиснулась в один рядок із пʼятьма числами. А group by робить те саме, але не для всієї таблиці, а окремо для кожної купки:

select category, count(*), avg(price)
from drinks
group by category
('кава', 4, 43.75)
('какао', 1, 40.0)
('чай', 2, 45.0)

База розклала сім рядків на три купки за значенням category і порахувала кожну окремо. У чаю позицій удвічі менше, ніж у кави, а середня ціна вища — 45 проти 43,75: такі речі й помічають через group by. Порівняй із тим, як це робилося б циклом і словником:

totals, counts = {}, {}
for drink in menu:
    cat = drink["category"]
    totals[cat] = totals.get(cat, 0) + drink["price"]
    counts[cat] = counts.get(cat, 0) + 1
for cat in totals:
    print(cat, counts[cat], totals[cat] / counts[cat])

Сім рядків проти трьох — і річ не лише в довжині. Цикл вимагає, щоб усі дані спершу опинились у памʼяті; group by рахує там, де дані лежать, і повертає три числа замість двох мільйонів рядків.

Останній шматок — having: він фільтрує купки, тоді як where фільтрує рядки. where працює до групування, having — після, і тільки в having можна згадувати count(*). Умову «щонайменше дві позиції» пишемо як count(*) > 1.

select category, count(*) from drinks
group by category
having count(*) > 1
('кава', 4)
('чай', 2) -- какао випало: у ньому одна позиція

GROUP BY по кроках

Чотири кроки: сім рядків → три купки → одне число на купку → відсів.

агрегат

крок—

Що саме відбувається на третьому кроці. Кожна купка перетворюється рівно на один рядок відповіді. Тому в select із group by не можна ставити стовпець, якого немає ані в групуванні, ані під агрегатною функцією: у купці «кава» чотири різні назви, і база не знає, яку з них показати.

ЗвʼязкиДві таблиці й JOIN

У кавʼярні зʼявились замовлення. Спокуса — дописати їх у ту саму таблицю, додавши стовпці «скільки штук» і «коли». Подивись, що з цього вийде:

name	price	category	qty	created_at
Еспресо	25	кава	2	2026-03-01T09:12
Еспресо	25	кава	1	2026-03-01T09:40
Капучино	45	кава	3	2026-03-01T10:05

Назва, ціна й категорія еспресо повторюються в кожному замовленні. Наслідки три, і всі погані. Місце: тисяча замовлень — тисяча копій слова «кава». Оновлення: подорожчав еспресо — виправляти доведеться тисячу рядків, і якщо процес обірветься посередині, у базі буде дві різні ціни на один напій. Зникнення: напій, якого ще ніхто не замовляв, у такій таблиці існувати не може — рядок без замовлення нікуди записати.

Тому дані розкладають по таблицях: кожен факт живе рівно в одному місці. Меню окремо, замовлення окремо, звʼязок між ними — через номер:

create table orders (
    id          integer primary key,
    drink_id   integer not null references drinks(id),
    qty        integer not null,
    created_at text not null  -- дата рядком ISO, про це — у темі 24
)

references drinks(id) оголошує зовнішній ключ (foreign key): drink_id мусить вказувати на наявний рядок у drinks. Факт «еспресо коштує 25» тепер записаний один раз, а замовлення лише посилається на нього.

Пастка SQLite. Перевірку зовнішніх ключів за замовчуванням вимкнено — заради сумісності зі старими базами: оголошення в create table є, перевірки немає, і замовлення на неіснуючий напій пройде мовчки. Умикається одним рядком після connect: con.execute("pragma foreign_keys = on") — і робити це треба на кожному зʼєднанні, бо налаштування не зберігається у файлі бази.

Питання «що замовляли і на яку суму» стосується обох таблиць. Їх зшивають оператором join:

select drinks.name, orders.qty, drinks.price * orders.qty as total
from drinks
join orders on orders.drink_id = drinks.id
('Еспресо', 2, 50)
('Капучино', 3, 135)

Читається так: «візьми drinks, приший до нього orders за умовою, що drink_id збігається з id». on — це і є умова зшивання, а as total дає обчисленому стовпцю імʼя.

Такий join (повна назва — inner join, «внутрішній») лишає лише рядки, для яких пара знайшлася з обох боків. Напій, якого ніхто не замовив, у відповідь не потрапить — а питання «які напої не замовляють?» цілком осмислене. Для нього є left join: він бере усі рядки лівої таблиці, а там, де пари немає, підставляє null.

INNER проти LEFT

Ті самі дві таблиці, той самий on. Дивись, які рядки випадають.

рядків—

без пари—

Пастка з where після left join. Якщо дописати where orders.qty > 0, рядки з null зникнуть — бо null > 0 не істина, — і left join тихо перетвориться на звичайний. Умови на праву таблицю в такому запиті ставлять у on, а не у where.

ЗміниUPDATE, DELETE, транзакція

Зміна й видалення рядка виглядають однаково просто — і однаково небезпечно:

cur.execute("update drinks set price = ? where name = ?", (55, "Латте"))
print(cur.rowcount) # скільки рядків реально змінилось
1

cur.execute("delete from drinks where category = ?", ("какао",))
print(cur.rowcount)
1

rowcount — єдиний спосіб дізнатися, чи запит узагалі щось зачепив: update із умовою, якій ніхто не відповідає, — не помилка, а порожня робота.

Забутий where. update drinks set price = 55 без умови — цілком коректний SQL: він поставить ціну 55 усім напоям. Те саме з delete from drinks — це очищення таблиці, а не помилка синтаксису. Звичка, яка рятує: спершу написати той самий запит як select з тим самим where і подивитись, скільки рядків він віддає.

Транзакція: усе або нічого

Уяви підвищення цін: спершу дорожчає кава, потім чай. Якщо між цими командами програма впаде, у базі лишиться половина змін. Щоб так не сталось, зміни обгортають у транзакцію.

try:
    con.execute("update drinks set price = price + 5 where category = 'кава'")
    con.execute("update drinks set price = price + 5 where category = 'чай'")
    raise ValueError("щось пішло не так")  # зламаємо навмисно
    con.commit()
except ValueError:
    con.rollback()  # скасовуємо ОБИДВА оновлення

con.execute("select price from drinks where name='Еспресо'").fetchone()
(25,)  # ціна не змінилась — відкат спрацював

rollback() повертає базу в стан на момент останнього commit. Разом ці дві команди й дають гарантію «усе або нічого»: інша програма ніколи не побачить базу з половиною змін. Саме цього не вміє файл.

Ту саму пару можна не писати руками — тепер видно, навіщо потрібен with con: із третього розділу:

with con:
con.execute("update drinks set price = price + 5 where category = 'кава'")
con.execute("update drinks set price = price + 5 where category = 'чай'")
# сюди дійшли — значить, обидва оновлення зафіксовано

ІндексІндекс і план запиту

У першому розділі база знаходила рядок за 0,020 мс, а файл шукав 12 секунд. Час розібратися, звідки береться різниця, — бо сама по собі база швидкою не буває: без додаткових структур вона робить те саме, що й Python із файлом, тобто йде по рядках підряд. Перевірити це можна командою explain query plan: вона не виконує запит, а показує, як база збирається його виконувати:

plan = con.execute("explain query plan select * from items where name=?", (target,))
print(plan.fetchone()[3]) # текст плану — у четвертому полі рядка
SCAN items

SCAN — «перегляну всю таблицю». На 2,5 млн рядків такий запит виконувався 277 мс: швидше за файл, бо не треба розбирати текст, але перегляд той самий. Тепер створимо індекс:

con.execute("create index idx_name on items(name)")

plan = con.execute("explain query plan select * from items where name=?", (target,))
print(plan.fetchone()[3])
SEARCH items USING INDEX idx_name (name=?)

Слово змінилося зі SCAN на SEARCH, а запит став виконуватись за 0,020 мс замість 277.

Як читати план

План — це не текст, а таблиця: кожен рядок відповіді описує один крок виконання, а сам людський опис лежить у четвертому полі рядка, тобто в row[3] (перші три — службові: номер кроку, номер кроку-батька й поле, яке лишилось від старих версій і завжди дорівнює нулю). Тому й fetchone()[3]. Складний запит дасть кілька рядків: по одному на кожну таблицю й на кожне проміжне впорядкування.

Сам опис читають зліва направо, і слів у ньому потрібно знати три:

SCAN таблиця — усі рядки підряд. Це червоний прапорець тоді, коли ти шукав один запис; для select без where це нормально й інакше бути не може;
SEARCH таблиця USING INDEX імʼя (стовпець=?) — пошук за індексом. У дужках написано найцінніше: яку саме умову індекс закрив. Якщо у where було два стовпці, а в дужках один — другий усе одно перевірятиметься перебором того, що знайшлось;
USE TEMP B-TREE FOR ORDER BY — база будувала тимчасове сортування, бо готового впорядкування під цей order by не знайшлось. На великій вибірці саме цей рядок і буває найдорожчим.

Практичний спосіб користуватись цим: створив індекс — прожени explain query plan на своєму запиті ще раз. Якщо SCAN лишився, індекс не підійшов (типова причина — умова написана так, що індекс незастосовний: where lower(name) = ? замість where name = ?), і місце під нього витрачено дарма. План — єдина чесна відповідь на питання «а індекс узагалі використовується?»; вимірювати це часом на маленькій таблиці марно.

Індекс — окрема структура, де значення стовпця лежать упорядковано, а поруч із кожним — вказівка, у якому рядку таблиці воно стоїть. Це покажчик у кінці книжки: щоб знайти слово, ти не гортаєш усі 500 сторінок. Пошук у впорядкованому переліку йде діленням навпіл — подивився в середину, зрозумів, у якій половині шукати, викинув другу. На 2,5 млн рядків це 22 кроки замість 2 500 000.

Чим індекс платить

Індекс не безкоштовний — саме тому база не створює його сама на кожен стовпець:

Що	Без індексу	З індексом
розмір файлу (2,5 млн рядків)	128,6 МБ	228,4 МБ (+78%)
вставка 200 000 рядків	0,28 с	0,47 с (×1,7)
пошук одного рядка	277 мс	0,020 мс

Логіка проста: індекс — це копія стовпця, і кожна вставка тепер оновлює не одну структуру, а дві. Тому індекси створюють під конкретні запити, які виконуються часто, а не «про всяк випадок». Первинний ключ індексується автоматично — пошук за id швидкий одразу.

SCAN проти SEARCH

Той самий запит на таблиці різного розміру. Порівнюємо кроки, не час.

рядків у таблиці 2 500 000

SCAN, кроків—

SEARCH, кроків—

Чому не час. На маленьких таблицях різниця тоне в шумі: обидва запити виконуються швидше, ніж таймер здатен виміряти. Кількість кроків детермінована й не залежить від машини — тому дивитись треба на неї, а перевіряти себе командою explain query plan, яка чесно каже SCAN чи SEARCH.

Рядкиrow_factory: рядки як словники

За замовчуванням select повертає кортежі, і значення дістають за номером:

row = con.execute("select name, price, category from drinks where id=1").fetchone()
print(row)
('Еспресо', 25, 'кава')
print(row[1])
25

Працює — і ламається від найменшої зміни. Варто дописати стовпець у середину select, як row[1] почне повертати категорію замість ціни. Помилки не буде: тип підійде, програма поїде далі з неправильним числом. Це та сама біда, через яку в темі про CSV ми обирали DictReader, а не позиційний розбір. Лікується одним рядком:

con.row_factory = sqlite3.Row
row = con.execute("select name, price, category from drinks where id=1").fetchone()

print(row["price"]) # звертання за іменем стовпця
25
print(row.keys())
['name', 'price', 'category']
print(dict(row))
{'name': 'Еспресо', 'price': 25, 'category': 'кава'}

sqlite3.Row — це рядок, який поводиться і як кортеж (row[0] працює), і як словник (row["price"] теж). Імена стовпців він бере із самого запиту, тому обчислений стовпець варто називати через as: без as total ключем стане текст "drinks.price * orders.qty". Одна відмінність від словника: sqlite3.Row незмінний, присвоїти row["price"] = 30 не вийде.

row_factory — просто функція, яку база викликає для кожного рядка, тож своя теж дозволена: імена стовпців вона бере з cursor.description, а далі збирає з них хоч звичайний словник, хоть щось своє.

ПідсумокДе закінчується SQLite

SQLite покриває вражаюче багато: сайт із тисячею відвідувачів на день, настільну програму, мобільний застосунок, аналітику по кількох гігабайтах. Але дві межі в неї жорсткі.

Пише завжди хтось один. Читати можуть скільки завгодно програм одночасно, а на час запису база блокується цілком. Наш замір із двома записувачами пройшов ідеально — 600 рядків із 600, — але там було по 300 крихітних вставок. Коли записувачів десятки й кожен тримає блокування довго, хтось урешті отримає OperationalError: database is locked.

Немає доступу по мережі. SQLite — бібліотека, яка читає файл. Якщо файл лежить на іншому компʼютері, «підключитись до бази» неможливо: лишається покласти його на мережевий диск, а це надійний спосіб базу зіпсувати.

Тоді беруть PostgreSQL або MySQL — окремі програми-сервери з власними користувачами, здатні обслуговувати сотні записувачів. Учити все з нуля не доведеться: select, join, group by, індекси й транзакції там ті самі. Різниця — у налаштуванні й у строгості типів: у Postgres текст у числовий стовпець не пройде ніколи.

Що з цього забрати

Файл віддає все, база відповідає на питання. Знайти рядок у 130-мегабайтному CSV — прочитати 130 МБ; у базі з індексом — 22 кроки;
commit() — не формальність. Без нього зміни зникають мовчки. with con: фіксує транзакцію, але не закриває зʼєднання;
Типи в SQLite — побажання. Текст пройде в стовпець integer і повернеться звідти текстом; хочеш суворості — пиши STRICT;
Значення підставляють тільки через ?. f-рядок у запиті — це готовий витік даних і поламаний запит на першому ж прізвищі з апострофом;
Дані розкладають по таблицях і зшивають через join. Одна широка таблиця — це дублювання, розʼїзд при оновленні й неможливість зберегти напій, якого ще не замовляли;
Індекс пришвидшує пошук і сповільнює запис. Створюй його під конкретний запит, а користь перевіряй через explain query plan.

І ширша думка, заради якої була тема. Перехід від файлу до бази — не заміна інструмента на потужніший, а зміна питання: замість «як мені прочитати ці дані?» ти починаєш питати «що я хочу дізнатися?» — і перекладаєш роботу на того, хто знає про дані більше за тебе. Саме тому SQL живий уже понад пʼятдесят років.

Що далі. Стовпець created_at у таблиці orders оголошений як text — бо типу для дати в SQLite немає взагалі. Дату кладуть туди рядком у форматі ISO 8601 (2026-03-01T09:12:00), і працює це завдяки одній властивості формату: такі рядки сортуються за абеткою в тому самому порядку, що й у часі. Звідки береться ця властивість, чому 01.03.2026 її не має і що робити з часовими поясами — у наступній темі.

МежаДе ламається файл

БазаЩо таке база даних

Чому саме SQLite

ЗʼєднанняПерше зʼєднання

Пастка: with con: не закриває зʼєднання

ТаблицяCREATE TABLE і типи

Типів у SQLite всього пʼять

Динамічна типізація: текст пройде в стовпець integer

ЗаписINSERT і executemany

ЗапитSELECT: питання до таблиці

БезпекаПараметри й SQL-інʼєкція

ПідсумкиАгрегація й GROUP BY

ЗвʼязкиДві таблиці й JOIN

ЗміниUPDATE, DELETE, транзакція

Транзакція: усе або нічого

ІндексІндекс і план запиту

Як читати план

Чим індекс платить

Рядкиrow_factory: рядки як словники

ПідсумокДе закінчується SQLite

Що з цього забрати

Пастка: `with con:` не закриває зʼєднання

Динамічна типізація: текст пройде в стовпець `integer`