sqlite3_cheatsheet.md

🐘 SQLite3 Backend Cheat Sheet

1. Установка и Настройка (Ubuntu Server)

Установка CLI инструментов и библиотек:

sudo apt update
sudo apt install sqlite3 libsqlite3-dev

Проверка версии:

sqlite3 --version

---

2. Работа в терминале (CLI)

Быстрая отладка и проверка данных прямо на сервере.

Подключение:

sqlite3 my_database.db

Полезные dot-команды (внутри оболочки sqlite):

.help                  -- Помощь
.tables                -- Список таблиц
.schema table_name     -- Показать CREATE statement таблицы
.mode column           -- Читабельный табличный вывод
.headers on            -- Показать заголовки столбцов
.quit                  -- Выход (или Ctrl+D)

Дамп и Восстановление (Backup):

# Создать дамп (SQL файл)
sqlite3 my_database.db .dump > backup.sql

# Восстановить из дампа
sqlite3 new_database.db < backup.sql

---

3. Python3: Базовый паттерн

Минимальный рабочий пример (Vanilla Python).

import sqlite3

# 1. Подключение (файл создастся сам, если его нет)
conn = sqlite3.connect('server_db.sqlite')
cursor = conn.cursor()

# 2. Создание таблицы
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

# 3. Вставка (ВНИМАНИЕ: Используйте `?` для защиты от SQL Injection)
user_data = ('backend_dev', 'dev@server.com')
cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', user_data)

# 4. Фиксация изменений
conn.commit()

# 5. Чтение
cursor.execute('SELECT * FROM users WHERE username = ?', ('backend_dev',))
row = cursor.fetchone()
print(row)  # Вывод: (1, 'backend_dev', 'dev@server.com', '2023-10-27...')

# 6. Закрытие
conn.close()

---

4. Python3: Best Practices (Pro Level)

Контекстный менеджер (Context Manager)

Автоматический commit (при успехе) или rollback (при ошибке) + автозакрытие.

import sqlite3

db_path = 'server_db.sqlite'

try:
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        # Множественная вставка (Bulk Insert) — намного быстрее цикла!
        users = [('user1', 'm1@test.com'), ('user2', 'm2@test.com')]
        cursor.executemany('INSERT INTO users (username, email) VALUES (?, ?)', users)
        
        # Commit делается автоматически при выходе из блока
except sqlite3.Error as e:
    print(f"Ошибка БД: {e}")

Возврат результатов в виде словаря (dict)

По умолчанию возвращает кортежи (tuple). Для API удобнее словари.

def get_db_connection():
    conn = sqlite3.connect('server_db.sqlite')
    # Позволяет обращаться к полям по имени
    conn.row_factory = sqlite3.Row 
    return conn

with get_db_connection() as conn:
    row = conn.execute('SELECT * FROM users LIMIT 1').fetchone()
    
    # Теперь можно так:
    print(row['email']) 
    print(dict(row)) # {'id': 1, 'username': 'user1', ...}

---

5. Оптимизация производительности (Tuning)

SQLite по умолчанию настроен на совместимость, а не на скорость. Для сервера обязательно включите WAL.

Включение WAL (Write-Ahead Logging)

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

SQL:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL; -- Баланс между скоростью и сохранностью

Python:

conn = sqlite3.connect('server_db.sqlite')
conn.execute('PRAGMA journal_mode = WAL;')
conn.execute('PRAGMA synchronous = NORMAL;')

Индексы

Без индексов SELECT на больших данных "уложит" CPU.

CREATE INDEX idx_users_email ON users(email);

---

6. Шпаргалка типов данных (SQLite vs Python)

SQLite Storage Class	Python Type
NULL	None
INTEGER	int
REAL	float
TEXT	str
BLOB	bytes

Примечание: SQLite не имеет типа DATETIME. Даты хранятся как строки (TEXT) или timestamp (INTEGER/REAL).

---

7. Быстрые команды для Cron / Bash скриптов

Очистка старых логов (пример обслуживания базы):

#!/bin/bash
DB_PATH="/var/www/app/data.db"
sqlite3 $DB_PATH "DELETE FROM logs WHERE created_at < date('now', '-30 days');"
sqlite3 $DB_PATH "VACUUM;" # Сжатие файла базы после удаления данных