SQLite3 – встроенная работа с SQLite

Введение в SQLite и модуль sqlite3

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

Модуль sqlite3 превращает создание и использование баз данных в процесс, аналогичный работе с обычными файлами. Это решение особенно ценно для прототипирования, встраиваемых систем, настольных приложений и образовательных целей.

Установка и совместимость

Модуль sqlite3 включен в стандартную библиотеку Python начиная с версии 2.5 и доступен без дополнительной установки. Он обеспечивает полную совместимость с операционными системами Windows, macOS и Linux, предоставляя интерфейс для работы с SQLite версии 3.

import sqlite3

Подключение к базе данных и создание соединения

Создание файловой базы данных

conn = sqlite3.connect('my_database.db')

При выполнении этой команды база данных будет создана автоматически, если указанный файл не существует.

Создание базы данных в памяти

conn = sqlite3.connect(':memory:')

Этот вариант особенно удобен для тестирования и временных операций.

Дополнительные параметры подключения

conn = sqlite3.connect('database.db', 
                      check_same_thread=False,
                      detect_types=sqlite3.PARSE_DECLTYPES)

Создание таблиц и работа со схемой данных

Создание таблиц

cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

Типы данных в SQLite

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

• INTEGER - целые числа
• REAL - числа с плавающей точкой
• TEXT - текстовые строки
• BLOB - бинарные данные
• NULL - отсутствие значения

SQLite использует динамическую типизацию, что обеспечивает гибкость, но требует внимательности при работе с данными.

Операции CRUD (Create, Read, Update, Delete)

Вставка данных

# Вставка одной записи
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ("Иван Петров", 30, "ivan@example.com"))

# Вставка нескольких записей
users_data = [
    ("Мария Сидорова", 25, "maria@example.com"),
    ("Петр Иванов", 35, "petr@example.com"),
    ("Анна Козлова", 28, "anna@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)
conn.commit()

Выборка данных

# Выборка всех записей
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()

# Выборка с условием
cursor.execute("SELECT name, age FROM users WHERE age > ?", (25,))
adult_users = cursor.fetchall()

# Выборка с сортировкой
cursor.execute("SELECT * FROM users ORDER BY age DESC")
sorted_users = cursor.fetchall()

Обновление данных

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Иван Петров"))
conn.commit()

# Получение количества измененных строк
print(f"Обновлено строк: {cursor.rowcount}")

Удаление данных

cursor.execute("DELETE FROM users WHERE name = ?", ("Иван Петров",))
conn.commit()

Методы чтения результатов запросов

Основные методы извлечения данных

• fetchone() - возвращает одну строку результата
• fetchall() - возвращает все строки в виде списка
• fetchmany(size) - возвращает указанное количество строк

cursor.execute("SELECT * FROM users")

# Получение одной строки
user = cursor.fetchone()
if user:
    print(f"ID: {user[0]}, Имя: {user[1]}, Возраст: {user[2]}")

# Получение нескольких строк
users_batch = cursor.fetchmany(5)
for user in users_batch:
    print(user)

Работа с результатами как с словарем

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():
    print(f"Имя: {row['name']}, Возраст: {row['age']}")

Контекстные менеджеры и управление транзакциями

Использование контекстных менеджеров

with sqlite3.connect('my_database.db') as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Тест", 25))
    # Автоматический commit при успешном выполнении
    # Автоматический rollback при ошибке

Ручное управление транзакциями

conn = sqlite3.connect('database.db')
try:
    cursor = conn.cursor()
    cursor.execute("BEGIN TRANSACTION")
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("User1", 25))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("User2", 30))
    conn.commit()
except sqlite3.Error as e:
    conn.rollback()
    print(f"Ошибка: {e}")
finally:
    conn.close()

Создание индексов и ограничений

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

# Создание обычного индекса
cursor.execute("CREATE INDEX idx_users_age ON users(age)")

# Создание уникального индекса
cursor.execute("CREATE UNIQUE INDEX idx_users_email ON users(email)")

# Создание составного индекса
cursor.execute("CREATE INDEX idx_users_name_age ON users(name, age)")

Добавление ограничений

cursor.execute('''
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL CHECK(price > 0),
        category_id INTEGER,
        FOREIGN KEY (category_id) REFERENCES categories(id)
    )
''')

Работа с датами и временем

Сохранение дат

from datetime import datetime

now = datetime.now()
cursor.execute("INSERT INTO logs (timestamp, message) VALUES (?, ?)", 
               (now, "Тестовое сообщение"))

Автоматическое преобразование типов

conn = sqlite3.connect("database.db", detect_types=sqlite3.PARSE_DECLTYPES)

# Теперь даты будут автоматически преобразовываться в объекты datetime
cursor.execute("SELECT timestamp FROM logs")
for row in cursor.fetchall():
    print(type(row[0]))  # <class 'datetime.datetime'>

Импорт и экспорт данных

Экспорт данных в CSV

import csv

cursor.execute("SELECT * FROM users")
with open('users.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    # Запись заголовков
    writer.writerow([description[0] for description in cursor.description])
    # Запись данных
    writer.writerows(cursor.fetchall())

Импорт данных из CSV

with open('users.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
                      (row['name'], row['age'], row['email']))
conn.commit()

Создание резервной копии базы данных

def backup_database(source_db, backup_db):
    source = sqlite3.connect(source_db)
    backup = sqlite3.connect(backup_db)
    source.backup(backup)
    source.close()
    backup.close()

Интеграция с Pandas и другими библиотеками

Чтение данных в DataFrame

import pandas as pd

df = pd.read_sql("SELECT * FROM users WHERE age > 25", conn)
print(df.head())

# Чтение с параметрами
df = pd.read_sql("SELECT * FROM users WHERE age > ?", conn, params=(25,))

Сохранение DataFrame в базу данных

df.to_sql("new_users", conn, index=False, if_exists="replace")

# Добавление к существующей таблице
df.to_sql("users", conn, index=False, if_exists="append")

Тестирование с SQLite

Создание тестовой базы данных

import unittest

class TestDatabase(unittest.TestCase):
    def setUp(self):
        self.conn = sqlite3.connect(":memory:")
        self.cursor = self.conn.cursor()
        self.cursor.execute('''
            CREATE TABLE test_users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL
            )
        ''')
    
    def tearDown(self):
        self.conn.close()
    
    def test_insert_user(self):
        self.cursor.execute("INSERT INTO test_users (name) VALUES (?)", ("Тест",))
        self.conn.commit()
        
        self.cursor.execute("SELECT COUNT(*) FROM test_users")
        count = self.cursor.fetchone()[0]
        self.assertEqual(count, 1)

Расширенные возможности sqlite3

Пользовательские функции

def reverse_string(s):
    return s[::-1]

conn.create_function("reverse", 1, reverse_string)
cursor.execute("SELECT reverse(name) FROM users")

Пользовательские агрегатные функции

class Average:
    def __init__(self):
        self.count = 0
        self.sum = 0
    
    def step(self, value):
        self.count += 1
        self.sum += value
    
    def finalize(self):
        return self.sum / self.count if self.count > 0 else 0

conn.create_aggregate("avg", 1, Average)

Оптимизация производительности

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

cursor.execute("PRAGMA synchronous = OFF")
cursor.execute("PRAGMA journal_mode = WAL")
cursor.execute("PRAGMA cache_size = 10000")
cursor.execute("PRAGMA temp_store = MEMORY")

Пакетная обработка данных

# Более эффективная вставка больших объемов данных
cursor.execute("BEGIN TRANSACTION")
for i in range(10000):
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", 
                  (f"User{i}", i % 100))
cursor.execute("COMMIT")

Таблица методов и функций модуля sqlite3

Безопасность и лучшие практики

Защита от SQL-инъекций

# Правильно - используйте параметризированные запросы
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

# Неправильно - избегайте форматирования строк
# cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

Управление ресурсами

# Всегда закрывайте соединения
try:
    conn = sqlite3.connect('database.db')
    # работа с базой данных
finally:
    conn.close()

# Или используйте контекстный менеджер
with sqlite3.connect('database.db') as conn:
    # работа с базой данных

Обработка ошибок

try:
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Иван", 30))
    conn.commit()
except sqlite3.IntegrityError:
    print("Ошибка целостности данных")
except sqlite3.OperationalError:
    print("Операционная ошибка")
except sqlite3.Error as e:
    print(f"Ошибка SQLite: {e}")

Сравнение с другими СУБД

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

Система логирования

def create_log_table():
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            level TEXT NOT NULL,
            message TEXT NOT NULL,
            module TEXT
        )
    ''')
    conn.commit()

def log_message(level, message, module=None):
    cursor.execute("INSERT INTO logs (level, message, module) VALUES (?, ?, ?)",
                  (level, message, module))
    conn.commit()

Кэширование данных

class SQLiteCache:
    def __init__(self, db_path):
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS cache (
                key TEXT PRIMARY KEY,
                value TEXT,
                expires DATETIME
            )
        ''')
    
    def set(self, key, value, ttl=3600):
        expires = datetime.now() + timedelta(seconds=ttl)
        self.cursor.execute("INSERT OR REPLACE INTO cache VALUES (?, ?, ?)",
                          (key, value, expires))
        self.conn.commit()
    
    def get(self, key):
        self.cursor.execute("SELECT value FROM cache WHERE key = ? AND expires > ?",
                          (key, datetime.now()))
        result = self.cursor.fetchone()
        return result[0] if result else None

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

Что такое sqlite3 в Python?

sqlite3 - это встроенный модуль Python для работы с SQLite базами данных. Он предоставляет интерфейс для создания, чтения, обновления и удаления данных в SQLite базах данных без необходимости установки дополнительного программного обеспечения.

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

Нет, модуль sqlite3 входит в стандартную библиотеку Python начиная с версии 2.5. SQLite движок также встроен в Python, поэтому дополнительная установка не требуется.

Подходит ли SQLite для продакшена?

SQLite отлично подходит для небольших и средних приложений, настольных программ, встроенных систем и мобильных приложений. Однако для крупных веб-приложений с высокой нагрузкой рекомендуется использовать серверные СУБД как PostgreSQL или MySQL.

Как защититься от SQL-инъекций?

Всегда используйте параметризированные запросы с заполнителями вместо форматирования строк. Используйте символ ? для подстановки значений и передавайте параметры отдельно.

Можно ли использовать SQLite с многопоточностью?

Да, но с ограничениями. SQLite поддерживает многопоточность, но одновременно может выполняться только одна операция записи. Для улучшения производительности используйте WAL-режим.

Как работать с SQLite и Pandas?

Pandas предоставляет методы read_sql() для чтения данных из базы и to_sql() для сохранения DataFrame в базу данных. Это позволяет легко интегрировать SQLite с анализом данных.

Как сделать резервную копию SQLite базы данных?

Используйте метод backup() или просто скопируйте файл базы данных. Для программной резервной копии можно использовать метод iterdump().

Какие ограничения у SQLite?

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

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

Используйте индексы для часто запрашиваемых полей, применяйте PRAGMA директивы для настройки производительности, используйте транзакции для пакетных операций, включите WAL-режим для многопоточных приложений.

Можно ли использовать SQLite для веб-приложений?

Да, SQLite подходит для небольших веб-приложений и прототипов. Однако для приложений с высокой нагрузкой и множественными одновременными пользователями рекомендуется переход на серверную СУБД.

Заключение

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

SQLite сочетает в себе простоту использования, надежность и высокую производительность, что делает его идеальным выбором для широкого спектра задач - от обучения программированию до создания коммерческих приложений. Понимание возможностей и ограничений SQLite поможет принять правильное решение о выборе базы данных для конкретного проекта.