SQLite3 и Python: настройка, запросы, ошибки и примеры использования

Расширенные примеры работы с SQLite3 в Python

Пример 1: Использование executemany для массовой вставки данных

Метод executemany позволяет вставить несколько строк за один вызов, что значительно ускоряет операцию.

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    data = [('Eve', 28), ('Frank', 35), ('Grace', 22)]
    cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)
    # После выхода из with commit автоматический

# В таблицу users добавлены три записи

Пример 2: Выполнение скрипта SQL из файла

Если у вас есть файл с SQL-командами, его можно выполнить с помощью executescript.

# prepare.sql:
# CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
# INSERT INTO products (name, price) VALUES ('Laptop', 999.99);

with sqlite3.connect('shop.db') as conn:
    with open('prepare.sql', 'r', encoding='utf-8') as f:
        sql = f.read()
    conn.executescript(sql)
    cursor = conn.execute("SELECT * FROM products")
    print(cursor.fetchall())

Пример 3: Использование пользовательских функций в SQLite

Можно зарегистрировать Python-функцию и вызывать её внутри SQL.

import sqlite3
import math

def circle_area(radius):
    return math.pi * radius * radius

with sqlite3.connect(':memory:') as conn:
    conn.create_function("circle_area", 1, circle_area)
    cursor = conn.execute("SELECT circle_area(3.0)")
    print(cursor.fetchone()[0])  # 28.274333882308138

28.274333882308138

Пример 4: Работа с BLOB-данными (изображения, файлы)

Бинарные данные можно хранить в столбце типа BLOB, передавая объект bytes.

import sqlite3

with sqlite3.connect('files.db') as conn:
    conn.execute('''CREATE TABLE IF NOT EXISTS images
                    (id INTEGER PRIMARY KEY, data BLOB)''')
    with open('photo.jpg', 'rb') as f:
        blob = f.read()
    conn.execute("INSERT INTO images (data) VALUES (?)", (blob,))
    cursor = conn.execute("SELECT data FROM images WHERE id=1")
    row = cursor.fetchone()
    if row:
        with open('output.jpg', 'wb') as out:
            out.write(row[0])

Файл output.jpg создан, содержит копию photo.jpg

Пример 5: Использование агрегатных функций и сортировки

Пример с группировкой и сортировкой по возрасту.

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('''SELECT name, age FROM users
                      ORDER BY age DESC
                      LIMIT 3''')
    for name, age in cursor:
        print(f"{name}: {age}")

Frank: 35
Alice: 30
Eve: 28

Пример 6: Обработка ошибок с откатом транзакции вручную

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
try:
    cursor.execute("BEGIN")
    cursor.execute("INSERT INTO users (name) VALUES ('Ivan')")
    # Предположим, следующая команда вызовет IntegrityError, так как id уже существует?
    cursor.execute("INSERT INTO users (id, name) VALUES (1, 'Dup')")  # ошибка, если id=1 уже есть
    conn.commit()
except sqlite3.DatabaseError as e:
    print("Ошибка:", e)
    conn.rollback()
finally:
    conn.close()

Если записи с id=1 нет, то вставка пройдет. При ошибке транзакция откатывается, первая вставка тоже отменяется.

Пример 7: Подключение к базе данных в памяти с множеством таблиц

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary REAL)")
cursor.execute("CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT)")
cursor.executemany("INSERT INTO employees (name, salary) VALUES (?, ?)",
                   [('John', 50000), ('Jane', 60000)])
cursor.executemany("INSERT INTO departments (name) VALUES (?)",
                   [('HR',), ('IT',)])

# INNER JOIN
cursor.execute('''SELECT e.name, d.name
                  FROM employees e
                  CROSS JOIN departments d''')
for row in cursor.fetchall():
    print(row)
conn.close()

('John', 'HR')
('John', 'IT')
('Jane', 'HR')
('Jane', 'IT')

Пример 8: Работа с NULL-значениями и IS NULL

import sqlite3

with sqlite3.connect(':memory:') as conn:
    conn.execute("CREATE TABLE test (value INTEGER)")
    conn.execute("INSERT INTO test VALUES (10)")
    conn.execute("INSERT INTO test VALUES (NULL)")
    cursor = conn.execute("SELECT * FROM test WHERE value IS NULL")
    print(cursor.fetchall())  # [(None,)]

[(None,)]

Пример 9: Использование встроенной функции sqlite3 для дампа базы данных

import sqlite3

with sqlite3.connect('example.db') as conn:
    # Получить SQL-скрипт для восстановления БД
    for line in conn.iterdump():
        print(line)

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS users (...);
...
COMMIT;

Пример 10: Параллельное чтение из нескольких соединений (WAL режим)

import sqlite3

# Включение WAL (Write-Ahead Log)
conn = sqlite3.connect('example.db')
conn.execute("PRAGMA journal_mode=WAL;")
conn.close()

# Теперь несколько соединений могут одновременно читать
conn1 = sqlite3.connect('example.db')
conn2 = sqlite3.connect('example.db')
c1 = conn1.execute("SELECT * FROM users")
c2 = conn2.execute("SELECT * FROM users")
print(c1.fetchone())
print(c2.fetchone())
conn1.close()
conn2.close()

(1, 'Alice', 30)
(1, 'Alice', 30)

WAL позволяет конкурентное чтение без блокировок.