Проект: база книжной библиотеки на SQLite с отчётами SQL
Финальный проект курса: база библиотеки из трёх связанных таблиц — авторы, книги, выдачи — и четыре настоящих отчёта на SQL. Собираем всё, что выучили за девять уроков.
Редакция Питоники
Девять уроков ты шёл к этому: теперь не упражнения, а проект. База книжной библиотеки — маленькая, но устроена как настоящая: у книг есть авторы, у читателей — сроки возврата, у библиотекаря — четыре отчёта, без которых не начинается понедельник. Мы спроектируем схему, наполним базу, напишем отчёты — и в конце тебя ждёт чек-лист, который покажет, чем ты теперь умеешь.
Шаг 1. Схема: три таблицы со связями
Библиотека просится на три таблицы. authors — авторы: имя и id. books — книги: название, жанр, цена и ссылка author_id на автора. loans — выдачи: какая книга (book_id), кому (reader), когда взяли (taken_on) и до когда срок (due_date), а returned_on — дата возврата, и NULL означает «всё ещё на руках». Ссылки между таблицами — это FOREIGN KEY: ограничение, которое не даст вставить книгу несуществующего автора.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("PRAGMA foreign_keys = ON")
cur.execute(
"""
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
)
"""
)
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id),
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute(
"""
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
reader TEXT NOT NULL,
taken_on TEXT NOT NULL,
due_date TEXT NOT NULL,
returned_on TEXT
)
"""
)
for (name,) in cur.execute(
"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"
):
print(name)
authors books loans
Читай схему сверху вниз: INTEGER PRIMARY KEY — автономер, он же идентификатор для связей; NOT NULL — «колонка обязательна»; UNIQUE — без дублей; DEFAULT 0 — значение по умолчанию. Всё это — инструменты урока про типы и CREATE TABLE. Строка REFERENCES authors(id) — внешняя ссылка: при включённом PRAGMA foreign_keys = ON (в SQLite он по умолчанию выключен и включается на каждое соединение) база не примет книгу с автором, которого нет. Список таблиц в конце мы вытащили из sqlite_master — служебного каталога, где SQLite хранит описание самой базы.
Шаг 2. Наполняем базу executemany
Наполнение — по одному executemany на таблицу, как в уроке про INSERT и SELECT. Даты пишем строками формата «ГГГГ-ММ-ДД»: в этом формате строковая сортировка совпадает с хронологией — «2026-07-21» меньше «2026-09-20», и сравнения в WHERE работают без всяких преобразований. None в Python превращается в NULL — им помечены невозвращённые книги.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id),
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute(
"""
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
reader TEXT NOT NULL,
taken_on TEXT NOT NULL,
due_date TEXT NOT NULL,
returned_on TEXT
)
"""
)
cur.executemany(
"INSERT INTO authors (id, name) VALUES (?, ?)",
[(1, "Пушкин"), (2, "Гоголь"), (3, "Достоевский"), (4, "Толстой"), (5, "Оруэлл")],
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
cur.executemany(
"""
INSERT INTO loans (id, book_id, reader, taken_on, due_date, returned_on)
VALUES (?, ?, ?, ?, ?, ?)
""",
[
(1, 1, "Аня", "2026-08-10", "2026-08-31", "2026-08-28"),
(2, 4, "Борис", "2026-09-01", "2026-09-20", None),
(3, 7, "Вера", "2026-09-05", "2026-09-26", None),
(4, 4, "Глеб", "2026-07-01", "2026-07-21", None),
(5, 1, "Даша", "2026-09-10", "2026-10-01", None),
(6, 3, "Аня", "2026-08-15", "2026-09-05", "2026-09-04"),
(7, 4, "Ева", "2026-09-12", "2026-10-02", None),
],
)
con.commit()
for table in ("authors", "books", "loans"):
n = cur.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
print(f"{table}: {n} строк")
authors: 5 строк books: 8 строк loans: 7 строк
Пять авторов, восемь книг, семь выдач — и уже видно, зачем связи: название книги хранится один раз, а не повторяется в каждой выдаче. На руках сейчас пять экземпляров: у «Преступления и наказания» три читателя сразу, и один из них держит книгу с июля — скоро увидим это в отчётах.
Отчёт 1. Каталог: JOIN книг с авторами
Классика из урока про JOIN: книги живут в одной таблице, имена авторов — в другой, а читателю нужно всё сразу. Алиасы b и a сокращают запись, ORDER BY b.id фиксирует порядок каталога.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id),
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.executemany(
"INSERT INTO authors (id, name) VALUES (?, ?)",
[(1, "Пушкин"), (2, "Гоголь"), (3, "Достоевский"), (4, "Толстой"), (5, "Оруэлл")],
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
con.commit()
cur.execute(
"""
SELECT b.title, a.name, b.price
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY b.id
"""
)
for title, author, price in cur.fetchall():
print(f"{title} — {author}, {price:.0f} руб.")
Евгений Онегин — Пушкин, 600 руб. Капитанская дочка — Пушкин, 350 руб. Мёртвые души — Гоголь, 450 руб. Преступление и наказание — Достоевский, 520 руб. Идиот — Достоевский, 480 руб. Война и мир — Толстой, 900 руб. 1984 — Оруэлл, 400 руб. Скотный двор — Оруэлл, 320 руб.
Отчёт 2. Топ-3 читаемого: GROUP BY и LIMIT
Сколько раз брали каждую книгу? Соединяем выдачи с книгами, группируем по book_id, считаем COUNT, сортируем по убыванию и оставляем три строки — весь арсенал уроков про GROUP BY и ORDER BY с LIMIT в одном запросе. Один нюанс: у «Мёртвых душ» и «1984» по одной выдаче, и без второго ключа сортировки SQLite вернул бы их в произвольном порядке. Поэтому ORDER BY cnt DESC, b.title: при равном счёте — по алфавиту, результат воспроизводим.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute(
"""
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
reader TEXT NOT NULL,
taken_on TEXT NOT NULL,
due_date TEXT NOT NULL,
returned_on TEXT
)
"""
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
cur.executemany(
"""
INSERT INTO loans (id, book_id, reader, taken_on, due_date, returned_on)
VALUES (?, ?, ?, ?, ?, ?)
""",
[
(1, 1, "Аня", "2026-08-10", "2026-08-31", "2026-08-28"),
(2, 4, "Борис", "2026-09-01", "2026-09-20", None),
(3, 7, "Вера", "2026-09-05", "2026-09-26", None),
(4, 4, "Глеб", "2026-07-01", "2026-07-21", None),
(5, 1, "Даша", "2026-09-10", "2026-10-01", None),
(6, 3, "Аня", "2026-08-15", "2026-09-05", "2026-09-04"),
(7, 4, "Ева", "2026-09-12", "2026-10-02", None),
],
)
con.commit()
cur.execute(
"""
SELECT b.title, COUNT(*) AS cnt
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY l.book_id
ORDER BY cnt DESC, b.title
LIMIT 3
"""
)
print("Топ-3 читаемого:")
for place, (title, cnt) in enumerate(cur.fetchall(), start=1):
print(f"{place}. {title} — выдач: {cnt}")
Топ-3 читаемого: 1. Преступление и наказание — выдач: 3 2. Евгений Онегин — выдач: 2 3. 1984 — выдач: 1
«Преступление и наказание» — безусловный лидер: три выдачи из семи. «1984» обошёл «Мёртвые души» только благодаря второму ключу сортировки — цифра в названии по таблице кодировок стоит раньше букв. Убирай b.title из ORDER BY и запускай блок много раз: порядок третьего места перестанет быть гарантированным.
Отчёт 3. Просроченные выдачи: WHERE по датам
Главный отчёт библиотекаря: кто не вернул книги, срок по которым уже прошёл. Условие двухчастное: returned_on IS NULL — книга всё ещё на руках, due_date < '2026-09-24' — срок истёк раньше сегодняшнего дня, который мы передаём строкой-параметром. Именно тут раскрывается выбор формата дат: сравнение строк «ГГГГ-ММ-ДД» — это сравнение календарей.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute(
"""
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
reader TEXT NOT NULL,
taken_on TEXT NOT NULL,
due_date TEXT NOT NULL,
returned_on TEXT
)
"""
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
cur.executemany(
"""
INSERT INTO loans (id, book_id, reader, taken_on, due_date, returned_on)
VALUES (?, ?, ?, ?, ?, ?)
""",
[
(1, 1, "Аня", "2026-08-10", "2026-08-31", "2026-08-28"),
(2, 4, "Борис", "2026-09-01", "2026-09-20", None),
(3, 7, "Вера", "2026-09-05", "2026-09-26", None),
(4, 4, "Глеб", "2026-07-01", "2026-07-21", None),
(5, 1, "Даша", "2026-09-10", "2026-10-01", None),
(6, 3, "Аня", "2026-08-15", "2026-09-05", "2026-09-04"),
(7, 4, "Ева", "2026-09-12", "2026-10-02", None),
],
)
con.commit()
today = "2026-09-24"
cur.execute(
"""
SELECT l.reader, b.title, l.due_date
FROM loans l
JOIN books b ON b.id = l.book_id
WHERE l.returned_on IS NULL AND l.due_date < ?
ORDER BY l.due_date
""",
(today,),
)
print("Просроченные выдачи:")
for reader, title, due_date in cur.fetchall():
print(f"{reader}, «{title}» — срок был {due_date}")
Просроченные выдачи: Глеб, «Преступление и наказание» — срок был 2026-07-21 Борис, «Преступление и наказание» — срок был 2026-09-20
Две строки, и обе про одну книгу — читатель-лидер подвёл библиотеку дважды. Вера в отчёт не попала: её срок — 2026-09-26, два дня в запасе, IS NULL без просрочки её не пропускает. Сортировка ORDER BY l.due_date ставит самую давнюю просрочку первой — именно с неё библиотекарь и начинает звонки.
Отчёт 4. Статистика по жанрам: агрегаты
Финальный отчёт отвечает на вопрос «что у нас вообще есть»: количество книг и средняя цена по каждому жанру. Это GROUP BY и агрегаты из урока 6 — COUNT и AVG в одном запросе, с округлением через ROUND.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
con.commit()
cur.execute(
"""
SELECT genre,
COUNT(*) AS books_cnt,
ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY books_cnt DESC, genre
"""
)
for genre, cnt, avg_price in cur.fetchall():
print(f"{genre}: книг {cnt}, средняя цена {avg_price}")
роман: книг 5, средняя цена 570.0 антиутопия: книг 2, средняя цена 360.0 поэма: книг 1, средняя цена 450.0
Романы рулят: пять книг при средней цене 570. ROUND(AVG(price), 2) прячет хвосты деления — пользователю не нужны цены вроде 570.0000000001, а вторым ключом сортировки жанры при равном количестве выстраиваются по алфавиту.
Как выглядит весь проект целиком?
Отчёты выше показаны по отдельности, но в жизни это один скрипт. Вот он целиком — схема, наполнение и все четыре отчёта. Запусти и держи под рукой: это готовый каркас небольшой базы.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
# --- схема: три таблицы со связями ---
cur.execute(
"""
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
)
"""
)
cur.execute(
"""
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id),
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute(
"""
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id),
reader TEXT NOT NULL,
taken_on TEXT NOT NULL,
due_date TEXT NOT NULL,
returned_on TEXT
)
"""
)
# --- наполнение ---
cur.executemany(
"INSERT INTO authors (id, name) VALUES (?, ?)",
[(1, "Пушкин"), (2, "Гоголь"), (3, "Достоевский"),
(4, "Толстой"), (5, "Оруэлл")],
)
cur.executemany(
"INSERT INTO books (id, title, author_id, genre, price) VALUES (?, ?, ?, ?, ?)",
[
(1, "Евгений Онегин", 1, "роман", 600.0),
(2, "Капитанская дочка", 1, "роман", 350.0),
(3, "Мёртвые души", 2, "поэма", 450.0),
(4, "Преступление и наказание", 3, "роман", 520.0),
(5, "Идиот", 3, "роман", 480.0),
(6, "Война и мир", 4, "роман", 900.0),
(7, "1984", 5, "антиутопия", 400.0),
(8, "Скотный двор", 5, "антиутопия", 320.0),
],
)
cur.executemany(
"""
INSERT INTO loans (id, book_id, reader, taken_on, due_date, returned_on)
VALUES (?, ?, ?, ?, ?, ?)
""",
[
(1, 1, "Аня", "2026-08-10", "2026-08-31", "2026-08-28"),
(2, 4, "Борис", "2026-09-01", "2026-09-20", None),
(3, 7, "Вера", "2026-09-05", "2026-09-26", None),
(4, 4, "Глеб", "2026-07-01", "2026-07-21", None),
(5, 1, "Даша", "2026-09-10", "2026-10-01", None),
(6, 3, "Аня", "2026-08-15", "2026-09-05", "2026-09-04"),
(7, 4, "Ева", "2026-09-12", "2026-10-02", None),
],
)
con.commit()
# --- отчёт 1: каталог ---
print("Каталог:")
cur.execute(
"""
SELECT b.title, a.name, b.price
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY b.id
"""
)
for title, author, price in cur.fetchall():
print(f"{title} — {author}, {price:.0f} руб.")
# --- отчёт 2: топ-3 читаемого ---
print("Топ-3 читаемого:")
cur.execute(
"""
SELECT b.title, COUNT(*) AS cnt
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY l.book_id
ORDER BY cnt DESC, b.title
LIMIT 3
"""
)
for place, (title, cnt) in enumerate(cur.fetchall(), start=1):
print(f"{place}. {title} — выдач: {cnt}")
# --- отчёт 3: просроченные выдачи ---
print("Просроченные выдачи:")
cur.execute(
"""
SELECT l.reader, b.title, l.due_date
FROM loans l
JOIN books b ON b.id = l.book_id
WHERE l.returned_on IS NULL AND l.due_date < ?
ORDER BY l.due_date
""",
("2026-09-24",),
)
for reader, title, due_date in cur.fetchall():
print(f"{reader}, «{title}» — срок был {due_date}")
# --- отчёт 4: жанры ---
print("Жанры:")
cur.execute(
"""
SELECT genre,
COUNT(*) AS books_cnt,
ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY books_cnt DESC, genre
"""
)
for genre, cnt, avg_price in cur.fetchall():
print(f"{genre}: книг {cnt}, средняя цена {avg_price}")
Каталог: Евгений Онегин — Пушкин, 600 руб. Капитанская дочка — Пушкин, 350 руб. Мёртвые души — Гоголь, 450 руб. Преступление и наказание — Достоевский, 520 руб. Идиот — Достоевский, 480 руб. Война и мир — Толстой, 900 руб. 1984 — Оруэлл, 400 руб. Скотный двор — Оруэлл, 320 руб. Топ-3 читаемого: 1. Преступление и наказание — выдач: 3 2. Евгений Онегин — выдач: 2 3. 1984 — выдач: 1 Просроченные выдачи: Глеб, «Преступление и наказание» — срок был 2026-07-21 Борис, «Преступление и наказание» — срок был 2026-09-20 Жанры: роман: книг 5, средняя цена 570.0 антиутопия: книг 2, средняя цена 360.0 поэма: книг 1, средняя цена 450.0
Структура всегда одна: схема → данные → запросы. Замени книги на товары, выдачи на заказы, читателей на клиентов — и у тебя база интернет-магазина. SQL при этом не меняется ни на букву.
import sqlite3
con = sqlite3.connect("library.db") # файл на диске
cur = con.cursor()
with con: # commit при выходе без ошибок
cur.execute(
"""
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL DEFAULT 0
)
"""
)
cur.execute("CREATE INDEX IF NOT EXISTS idx_books_title ON books(title)")
con.close()
Чек-лист: SQL освоен
Пройдись по списку и отметь пункты, в которых уверен. Где сомневаешься — ссылка ведёт ровно туда, где это чинится:
- создать базу, подключиться connect-ом и выполнить первый запрос — ](/sqlite/urok-1/);
- спроектировать таблицу: типы, PRIMARY KEY, NOT NULL, DEFAULT — ](/sqlite/urok-2/);
- наполнить её INSERT-ом с параметрами ? и прочитать SELECT-ом — ](/sqlite/urok-3/);
- фильтровать строки: WHERE, LIKE, BETWEEN, IN, IS NULL — ](/sqlite/urok-4/);
- управлять выдачей: ORDER BY, LIMIT, OFFSET, DISTINCT — ](/sqlite/urok-5/);
- считать агрегаты: COUNT, SUM, AVG с GROUP BY и HAVING — ](/sqlite/urok-6/);
- соединять таблицы INNER и LEFT JOIN-ом — ](/sqlite/urok-7/);
- менять и удалять данные безопасно, со страховкой rowcount — ](/sqlite/urok-8/);
- заворачивать операции в транзакции и ускорять запросы индексами — ](/sqlite/urok-9/);
- собрать базу со схемой, связями и отчётами — этот урок. Поздравляем, чек-лист закрыт.
Итоги курса
Десять уроков назад SQL был набором заклинаний, теперь ты читаешь чужие запросы и пишешь свои: от CREATE TABLE до отчёта с JOIN-ом, GROUP BY и сортировкой по двум ключам. Дальше — применять это в настоящих приложениях: FastAPI подключает SQLite к API, а проект-трекер расходов на aiogram хранит в базе каждый чек бота. И главное: SQL почти не менялся за сорок лет, и всё, что ты собрал здесь, заработает без переделки в FastAPI, Flask и телеграм-боте. Какой будет твоя следующая база — выбирай.
База данных — это не про хранение. Это про вопросы, на которые ты теперь умеешь отвечать.
Сначала предскажи ответ в голове — это главный навык программиста.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)")
cur.execute("CREATE TABLE loans (book_id INTEGER)")
cur.executemany(
"INSERT INTO books VALUES (?, ?)",
[(1, "Онегин"), (2, "Идиот"), (3, "1984")],
)
cur.executemany("INSERT INTO loans VALUES (?)", [(1,), (1,), (2,), (3,)])
cur.execute(
"""
SELECT b.title, COUNT(*) AS cnt
FROM loans l JOIN books b ON b.id = l.book_id
GROUP BY l.book_id
ORDER BY cnt DESC, b.title
LIMIT 2
"""
)
print(cur.fetchall())
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
"CREATE TABLE loans (id INTEGER PRIMARY KEY, due_date TEXT, returned_on TEXT)"
)
cur.executemany(
"INSERT INTO loans (due_date, returned_on) VALUES (?, ?)",
[("2026-09-01", "2026-09-02"), ("2026-09-20", None), ("2026-10-01", None)],
)
print(
cur.execute(
"SELECT COUNT(*) FROM loans WHERE returned_on IS NULL AND due_date < '2026-09-24'"
).fetchone()[0]
)
1. Что делает строка author_id INTEGER NOT NULL REFERENCES authors(id)?
2. Какой JOIN покажет все книги, включая те, что ни разу не брали?
3. Почему сравнение due_date < '2026-09-24' корректно работает для дат-строк?
4. Зачем в отчёте топа второй ключ сортировки: ORDER BY cnt DESC, b.title?
5. Почему для наполнения таблиц использовали executemany, а не цикл с execute?
В редакторе — база библиотеки: книги и выдачи. Напиши пятый отчёт: найди книги, которые ещё ни разу не брали. Нужен LEFT JOIN с loans и условие, отбирающее строки без пары. Выведи только названия в порядке b.id.
Как перенести проект с :memory: на файл базы данных?
Замените sqlite3.connect(":memory:") на sqlite3.connect("library.db") — все запросы останутся теми же. Добавьте commit() после изменений и con.close() в конце, а схеме — IF NOT EXISTS, чтобы повторный запуск не падал. В веб-приложениях подключением занимается фреймворк: так работает FastAPI со своей SQLite.
Почему SQLite не ругается на несуществующий author_id?
Потому что проверка внешних ключей в SQLite по умолчанию выключена — для совместимости со старыми базами. Включите её командой PRAGMA foreign_keys = ON сразу после connect: это делается на каждое соединение. После этого вставка книги с автором, которого нет в authors, упадёт с ошибкой FOREIGN KEY constraint failed.
Как хранить даты, если у SQLite нет отдельного типа дат?
Строками формата «ГГГГ-ММ-ДД»: в нём обычное строковое сравнение совпадает с хронологией, поэтому ORDER BY и условия вида due_date < '2026-09-24' работают без преобразований. Для невозвращённых книг используйте NULL и проверяйте его через IS NULL. Проект урока построен ровно на этом приёме.
Что изучать после этого курса SQL?
Следующий уровень языка — подзапросы со связью, оконные функции и составные индексы; фундамент у тебя уже есть. Лучшее закрепление — применить SQL в настоящем приложении: телеграм-бот хранит в SQLite каждый расход пользователя, а API — записи каталога. Ещё один мост — pd.read_sql: выученные SELECT-ы работают прямо в Pandas.
Понравился урок? Сошлитесь на него
«SQL почти не менялся за сорок лет, и всё, что ты собрал здесь, заработает без переделки в FastAPI, Flask и телеграм-боте.»
Скопируйте готовую ссылку в формате HTML, Markdown или чистый адрес и вставьте в статью на Habr, VC, Telegram-канал или свой блог — так о проекте узнают новые читатели.
Что читать дальше
sqlite3 · Урок 7
JOIN в SQL: соединяем таблицы INNER и LEFT JOIN
Данные живут в разных таблицах — JOIN соединяет их по ключу: INNER оставляет только пары, LEFT бережёт и одиночек.
FastAPI · Урок 7
Подключаем базу данных SQLite к FastAPI
Словарь задач из урока 5 умирает при перезапуске. Ставим на его место SQLite через SQLAlchemy: движок, сессии, Depends и CRUD, который переживает uvicorn --reload.
aiogram · Урок 10
Проект: телеграм-бот трекер расходов на aiogram с базой данных
Сквозной проект курса: бот-трекер расходов с inline-кнопками, FSM-диалогом, SQLite-хранилищем и отчётом за месяц. Рабочая версия в песочнице, полный код на aiogram и чек-лист запуска.
Похожие уроки по темам
Подобраны автоматически по пересечению тем и ключевых слов.
Flask · Урок 5
База данных в Flask: SQLite и Flask-SQLAlchemy
Пятый урок курса Flask: данные переживают перезапуск сервера. Подключаем SQLite, описываем модели Flask-SQLAlchemy, проходим CRUD и собираем гостевую книгу, которая помнит всех гостей.
flask и база данных sqlalchemysqlalchemy sqlite
json · Урок 20
Проект: каталог книг — от URL до файла
Финал раздела: один скрипт собирает каталог книг — параметры в URL, разбор ответа, фильтр по рейтингу, сортировка по цене и проверенный файл на диске.
python проект json api каталогpython проект пример для начинающих
sqlite3 · Урок 1
SQL с нуля через Python: первая база SQLite и таблица
Первая настоящая база данных без установки серверов: import sqlite3, курсор, CREATE TABLE и первые строки — всё исполняется прямо на странице.
sql с нуляsql для начинающих