Базы данных в Python: sqlite3

Работа со стандартным модулем `sqlite3`: соединение и курсор, `CREATE`/`INSERT`/`SELECT`, параметры против SQL-инъекций, `executemany`, `row_factory`, транзакции и `with`, небольшое приложение-библиотека и первый взгляд на SQLAlchemy.

  • Продвинутый
  • 22 мин
  • 12 вопросов
  • Урок 37 из 66

В этом уроке ты узнаешь

  • Подключаться к базе, создавать таблицу, добавлять строки и читать результаты запросов
  • Передавать значения только параметрами и объяснять, как работает SQL-инъекция
  • Эффективно работать с executemany, fetchone/fetchall и sqlite3.Row
  • Объединять связанные изменения в транзакцию with conn:

Школьная библиотека хранит список книг в 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 показывает номер.

Python
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. Результат — список кортежей.
  1. Подключиться

    conn = sqlite3.connect('library.db') — файл создаётся, если его нет.

  2. Выполнить с параметрами

    conn.execute('... WHERE id = ?', (book_id,)) — значения только параметрами.

  3. Прочитать

    fetchone(), fetchall() или цикл по курсору.

  4. Зафиксировать

    conn.commit() или блок with conn:.

  5. Закрыть

    conn.close(), когда работа закончена.

PythonSQLite
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB
Даты храни как ISO-текст ('2026-10-03'): он правильно сортируется и работает с функциями дат SQL.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Параметры вместо склейки строк

Вклеивать введённое пользователем значение в текст SQL через f-строку — самая опасная ошибка. Если в значении есть апостроф, он закрывает строку, и остаток читается как SQL-код, — это называется SQL-инъекцией. В параметризованном запросе в тексте стоят только заполнители ?, значения передаются отдельно и никогда не читаются как код.

Python
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" и почему пускает всех?

Показать решениеСкрыть решение

Решение

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 считает каждую её букву отдельным параметром. Заполнители — только для значений; имена таблиц и столбцов параметрами передать нельзя.

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).

Python
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']
None
row_factory задаётся у соединения и действует на последующие запросы. Если подходящей строки нет, fetchone() возвращает None — всегда проверяй это.
Метод / атрибутЧто делает
conn.execute(sql, params)выполняет одну команду, возвращает курсор
conn.executemany(sql, seq)повторяет команду для каждого элемента последовательности
conn.executescript(text)выполняет несколько команд через ; (без параметров)
fetchone(), fetchall(), fetchmany(n)одна строка / все / n строк
cur.lastrowid, cur.rowcountid последней добавленной строки / число изменённых строк
conn.commit(), conn.rollback(), conn.close()зафиксировать / отменить / закрыть

Транзакции и with

ТранзакцияОпределение

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

По умолчанию модуль sqlite3 сам открывает транзакцию перед INSERT, UPDATE и DELETE, а завершать её должен ты через commit(). Удобнее всего блок with conn:: если блок завершился без ошибок — COMMIT, если возникло исключение — ROLLBACK. Но соединение остаётся открытым — закрывай его через close() или contextlib.closing (см. «Контекстные менеджеры и оператор with»). Теория — в уроке курса SQL «Индексы, представления и транзакции».

Python
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)]
Во втором переводе Айсель получила 500, затем баланс Мурада стал бы отрицательным, и ограничение CHECK вызвало IntegrityError. with выполнил ROLLBACK, поэтому уже записанные Айсель 500 тоже отменились.

Мини-приложение: библиотека

Соберём всё вместе. connect открывает файл и готовит таблицы через CREATE TABLE IF NOT EXISTS, а borrow проверяет остаток книги, уменьшает его и записывает выдачу в одной транзакции. В конце соединение закрывается и открывается снова: данные остались в файле.

Python
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()
Оба отправляют параметризованный SQL; в ORM Book — класс, описывающий таблицу, а session управляет соединением и транзакцией.

Задание

Добавь список products в таблицу одним вызовом executemany. Затем выбери название и цену товаров дешевле 10, которые есть на складе (stock > 0), от самого дешёвого, и напечатай каждую строку как name: price. Порог (10) передай параметром.

Задание · Python
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.

Задание · Python
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() теряет изменения.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Что создаёт sqlite3.connect(':memory:')?