как работает SQLite в питоне?

Работа с SQLite в Python: полное руководство для начинающих и опытных разработчиков

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

В связке с Python, благодаря стандартному модулю sqlite3, можно очень быстро создавать, изменять и управлять базами данных прямо из кода. В этой статье вы узнаете, как подключаться к SQLite, выполнять операции вставки данных (insert), обновления (update) и многое другое.

Что такое SQLite и почему его выбирают

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

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

Преимущества SQLite

• Не требует установки сервера
• Высокая скорость работы
• Простота в использовании
• Отличная интеграция с Python через модуль sqlite3
• Кроссплатформенность и портативность
• Поддержка транзакций ACID
• Минимальное потребление ресурсов

Ограничения SQLite

• Не поддерживает одновременные записи от нескольких пользователей
• Ограниченная производительность при работе с большими объёмами данных
• Отсутствие встроенной репликации
• Ограниченные возможности для сложных многопользовательских приложений

Подключение к базе данных SQLite в Python

Основное подключение

import sqlite3

# Подключение к базе данных (если файла нет — он будет создан)
conn = sqlite3.connect('my_database.db')

# Создание курсора для выполнения SQL-запросов
cursor = conn.cursor()

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

Для временного хранения данных или тестирования можно использовать базу данных в памяти:

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

Настройка параметров подключения

conn = sqlite3.connect('my_database.db', timeout=10)
conn.execute('PRAGMA foreign_keys = ON')  # Включение внешних ключей

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

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

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit()

Создание таблицы с дополнительными ограничениями

cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        price REAL CHECK(price > 0),
        category TEXT DEFAULT 'uncategorized',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

Создание индексов для оптимизации

cursor.execute('''
    CREATE INDEX IF NOT EXISTS idx_users_name ON users(name)
''')
conn.commit()

Вставка данных в таблицу — python sqlite3 insert

Вставка одной записи

Один из самых частых запросов — как вставлять данные в таблицу SQLite:

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Иван', 30))
conn.commit()

Использование знаков вопроса защищает от SQL-инъекций и является рекомендуемым подходом.

Массовая вставка данных

users = [('Алексей', 25), ('Мария', 28), ('Сергей', 35)]
cursor.executemany('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', users)
conn.commit()

Вставка с игнорированием дубликатов

cursor.execute('''
    INSERT OR IGNORE INTO users (name, age) VALUES (?, ?)
''', ('Иван', 30))
conn.commit()

Вставка с обновлением при конфликте

cursor.execute('''
    INSERT OR REPLACE INTO users (name, age) VALUES (?, ?)
''', ('Иван', 32))
conn.commit()

Чтение данных из таблицы

Выборка всех данных

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

Выборка с условиями

cursor.execute('SELECT * FROM users WHERE age > ?', (25,))
rows = cursor.fetchall()
for row in rows:
    print(f"Имя: {row[1]}, Возраст: {row[2]}")

Постраничная выборка

cursor.execute('SELECT * FROM users LIMIT ? OFFSET ?', (10, 0))
rows = cursor.fetchall()

Сортировка результатов

cursor.execute('SELECT * FROM users ORDER BY age DESC')
rows = cursor.fetchall()

Обновление данных — python sqlite update / update sqlite3 python

Обновление одной записи

Чтобы изменить данные, используйте оператор UPDATE:

cursor.execute('''
    UPDATE users SET age = ? WHERE name = ?
''', (32, 'Иван'))
conn.commit()

Обновление нескольких записей

cursor.execute('''
    UPDATE users SET age = age + 1 WHERE age < ?
''', (30,))
conn.commit()

Обновление с проверкой существования записи

cursor.execute('''
    UPDATE users SET age = ? WHERE name = ? AND EXISTS (
        SELECT 1 FROM users WHERE name = ?
    )
''', (35, 'Алексей', 'Алексей'))
conn.commit()

Удаление данных из таблицы

Удаление конкретной записи

cursor.execute('''
    DELETE FROM users WHERE name = ?
''', ('Сергей',))
conn.commit()

Удаление по условию

cursor.execute('''
    DELETE FROM users WHERE age < ?
''', (18,))
conn.commit()

Очистка всей таблицы

cursor.execute('DELETE FROM users')
conn.commit()

Обработка ошибок при работе с базой данных

Правильное закрытие соединений

conn.close()

Использование конструкций try-except

try:
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())
except sqlite3.Error as e:
    print(f"Ошибка: {e}")
except sqlite3.IntegrityError as e:
    print(f"Ошибка целостности данных: {e}")

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

with sqlite3.connect('my_database.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())

Контекстный менеджер автоматически закроет соединение, даже если возникнет ошибка.

Работа с транзакциями

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

try:
    conn.execute('BEGIN TRANSACTION')
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Тест', 25))
    cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Тест'))
    conn.commit()
except sqlite3.Error:
    conn.rollback()
    print("Транзакция отменена")

Использование savepoint

conn.execute('SAVEPOINT sp1')
try:
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Тест2', 30))
    conn.execute('RELEASE SAVEPOINT sp1')
except sqlite3.Error:
    conn.execute('ROLLBACK TO SAVEPOINT sp1')

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

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

stmt = cursor.execute('SELECT * FROM users WHERE age > ?')
for age_limit in [20, 30, 40]:
    cursor.execute(stmt, (age_limit,))
    print(cursor.fetchall())

Настройка прагм для увеличения производительности

cursor.execute('PRAGMA synchronous = OFF')
cursor.execute('PRAGMA journal_mode = MEMORY')
cursor.execute('PRAGMA cache_size = 10000')

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

Можно ли использовать SQLite для крупных проектов

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

Как проверить существование таблицы перед её созданием

Используйте CREATE TABLE IF NOT EXISTS, как показано выше, или проверьте через системную таблицу:

cursor.execute('''
    SELECT name FROM sqlite_master WHERE type='table' AND name='users'
''')
if cursor.fetchone():
    print("Таблица существует")

Как избежать SQL-инъекций

Используйте параметризованные запросы с знаками вопроса вместо подстановки строк через f-строки или конкатенацию.

Как получить количество записей в таблице

cursor.execute('SELECT COUNT(*) FROM users')
count = cursor.fetchone()[0]
print(f'Количество записей: {count}')

Как получить ID последней вставленной записи

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Екатерина', 27))
conn.commit()
last_id = cursor.lastrowid
print(f"ID новой записи: {last_id}")

Как работать с NULL значениями

cursor.execute('SELECT * FROM users WHERE name IS NULL')
null_users = cursor.fetchall()

cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', (None, 25))

Заключение

Работа с базами данных в Python с помощью SQLite проста и удобна. Вы узнали, как выполнять основные операции — вставку (insert), обновление (update), удаление и выборку данных. Использование стандартного модуля sqlite3 позволяет быстро организовать хранение данных без сложной настройки серверов.

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

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