🔌 Python + SQLite: скрытые возможности (возврат изменённых строк, оконные функции)
Все пишут SELECT и INSERT, но SQLite — это полноценная СУБД с фичами из больших баз. Используй RETURNING, UPSERT и оконные функции, чтобы логика работала на уровне данных, а не в коде.
⚡️ UPSERT: вставь или обнови одной командой
Классика: нужно обновить запись, если она есть, иначе создать. Раньше требовалось два запроса. Теперь — один:
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
login_count INTEGER)''')
# Первый вызов — вставит новую запись
conn.execute('''INSERT INTO users (email, login_count)
VALUES ('
[email protected]', 1)
ON CONFLICT(email) DO UPDATE
SET login_count = login_count + 1''')
# Второй вызов — увеличит счётчик
conn.execute('''INSERT INTO users (email, login_count)
VALUES ('
[email protected]', 1)
ON CONFLICT(email) DO UPDATE
SET login_count = users.login_count + 1''')
result = conn.execute('SELECT * FROM users').fetchone()
print(result) # (1, '
[email protected]', 2)
➡️ ON CONFLICT DO UPDATE — магия UPSERT. Ключевое поле должно иметь ограничение UNIQUE или PRIMARY KEY.
✅ RETURNING: возвращай изменённые строки сразу
Раньше чтобы получить ID новой записи, делали отдельный SELECT. Теперь всё сразу:
# Возвращаем ID и данные новой записи
cursor = conn.execute('''INSERT INTO users (email, login_count)
VALUES ('
[email protected]', 1)
RETURNING id, email''')
new_user = cursor.fetchone()
print(f'Новый ID: {new_user[0]}, email: {new_user[1]}')
# Работает и с UPDATE/DELETE
cursor = conn.execute('''UPDATE users
SET login_count = login_count * 2
WHERE email = '
[email protected]'
RETURNING email, login_count''')
updated = cursor.fetchone()
print(f'Обновлено: {updated[0]}, новые логины: {updated[1]}')
➡️ RETURNING возвращает строки, затронутые INSERT, UPDATE или DELETE. Идеально для логирования и цепочек операций.
🧪 Оконные функции (OVER): аналитика без костылей
Нужно пронумеровать строки, посчитать скользящее среднее или ранжировать — делай одним запросом:
# Создадим таблицу с продажами
conn.execute('''CREATE TABLE sales (
id INTEGER PRIMARY KEY,
product TEXT,
amount REAL,
sale_date DATE
)''')
# Заполняем тестовыми данными
sales_data = [
('Ноутбук', 1000, '2024-01-15'),
('Ноутбук', 1200, '2024-01-20'),
('Мышь', 50, '2024-01-10'),
('Мышь', 45, '2024-01-18'),
('Клавиатура', 80, '2024-01-12'),
]
conn.executemany('INSERT INTO sales (product, amount, sale_date) VALUES (?, ?, ?)', sales_data)
# Ранжируем товары по убыванию суммы продаж внутри каждой категории
cursor = conn.execute('''
SELECT
product,
amount,
sale_date,
ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount DESC) as rank_in_product,
SUM(amount) OVER (PARTITION BY product) as total_per_product,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg
FROM sales
ORDER BY product, amount DESC
''')
for row in cursor.fetchall():
print(row)
➡️ OVER (PARTITION BY ... ORDER BY ...) создаёт "окно" для вычислений. Не путать с GROUP BY — здесь строки не сворачиваются.
🗣 Запомни: Современный SQLite — это не просто хранилище. Используй UPSERT для атомарных обновлений, RETURNING чтобы не делать лишних запросов, а оконные функции — для аналитики прямо в БД.
Понравились скрытые фичи SQLite? Жмите 🔥
