Работа с SQLite в Python с помощью встроенной библиотеки sqlite3

Раздел: Библиотеки -> Библиотеки для работы с базами данных

Библиотека sqlite3 в Python

Библиотека sqlite3 входит в стандартный набор модулей Python и предоставляет интерфейс для работы с базами данных SQLite. Это легковесная реляционная СУБД, не требующая отдельного сервера. Библиотека позволяет создавать, подключаться, выполнять запросы и управлять транзакциями.

Наиболее эффективное решение для повседневных задач - использование контекстного менеджера with при подключении и выполнении запросов. Такой подход гарантирует закрытие соединения и фиксацию транзакций.

import sqlite3

# создание или подключение к базе данных
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    # создание таблицы
    cursor.execute('''CREATE TABLE IF NOT EXISTS users
                      (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
    # вставка данных
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
    # получение данных
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

библиотека sqlite3 python (библиотека sqlite3 в python)

(1, 'Alice', 30)

Sqlalchemy библиотеки python (библиотека sqlalchemy в python)

В этом примере соединение автоматически закрывается после выхода из блока with, а изменения фиксируются. Использование параметризованных запросов (знаки ?) защищает от SQL-инъекций.

Как выполнять запросы с параметрами?

Для передачи переменных в SQL-запросы применяются плейсхолдеры ? (или именованные :name).

name = 'Bob'
age = 25
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', (name, age))
conn.commit()

Это предотвращает подстановку неэкранированных строк.

Проблема: Конкатенация строк при построении запроса (например, f'SELECT * FROM users WHERE name = "{name}"') ведёт к уязвимостям SQL-инъекций и ошибкам синтаксиса. Решение: Всегда использовать параметризованные запросы.

Как получать данные в виде словаря?

По умолчанию строки возвращаются в виде кортежей. Для получения словаря (с именами столбцов) используется атрибут row_factory.

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
for row in cursor:
    print(row['name'], row['age'])
# или dict(row) для преобразования в обычный словарь

Это удобно при работе с большим количеством полей.

Как обрабатывать ошибки базы данных?

Исключения, связанные с SQLite, наследуются от sqlite3.Error. Рекомендуется оборачивать операции в блок try/except.

try:
    cursor.execute('INVALID SQL')
except sqlite3.IntegrityError as e:
    print('Нарушение целостности:', e)
except sqlite3.OperationalError as e:
    print('Ошибка выполнения:', e)
except sqlite3.Error as e:
    print('Другая ошибка SQLite:', e)

Типичные ошибки: IntegrityError - при нарушении уникальности или внешнего ключа; OperationalError - например, при попытке создать уже существующую таблицу без IF NOT EXISTS.

Как управлять транзакциями?

По умолчанию каждый execute выполняется в отдельной транзакции, если не включён isolation_level=None. Для явного управления используются commit() и rollback().

with sqlite3.connect('example.db') as conn:
    conn.execute('CREATE TABLE IF NOT EXISTS logs (msg TEXT)')
    conn.execute('INSERT INTO logs VALUES (?)', ('start',))
    conn.commit()  # фиксация
    conn.execute('INSERT INTO logs VALUES (?)', ('error',))
    conn.rollback() # откат, вставка не сохранится

Автоматическая фиксация: При использовании with контекстный менеджер вызывает commit при успешном завершении блока, иначе rollback.

Как вставлять множество записей?

Метод executemany позволяет выполнить один и тот же запрос для списка кортежей.

data = [('Charlie', 35), ('Diana', 28), ('Eve', 22)]
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', data)
conn.commit()

Это быстрее, чем вызов execute в цикле.

Как выполнить несколько операторов из скрипта?

Метод executescript принимает строку с несколькими SQL-командами, разделёнными точкой с запятой.

script = '''
CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Laptop', 999.99);
INSERT INTO products VALUES (2, 'Mouse', 25.50);
'''
cursor.executescript(script)
conn.commit()

Важно: executescript перед выполнением неявно вызывает commit для предыдущих транзакций.

Как зарегистрировать пользовательскую функцию SQLite?

Модуль позволяет добавлять функции Python, доступные в SQL-запросах.

def reverse_string(s):
    return s[::-1] if s else ''

conn.create_function('reverse', 1, reverse_string)
cursor.execute('SELECT reverse(name) FROM users')
print(cursor.fetchall())

Это полезно для вычислений, не реализованных в SQLite.

Расширенные примеры работы с sqlite3

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

Подключение к базе в оперативной памяти

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

Пример 
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE test (col1 TEXT)')
cursor.execute('INSERT INTO test VALUES ("data")')
cursor.execute('SELECT * FROM test')
print(cursor.fetchall())
conn.close()
[('data',)]

Использование собственной агрегатной функции

Пример функции, вычисляющей медиану значений.

Пример 
import sqlite3

class Median:
    def __init__(self):
        self.values = []
    def step(self, value):
        if value is not None:
            self.values.append(value)
    def finalize(self):
        if not self.values:
            return None
        sorted_vals = sorted(self.values)
        n = len(sorted_vals)
        if n % 2 == 1:
            return sorted_vals[n//2]
        else:
            return (sorted_vals[n//2 - 1] + sorted_vals[n//2]) / 2

conn = sqlite3.connect(':memory:')
conn.create_aggregate('median', 1, Median)
cursor = conn.cursor()
cursor.execute('CREATE TABLE nums (val REAL)')
cursor.execute('INSERT INTO nums VALUES (1), (2), (3), (10)')
cursor.execute('SELECT median(val) FROM nums')
print(cursor.fetchone()[0])
2.5

Работа с BLOB (бинарными данными)

SQLite поддерживает хранение бинарных объектов. В Python они передаются как bytes.

Пример 
import sqlite3

conn = sqlite3.connect('blob_example.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, data BLOB)')

with open('image.png', 'rb') as f:
    img_data = f.read()
cursor.execute('INSERT INTO images (data) VALUES (?)', (img_data,))
conn.commit()

# Чтение BLOB
cursor.execute('SELECT data FROM images WHERE id=1')
retrieved_data = cursor.fetchone()[0]
print(type(retrieved_data), len(retrieved_data))
conn.close()

Результат: 

<class 'bytes'> 12345 (размер файла)

Использование режима WAL (Write-Ahead Logging)

Режим WAL улучшает параллельный доступ при чтении. Устанавливается с помощью PRAGMA.

Пример 
conn = sqlite3.connect('example_wal.db')
cursor = conn.cursor()
cursor.execute('PRAGMA journal_mode=WAL')
print(cursor.fetchone())  # ('wal',)
# Далее обычные операции

Этот режим полезен для приложений, где много одновременных читателей.

Обработка внешних ключей

По умолчанию ограничения внешних ключей отключены. Включение:

Пример 
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('PRAGMA foreign_keys = ON')
# Теперь внешние ключи будут проверяться

Без этого каскадные операции (ON DELETE CASCADE) могут не срабатывать.

Копирование базы данных в память

Можно перенести содержимое файловой БД в оперативную для ускорения.

Пример 
import sqlite3

disk_conn = sqlite3.connect('example.db')
disk_conn.backup(sqlite3.connect(':memory:'))
# Теперь memory_conn содержит ту же схему и данные

Метод backup() доступен с Python 3.7.

Использование курсора итератора

Курсор поддерживает итерацию, что экономит память при больших результатах.

Пример 
cursor = conn.execute('SELECT * FROM large_table')
for row in cursor:
    # обработка строки без загрузки всего результата
    pass

Это предпочтительно для выборок, которые не помещаются в памяти.

Библиотека sqlite3 в Python - comments

En
библиотека sqlite3 python (python)