Как работать с SQLite в Python: пошаговая инструкция с примерами кода

В этой инструкции разбираем 8 последовательных шагов работы с SQLite на Python: от создания базы данных до оптимизации запросов с индексами. Покажем, как правильно вставлять данные без SQL-инъекций, как работают fetchall(), fetchone() и fetchmany(), зачем нужны транзакции, как автоматизировать работу с триггерами и как интегрировать SQLite с Pandas. Каждый шаг — с рабочим примером кода.

Официальная документация модуля — на сайте Python.

SQLite и модуль sqlite3 — ключевые понятия

SQLite — встраиваемая реляционная СУБД (система управления базами данных): вся база хранится в одном .db-файле на диске, отдельный сервер не нужен. Это делает её идеальной для прототипирования, мобильных приложений, настольных программ и тестового окружения — всего, где нужна полноценная реляционная база без инфраструктурной сложности. SQLite полностью поддерживает ACID-свойства и SQL-синтаксис, включая транзакции, индексы, представления и триггеры.

Модуль sqlite3 входит в стандартную библиотеку Python начиная с версии 2.5 и реализует стандарт DB-API 2.0 — базовый синтаксис работы с курсором и соединением совпадает с интерфейсом других СУБД. Установка через pip не нужна.

Хотите изучить Python с нуля — и сразу делать реальные проекты? На курсе «Программирование: Уверенный старт» школьники за 36 часов осваивают Python, веб-разработку на HTML/CSS/JavaScript и Flask, а также прототипирование на Arduino. С первого занятия — код, с первого модуля — собственный Telegram-бот. Узнайте подробнее на странице курса.

Когда выбирать каждую СУБД:

• SQLite — один пользователь, небольшие объёмы данных, встроенность в приложение, прототипирование;
• MySQL — веб-проекты с умеренными нагрузками и несколькими пользователями;
• PostgreSQL — высокие нагрузки, сложные запросы, объёмы данных от нескольких гигабайт.

SQLite поддерживает пять типов данных:

Важная особенность SQLite — динамическая типизация (type affinity): тип определяется самим значением, а не только объявлением столбца. Это удобно при прототипировании, но требует внимательности при работе со строгими схемами данных.

Callout: import sqlite3 — и можно начинать. Установка не нужна.

Что понадобится для работы с SQLite в Python

Требования минимальны:

• Python 3.x любой актуальной версии — модуль sqlite3 уже включён в стандартную библиотеку;
• IDE или текстовый редактор (PyCharm, VS Code, IDLE — любой удобный);
• опционально — DB Browser for SQLite (sqlitebrowser.org) для визуального просмотра и редактирования .db-файлов без кода.

Чего точно не нужно: pip install sqlite3, отдельный сервер, настройка окружения, дополнительные зависимости.

Убедитесь, что всё работает — выполните команду в терминале:

python -c «import sqlite3; print(sqlite3.sqlite_version)»

Если в ответ появится строка вроде 3.41.2 — подключение к SQLite на Python готово, можно переходить к первому шагу.

Пошаговая инструкция по работе с SQLite в Python

Полный цикл работы с SQLite на Python — от создания соединения до оптимизации производительности. Разбиваем на 8 последовательных шагов с кодом на каждом.

Шаг 1 — Подключение к базе данных и создание курсора

Соединение с базой данных открывается через sqlite3.connect(). Если файл существует — открывается; если нет — создаётся автоматически в текущей директории.

import sqlite3

conn = sqlite3.connect(‘mydb.db’) # создаёт файл mydb.db или открывает его

cur = conn.cursor() # курсор для отправки SQL-запросов

# … работа с базой данных …

cur.close()

conn.close()

Рекомендуемый паттерн — контекстный менеджер with. Он автоматически вызывает commit() при успешном завершении блока и rollback() при исключении, а также закрывает соединение:

import sqlite3

with sqlite3.connect(‘mydb.db’) as conn:

cur = conn.cursor()

# Вся работа с базой — здесь

# commit() и close() выполнятся автоматически

Для тестирования без записи на диск — база данных в памяти:

conn = sqlite3.connect(‘:memory:’) # данные живут только в оперативной памяти

Курсор (cursor) — объект, через который отправляются все SQL-запросы. На одно соединение можно создать несколько курсоров, но обычно достаточно одного.

Шаг 2 — Создание таблицы: типы данных и DDL-синтаксис

Таблица создаётся командой CREATE TABLE. Директива IF NOT EXISTS защищает от ошибки при повторном запуске скрипта — если таблица уже существует, команда просто пропускается:

cur.execute(«»»

CREATE TABLE IF NOT EXISTS employees (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

dept TEXT,

salary REAL DEFAULT 0.0,

active INTEGER DEFAULT 1

)

«»»)

conn.commit()

Разбор структуры: PRIMARY KEY AUTOINCREMENT — уникальный целочисленный идентификатор, который SQLite увеличивает при каждой вставке. NOT NULL запрещает пустые значения в столбце name. DEFAULT задаёт значение по умолчанию.

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

После DDL-операций (CREATE TABLE, DROP TABLE, ALTER TABLE) вызывайте conn.commit() — без него изменения могут не сохраниться.

Шаг 3 — Вставка данных: INSERT и пакетная вставка executemany

Вставка одной записи через Prepared Statement (подготовленный запрос) с плейсхолдером ?:

cur.execute(

«INSERT INTO employees (name, dept, salary) VALUES (?, ?, ?)»,

(‘Анна’, ‘Разработка’, 120000.0)

)

conn.commit()

Плейсхолдер ? — единственный безопасный способ подставить данные в SQL. Сравните с уязвимым вариантом:

# ⚠️ НЕБЕЗОПАСНО — никогда не делайте так

name = «‘ OR ‘1’=’1″

cur.execute(f»SELECT * FROM employees WHERE name='{name}'»)

# Запрос: WHERE name=» OR ‘1’=’1′ — возвращает все строки

Для пакетной вставки нескольких записей используйте executemany() — он принимает SQL-шаблон и список кортежей:

new_employees = [

(‘Иван’, ‘Аналитика’, 95000.0),

(‘Мария’, ‘Дизайн’, 85000.0),

(‘Пётр’, ‘Разработка’, 130000.0),

(‘Елена’, ‘QA’, 90000.0),

]

cur.executemany(

«INSERT INTO employees (name, dept, salary) VALUES (?, ?, ?)»,

new_employees

)

conn.commit()

executemany() быстрее цикла из сотен отдельных execute() и так же безопасен — параметризация работает для каждой строки в списке.

Шаг 4 — Чтение данных: SELECT и методы fetchone, fetchall, fetchmany

Базовый SELECT:

cur.execute(«SELECT * FROM employees»)

Три метода получения результатов:

fetchone() возвращает кортеж или None — проверка обязательна:

cur.execute(«SELECT name, salary FROM employees WHERE id = ?», (5,))

row = cur.fetchone()

if row:

print(f»Имя: {row[0]}, Зарплата: {row[1]}»)

else:

print(«Запись не найдена»)

fetchall() загружает все строки в память сразу — при больших таблицах (тысячи строк и больше) лучше использовать fetchmany() или итерировать по курсору напрямую:

cur.execute(«SELECT * FROM employees»)

for row in cur: # построчная обработка без загрузки всей таблицы

print(row)

Цикл с fetchall() для небольших наборов:

rows = cur.fetchall()

for emp in rows:

print(f»{emp[1]} ({emp[2]}): {emp[3]} ₽»)

Шаг 5 — Фильтрация, сортировка и агрегация данных

WHERE с параметрами — всегда через ?:

cur.execute(

«SELECT name, dept, salary FROM employees WHERE salary > ? ORDER BY salary DESC»,

(100000,)

)

Агрегатные функции и GROUP BY:

cur.execute(«»»

SELECT dept,

COUNT(*) AS count,

AVG(salary) AS avg_salary,

MIN(salary) AS min_salary,

MAX(salary) AS max_salary

FROM employees

GROUP BY dept

HAVING COUNT(*) >= 2

ORDER BY avg_salary DESC

«»»)

results = cur.fetchall()

Поддерживаемые агрегатные функции: COUNT, SUM, AVG, MIN, MAX. HAVING фильтрует группы после GROUP BY — в отличие от WHERE, который фильтрует строки до группировки.

Пагинация через LIMIT и OFFSET:

page = 2

per_page = 10

cur.execute(

«SELECT * FROM employees ORDER BY name LIMIT ? OFFSET ?»,

(per_page, (page — 1) * per_page)

)

BETWEEN для диапазонов, LIKE для шаблонов, IN для списков — все работают с параметрами ?.

Шаг 6 — Обновление и удаление записей

Обновление записи:

cur.execute(

«UPDATE employees SET salary = ?, dept = ? WHERE id = ?»,

(140000.0, ‘Архитектура’, 1)

)

conn.commit()

Удаление записи:

cur.execute(«DELETE FROM employees WHERE id = ?», (3,))

conn.commit()

⚠️ Критичное правило: UPDATE без WHERE обновит каждую строку таблицы. DELETE без WHERE удалит все строки без предупреждения. Оба запроса — только через параметризацию (?); conn.commit() — обязателен после каждой DML-операции. Без commit() изменения останутся в буфере и пропадут при закрытии соединения.

Шаг 7 — Транзакции и ACID-свойства

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

ACID-свойства в SQLite:

• Атомарность (Atomicity) — набор операций выполняется целиком или полностью откатывается при ошибке;
• Согласованность (Consistency) — база остаётся в допустимом состоянии до и после транзакции;
• Изолированность (Isolation) — параллельные транзакции не видят незафиксированных изменений друг друга;
• Долговечность (Durability) — зафиксированные данные сохраняются даже после сбоя или перезапуска.

Паттерн 1 — явная обработка через try/except/finally:

conn = sqlite3.connect(‘mydb.db’)

try:

cur = conn.cursor()

cur.execute(«UPDATE accounts SET balance = balance — ? WHERE id = ?», (5000, 1))

cur.execute(«UPDATE accounts SET balance = balance + ? WHERE id = ?», (5000, 2))

conn.commit()

except Exception as e:

conn.rollback() # откат всех изменений при ошибке

print(f»Ошибка: {e}»)

finally:

conn.close()

Паттерн 2 — контекстный менеджер (рекомендуется):

with sqlite3.connect(‘mydb.db’) as conn:

cur = conn.cursor()

cur.execute(«UPDATE accounts SET balance = balance — ? WHERE id = ?», (5000, 1))

cur.execute(«UPDATE accounts SET balance = balance + ? WHERE id = ?», (5000, 2))

# commit() — автоматически; при исключении — автоматический rollback()

Транзакции особенно важны при пакетных вставках, финансовых операциях и обновлении нескольких связанных таблиц одновременно.

Шаг 8 — Индексы и оптимизация запросов

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

Создание индекса:

cur.execute(

«CREATE INDEX IF NOT EXISTS idx_dept ON employees(dept)»

)

conn.commit()

Влияние индекса на разные операции:

Создавайте индекс, если таблица содержит 1 000+ записей и по столбцу часто выполняются запросы с WHERE или ORDER BY. Индексы на всех столбцах подряд — лишние: каждый из них замедляет запись.

Проверка использования индекса через EXPLAIN QUERY PLAN:

cur.execute(

«EXPLAIN QUERY PLAN SELECT * FROM employees WHERE dept = ‘Разработка'»

)

print(cur.fetchall())

# Ищите ‘USING INDEX’ в выводе — значит, индекс работает

Удаление ненужного индекса:

cur.execute(«DROP INDEX IF EXISTS idx_dept»)

Продвинутые концепции: Views, Triggers и безопасность запросов

Три инструмента для реальных проектов: представления упрощают повторяющиеся выборки, триггеры автоматизируют реакцию на изменения данных, параметризованные запросы защищают от атак на уровне кода.

Представления (Views) — виртуальные таблицы

Представление (view) — виртуальная таблица. Оно не хранит данные само по себе, а содержит SQL-запрос, который выполняется каждый раз при обращении.

cur.execute(«»»

CREATE VIEW IF NOT EXISTS active_developers AS

SELECT id, name, salary

FROM employees

WHERE dept = ‘Разработка’ AND active = 1

«»»)

conn.commit()

# Обращение — как к обычной таблице

cur.execute(«SELECT * FROM active_developers ORDER BY salary DESC»)

rows = cur.fetchall()

В SQLite представления доступны только для чтения: INSERT, UPDATE или DELETE через view не работают — нужно обращаться к исходной таблице напрямую.

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

Триггеры (Triggers) — автоматизация при изменении данных

Триггер (trigger) — хранимая процедура, которая срабатывает автоматически при INSERT, UPDATE или DELETE. Создаётся один раз и работает без явного вызова.

cur.execute(«»»

CREATE TRIGGER IF NOT EXISTS log_salary_change

AFTER UPDATE ON employees

WHEN NEW.salary <> OLD.salary

BEGIN

INSERT INTO audit_log (employee_id, old_salary, new_salary, changed_at)

VALUES (OLD.id, OLD.salary, NEW.salary, datetime(‘now’));

END

«»»)

conn.commit()

Триггер AFTER UPDATE сработает после каждого обновления таблицы employees и автоматически запишет изменение зарплаты в таблицу audit_log.

Сценарии применения: журнал изменений (audit log), автообновление связанных таблиц, валидация данных перед записью. Для каждого события (BEFORE / AFTER × INSERT / UPDATE / DELETE) создаётся отдельный триггер.

Параметризованные запросы и защита от SQL-инъекций

SQL injection (SQL-инъекция) — атака, при которой злоумышленник внедряет произвольный SQL-код через пользовательский ввод. Пример уязвимого кода:

# ⚠️ НЕБЕЗОПАСНО — f-строка открывает уязвимость

user_login = «admin’ OR ‘1’=’1″

cur.execute(f»SELECT * FROM users WHERE login='{user_login}'»)

# Итоговый запрос: WHERE login=’admin’ OR ‘1’=’1′

# Результат: возвращает все строки — обход авторизации

Правило без исключений: всегда используйте ? для пользовательских данных в SQLite (в MySQL/PostgreSQL — %s):

# ✅ БЕЗОПАСНО — параметризованный запрос

cur.execute(«SELECT * FROM users WHERE login = ?», (user_login,))

executemany() тоже работает с параметризацией — пакетные вставки с ним безопасны для любых входных данных.

Интеграция SQLite с Pandas для анализа данных

Pandas — единственная внешняя библиотека в этой инструкции, которую нужно установить отдельно:

pip install pandas

Чтение таблицы SQLite в DataFrame:

import pandas as pd

import sqlite3

conn = sqlite3.connect(‘mydb.db’)

df = pd.read_sql_query(

«SELECT dept, AVG(salary) AS avg_salary, COUNT(*) AS count FROM employees GROUP BY dept»,

conn

)

print(df)

После этого доступен весь арсенал Pandas: groupby(), merge(), plot(), фильтрация через булевые маски — без написания сложного SQL. Агрегация через df.groupby(‘dept’).agg(…) часто читается проще, чем эквивалентный SQL с несколькими JOIN.

Запись DataFrame обратно в SQLite:

# Сохраняем отфильтрованный результат в новую таблицу

df_top = df[df[‘avg_salary’] > 100000]

df_top.to_sql(‘top_departments’, conn, if_exists=’replace’, index=False)

Параметр if_exists=’replace’ пересоздаёт таблицу при каждом запуске; if_exists=’append’ добавляет строки к существующей; if_exists=’fail’ выбрасывает ошибку, если таблица уже есть.

Такая интеграция объединяет надёжность SQLite как хранилища и гибкость Pandas для трансформации и визуализации: данные хранятся в .db-файле, анализ — в Python. Это удобная связка для прототипирования аналитических пайплайнов (pipeline, конвейеров обработки данных) без разворачивания тяжёлой инфраструктуры.

Типичные ошибки при работе с SQLite в Python и как их устранить

Самая распространённая причина потери данных — забытый conn.commit(). Соединение закрылось, транзакция откатилась, изменения пропали. Контекстный менеджер with sqlite3.connect(…) as conn: решает эту проблему автоматически: commit вызывается при успешном выходе из блока, rollback — при исключении.

Часто задаваемые вопросы

Нужно ли устанавливать sqlite3 в Python отдельно?

Нет. Модуль sqlite3 входит в стандартную библиотеку Python начиная с версии 2.5 и присутствует во всех современных версиях Python 3.x. Никакой команды pip install не требуется. Для начала работы достаточно написать import sqlite3. Проверить версию встроенной SQLite можно командой python -c «import sqlite3; print(sqlite3.sqlite_version)».

Где найти официальную документацию по модулю sqlite3?

Официальная документация находится на docs.python.org/3/library/sqlite3.html — исчерпывающий источник по всем методам, классам и параметрам модуля. Для изучения синтаксиса SQL подходит sqlite.org/lang.html. Эта статья даёт русскоязычный разбор с готовыми примерами кода для всех основных сценариев.

Чем fetchone() отличается от fetchall() и fetchmany()?

fetchone() возвращает одну строку в виде кортежа или None — всегда проверяйте результат на None. fetchall() возвращает сразу все строки в виде списка кортежей — подходит для небольших выборок. fetchmany(n) берёт ровно n строк — оптимально для пагинации и обработки больших наборов данных порциями без загрузки всей таблицы в память.

Зачем в запросах sqlite3 использовать знак ? вместо f-строк?

Знак ? — это плейсхолдер параметризованного запроса (Prepared Statement). Python подставляет значения безопасно, экранируя спецсимволы. F-строка f»WHERE name='{val}'» делает код уязвимым к SQL-инъекции: злоумышленник может передать ‘ OR ‘1’=’1 и получить все данные таблицы. Правило: никогда не встраивайте пользовательский ввод в SQL напрямую.

Как работает executemany() и когда его применять?

cursor.executemany(sql, data) выполняет один SQL-шаблон многократно для каждого элемента списка кортежей. Используется для пакетной вставки: вместо цикла с сотнями вызовов execute() — одна команда с commit() в конце. Это быстрее и безопаснее — параметризация работает для каждой строки. Пример: cursor.executemany(«INSERT INTO t VALUES (?,?)», [(1,’a’),(2,’b’)]).

Зачем нужны транзакции и что произойдёт, если забыть commit()?

Транзакция — группа операций, которые выполняются атомарно: либо все, либо ни одна. Без conn.commit() изменения остаются в буфере и не записываются в файл .db — после закрытия соединения они потеряются. Используйте try-except-finally с rollback() при ошибке или контекстный менеджер with, который вызывает commit автоматически.

Когда использовать SQLite, а не MySQL или PostgreSQL?

SQLite подходит для мобильных приложений, прототипов, настольных программ, тестового окружения и небольших веб-проектов с одним пользователем. MySQL и PostgreSQL нужны при высоких нагрузках, одновременном доступе многих пользователей и объёмах данных от нескольких гигабайт. Главное преимущество SQLite — нулевая настройка и встроенность в Python без дополнительных зависимостей.

Как исправить ошибку «OperationalError: database is locked»?

Ошибка возникает, когда соединение не закрыто или несколько потоков обращаются к базе одновременно. Решения: используйте with sqlite3.connect(…) as conn: — соединение закрывается автоматически; для многопоточного доступа добавьте параметр check_same_thread=False; убедитесь, что предыдущее соединение закрыто перед открытием нового.

Как интегрировать SQLite с библиотекой Pandas?

pd.read_sql_query(«SELECT * FROM table», conn) читает таблицу SQLite в DataFrame для анализа. Обратно: df.to_sql(‘table’, conn, if_exists=’replace’, index=False) записывает DataFrame в SQLite. Такой подход объединяет надёжность SQL-хранилища и возможности Pandas для агрегации, фильтрации и визуализации данных.

Нужно ли вызывать commit() после команды SELECT?

Нет. commit() нужен только после операций изменения данных: INSERT, UPDATE, DELETE, а также CREATE TABLE. После SELECT он не требуется — чтение не меняет состояние базы. При использовании with sqlite3.connect(…) as conn: commit вызывается автоматически при выходе из блока, но только если не было исключения.