Школьная библиотека хранит список книг в JSON-файле. Чтобы найти книгу, читаешь весь файл, чтобы изменить одну книгу — переписываешь всё, а если запись оборвётся на середине, файл испорчен. Эти проблемы решает база данных, а SQLite — самая простая из них: ей не нужен сервер, вся база — один файл, а в Python она входит как модуль sqlite3. SQLite работает в телефонах, браузерах и тысячах программ. В курсе SQL ты писал запросы вручную, теперь их будет выполнять программа на Python.
Соединение и курсорОпределение
Соединение (sqlite3.connect(...)) — открытая связь с одной базой: оно управляет транзакциями (commit, rollback) и должно закрываться. Курсор (conn.cursor()) выполняет запрос и выдаёт строки результата по одной. conn.execute(...) — сокращение, которое само создаёт курсор и возвращает его.
Подключение и первая таблица
sqlite3.connect('library.db') открывает файл (и создаёт его, если его нет), а sqlite3.connect(':memory:') создаёт временную базу в памяти, которая исчезает при закрытии соединения, — идеально для примеров. SQL-текст выполняет execute. Изменения не фиксируются, пока не вызван conn.commit(). Столбец INTEGER PRIMARY KEY сам нумерует новые строки, а cur.lastrowid показывает номер.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
year INTEGER,
copies INTEGER DEFAULT 1
)
''')
cur.execute("INSERT INTO books (title, author, year) VALUES ('Ali and Nino', 'Kurban Said', 1937)")
print(cur.lastrowid, cur.rowcount)
conn.commit()
cur.execute('SELECT id, title, year, copies FROM books')
print(cur.fetchall())
conn.close()Ожидаемый результат
1 1 [(1, 'Ali and Nino', 1937, 1)]
lastrowid равен 1 (это id новой книги), а rowcount — число изменённых строк. Столбец copies не указан, поэтому сработал DEFAULT 1. Результат — список кортежей.Подключиться
conn = sqlite3.connect('library.db')— файл создаётся, если его нет.Выполнить с параметрами
conn.execute('... WHERE id = ?', (book_id,))— значения только параметрами.Прочитать
fetchone(),fetchall()или цикл по курсору.Зафиксировать
conn.commit()или блокwith conn:.Закрыть
conn.close(), когда работа закончена.
| Python | SQLite |
|---|---|
None | NULL |
int | INTEGER |
float | REAL |
str | TEXT |
bytes | BLOB |
'2026-10-03'): он правильно сортируется и работает с функциями дат SQL.Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Параметры вместо склейки строк
Вклеивать введённое пользователем значение в текст SQL через f-строку — самая опасная ошибка. Если в значении есть апостроф, он закрывает строку, и остаток читается как SQL-код, — это называется SQL-инъекцией. В параметризованном запросе в тексте стоят только заполнители ?, значения передаются отдельно и никогда не читаются как код.
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE users (name TEXT, password TEXT)')
conn.executemany('INSERT INTO users VALUES (?, ?)', [('aysel', 'x7!kL'), ('murad', 'p@ss9')])
def login_unsafe(name, password):
sql = f"SELECT name FROM users WHERE name = '{name}' AND password = '{password}'"
return conn.execute(sql).fetchall()
def login_safe(name, password):
sql = 'SELECT name FROM users WHERE name = ? AND password = ?'
return conn.execute(sql, (name, password)).fetchall()
attack = "' OR '1'='1"
print(login_unsafe('aysel', 'wrong'))
print(login_unsafe('aysel', attack))
print(login_safe('aysel', attack))
print(login_safe('aysel', 'x7!kL'))Ожидаемый результат
[]
[('aysel',), ('murad',)]
[]
[('aysel',)]Пример: инъекция по шагам
Задача
Какой SQL выполняет login_unsafe при password = "' OR '1'='1" и почему пускает всех?
Показать решениеСкрыть решение
Решение
WHERE name = 'aysel' AND password = '' OR '1'='1'.2)
AND вычисляется раньше OR: (name = 'aysel' AND password = '') OR '1'='1'.3)
'1'='1' истинно для каждой строки, значит, условие истинно для всех строк, и запрос возвращает всех пользователей.4) С
? текст ' OR '1'='1 сравнивается как пароль; такого пароля нет — результат [].Есть два стиля заполнителей: ? берёт значения из кортежа по порядку, :name — из словаря по имени. Внимание: кортеж из одного значения пишется с запятой — (author,). Без запятой (author) — просто строка в скобках, и Python считает каждую её букву отдельным параметром. Заполнители — только для значений; имена таблиц и столбцов параметрами передать нельзя.
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (title TEXT, author TEXT, year INTEGER)')
conn.executemany('INSERT INTO books VALUES (:title, :author, :year)', [
{'title': 'Animal Farm', 'author': 'George Orwell', 'year': 1945},
{'title': 'Nineteen Eighty-Four', 'author': 'George Orwell', 'year': 1949},
{'title': 'Robinson Crusoe', 'author': 'Daniel Defoe', 'year': 1719},
])
author = 'George Orwell'
print(conn.execute('SELECT title FROM books WHERE author = ? ORDER BY year', (author,)).fetchall())
print(conn.execute('SELECT title FROM books WHERE year > :after', {'after': 1900}).fetchall())
try:
conn.execute('SELECT title FROM books WHERE author = ?', (author))
except sqlite3.ProgrammingError as e:
print('ProgrammingError:', e)Ожидаемый результат
[('Animal Farm',), ('Nineteen Eighty-Four',)]
[('Animal Farm',), ('Nineteen Eighty-Four',)]
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 13 supplied.'George Orwell' 13 символов — кортеж без запятой передался как 13 параметров. executemany со списком словарей заполнил имена :title, :author и :year.Чтение результатов: методы fetch и row_factory
Курсор можно перебирать: for row in conn.execute(...) выдаёт строки по одной и экономит память на больших результатах. fetchone() возвращает следующую строку или None, когда строки кончились, а fetchall() — все оставшиеся строки списком. executemany повторяет одну команду для каждого элемента списка — самый быстрый способ добавить тысячи строк. По умолчанию строка — кортеж; после conn.row_factory = sqlite3.Row к столбцам можно обращаться по имени: row['title'], dict(row).
import sqlite3
books = [
('Ali and Nino', 'Kurban Said', 1937, 3),
('The Little Prince', 'Antoine de Saint-Exupéry', 1943, 5),
('Robinson Crusoe', 'Daniel Defoe', 1719, 2),
('Animal Farm', 'George Orwell', 1945, 4),
('Nineteen Eighty-Four', 'George Orwell', 1949, 0),
]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER, copies INTEGER)')
cur = conn.executemany('INSERT INTO books (title, author, year, copies) VALUES (?, ?, ?, ?)', books)
print('inserted:', cur.rowcount)
conn.row_factory = sqlite3.Row
for row in conn.execute('SELECT id, title, year FROM books WHERE year < ? ORDER BY year', (1940,)):
print(row['id'], row['title'], row['year'])
row = conn.execute('''SELECT author, COUNT(*) AS n, SUM(copies) AS total FROM books
GROUP BY author ORDER BY n DESC, author LIMIT 1''').fetchone()
print(dict(row), row.keys())
print(conn.execute('SELECT title FROM books WHERE copies = 99').fetchone())Ожидаемый результат
inserted: 5
3 Robinson Crusoe 1719
1 Ali and Nino 1937
{'author': 'George Orwell', 'n': 2, 'total': 4} ['author', 'n', 'total']
Nonerow_factory задаётся у соединения и действует на последующие запросы. Если подходящей строки нет, fetchone() возвращает None — всегда проверяй это.| Метод / атрибут | Что делает |
|---|---|
conn.execute(sql, params) | выполняет одну команду, возвращает курсор |
conn.executemany(sql, seq) | повторяет команду для каждого элемента последовательности |
conn.executescript(text) | выполняет несколько команд через ; (без параметров) |
fetchone(), fetchall(), fetchmany(n) | одна строка / все / n строк |
cur.lastrowid, cur.rowcount | id последней добавленной строки / число изменённых строк |
conn.commit(), conn.rollback(), conn.close() | зафиксировать / отменить / закрыть |
Транзакции и with
ТранзакцияОпределение
Группа изменений, выполняемая по принципу «всё или ничего». COMMIT фиксирует их все сразу, ROLLBACK отменяет все, будто ничего не было. Классический пример — перевод денег: списание с одного счёта и зачисление на другой должны произойти вместе.
По умолчанию модуль sqlite3 сам открывает транзакцию перед INSERT, UPDATE и DELETE, а завершать её должен ты через commit(). Удобнее всего блок with conn:: если блок завершился без ошибок — COMMIT, если возникло исключение — ROLLBACK. Но соединение остаётся открытым — закрывай его через close() или contextlib.closing (см. «Контекстные менеджеры и оператор with»). Теория — в уроке курса SQL «Индексы, представления и транзакции».
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('''CREATE TABLE accounts (
name TEXT PRIMARY KEY,
balance INTEGER NOT NULL CONSTRAINT non_negative CHECK (balance >= 0))''')
with conn:
conn.executemany('INSERT INTO accounts VALUES (?, ?)', [('Aysel', 100), ('Murad', 50)])
def transfer(src, dst, amount):
try:
with conn: # one transaction: COMMIT on success, ROLLBACK on an error
conn.execute('UPDATE accounts SET balance = balance + ? WHERE name = ?', (amount, dst))
conn.execute('UPDATE accounts SET balance = balance - ? WHERE name = ?', (amount, src))
print(f'{src} -> {dst}: {amount} OK')
except sqlite3.IntegrityError as e:
print(f'{src} -> {dst}: {amount} cancelled ({e})')
transfer('Aysel', 'Murad', 30)
transfer('Murad', 'Aysel', 500)
print(conn.execute('SELECT * FROM accounts ORDER BY name').fetchall())
conn.close()Ожидаемый результат
Aysel -> Murad: 30 OK
Murad -> Aysel: 500 cancelled (CHECK constraint failed: non_negative)
[('Aysel', 70), ('Murad', 80)]CHECK вызвало IntegrityError. with выполнил ROLLBACK, поэтому уже записанные Айсель 500 тоже отменились.Мини-приложение: библиотека
Соберём всё вместе. connect открывает файл и готовит таблицы через CREATE TABLE IF NOT EXISTS, а borrow проверяет остаток книги, уменьшает его и записывает выдачу в одной транзакции. В конце соединение закрывается и открывается снова: данные остались в файле.
import sqlite3
def connect(path):
conn = sqlite3.connect(path)
conn.row_factory = sqlite3.Row
conn.executescript('''
CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT UNIQUE NOT NULL,
copies INTEGER NOT NULL CHECK (copies >= 0));
CREATE TABLE IF NOT EXISTS loans (book_id INTEGER REFERENCES books (id), reader TEXT NOT NULL);
''')
return conn
def borrow(conn, title, reader):
with conn:
book = conn.execute('SELECT id, copies FROM books WHERE title = ?', (title,)).fetchone()
if book is None or book['copies'] == 0:
return f'{reader}: {title!r} is not available'
conn.execute('UPDATE books SET copies = copies - 1 WHERE id = ?', (book['id'],))
conn.execute('INSERT INTO loans VALUES (?, ?)', (book['id'], reader))
return f'{reader} borrowed {title!r}'
conn = connect('library.db')
with conn:
conn.executemany('INSERT INTO books (title, copies) VALUES (?, ?)',
[('Ali and Nino', 1), ('The Little Prince', 2)])
for reader, title in [('Aysel', 'Ali and Nino'), ('Murad', 'Ali and Nino'), ('Leyla', 'Dune')]:
print(borrow(conn, title, reader))
conn.close()
conn = connect('library.db') # a new connection: the data is still in the file
sql = '''SELECT b.title, b.copies, COUNT(l.reader) AS loans
FROM books b LEFT JOIN loans l ON l.book_id = b.id GROUP BY b.id ORDER BY b.title'''
for row in conn.execute(sql):
print(dict(row))
conn.close()Ожидаемый результат
Aysel borrowed 'Ali and Nino'
Murad: 'Ali and Nino' is not available
Leyla: 'Dune' is not available
{'title': 'Ali and Nino', 'copies': 0, 'loans': 1}
{'title': 'The Little Prince', 'copies': 2, 'loans': 0}LEFT JOIN показывает и книги без выдач (loans 0). В браузере library.db при каждом запуске создаётся заново в пустой папке.Когда проект растёт, SQL-строки, ручное копирование имён столбцов и превращение строк в объекты утомляют. SQLAlchemy делает ту же работу через классы Python, и тот же код продолжает работать при переходе с SQLite на PostgreSQL. Сравни (подробно — в уроке «SQLAlchemy: базы данных через ORM»):
sqlite3
rows = conn.execute(
'SELECT title, year FROM books WHERE author = ? ORDER BY year',
('George Orwell',),
).fetchall()SQLAlchemy
stmt = (select(Book.title, Book.year)
.where(Book.author == 'George Orwell')
.order_by(Book.year))
rows = session.execute(stmt).all()Book — класс, описывающий таблицу, а session управляет соединением и транзакцией.Задание
Добавь список products в таблицу одним вызовом executemany. Затем выбери название и цену товаров дешевле 10, которые есть на складе (stock > 0), от самого дешёвого, и напечатай каждую строку как name: price. Порог (10) передай параметром.
import sqlite3
products = [('pen', 1.5, 120), ('notebook', 3.2, 0), ('backpack', 45.0, 7),
('ruler', 0.8, 60), ('calculator', 18.9, 12)]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE products (name TEXT, price REAL, stock INTEGER)')
# 1) insert all products with one executemany call
# 2) select name and price of the products cheaper than 10 that are in stock, cheapest first
# 3) print every row as "name: price"Ожидаемый результат
ruler: 0.8 pen: 1.5
Задание
sell(name, qty) должна в одной транзакции добавить строку продажи в sales и уменьшить остаток в stock. Если остаток стал бы меньше 0 (ошибка CHECK), ничего не должно измениться, и функция возвращает False; при успехе — True.
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE stock (name TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0))')
conn.execute('CREATE TABLE sales (name TEXT, qty INTEGER)')
conn.executemany('INSERT INTO stock VALUES (?, ?)', [('pen', 5), ('ruler', 2)])
conn.commit()
def sell(name, qty):
# in ONE transaction: add a row to sales and reduce the stock;
# if the stock would drop below 0, nothing may change and the function returns False
return False
print(sell('pen', 3), sell('ruler', 5), sell('pen', 2))
print(conn.execute('SELECT * FROM stock ORDER BY name').fetchall())
print(conn.execute('SELECT * FROM sales').fetchall())Ожидаемый результат
True False True
[('pen', 0), ('ruler', 2)]
[('pen', 3), ('pen', 2)]Запомни
sqlite3.connect(path)открывает файл,':memory:'— временную базу;conn.executeвозвращает курсор.- Значения всегда передавай параметрами
?или:name; кортеж из одного значения —(x,). fetchone()(илиNone),fetchall()либо цикл по курсору;sqlite3.Rowдаёт доступ к столбцам по имени.executemanyдобавляет много строк одним вызовом.with conn:фиксирует при успехе и откатывает при ошибке, но соединение не закрывает;close()безcommit()теряет изменения.