Python sqlite

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

Вы научитесь создавать базу на диске и в памяти, описывать таблицы и связывать их. В добавленном разделе разберем то, без чего код с базой нельзя считать рабочим: параметры вместо склейки строк, транзакции через with, доступ к колонкам по имени и включение внешних ключей. Примеры проверены на Python 3.12 и 3.13.

В этой большой статьей, мы с вами сегодня познакомимся с одним из самых популярных встроенных модулей, для работы с базой данных python sqlite. SQLite предназначен для хранения информации, и для его удобного чтения. На этом уроке, мы разберем, все основные моменты по работе с ним.

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

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

Как и в примере с csv файлами, при попытке подключения к несуществующей базе данных, она автоматически создастся. И так, что бы начать работу с базой данных, нам ее надо сначала создать, как обычно, рассмотрим сначала код, затем разберем как это работает.

import sqlite3
from sqlite3 import Error
def create_connection(db_file):
    """ Создание соединения с БД """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()
if __name__ == '__main__':
    create_connection(r"C:\db\pythonsqlite.db")

1. Импортируем непосредственно сам модуль SQLite
2. Создаем функцию подключения create_connection, в качестве аргумента передаем нашу БД
3. Вызываем функцию connect() модуля SQLite
4. Используем блок try...except, что бы в случае ошибке вывести ее на экран, в случае успеха, мы увидим версию SQLite
5. Затем после всех наших операций, незабываем закрыть соединение

После запуска, вы увидите, что по указанному пути, у вас появился файл pythonsqlite.db.

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

Базу данных можно создать и внутри вашей оперативной памяти. Для этого вам достаточно передать имя файла как :memory: методу connect(), после чего, у вас создастся новая БД внутри вашего ОЗУ. Ниже приведен пример кода.

import sqlite3
from sqlite3 import Error
def create_connection():
    """ Создание БД внутри ОЗУ
    """
    conn = None;
    try:
        conn = sqlite3.connect(':memory:')
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()
if __name__ == '__main__':
    create_connection()

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

Для создания новой таблицы в SQLite из программы Python, нам необходимо выполнить следующие действия:

1. Создаем объект Connection используя метод connect() модуля sqlite3
2. Создаем объект cursor, вызвав метод cursor объекта Connection
3. Передаем инструкцию CREATE TABLE методу execute() объекта Cursor

И так, теперь для демонстрации, попробуем создать две таблицы, проекты и задачи, как показано на рисунке ниже.

Сначала приведу полный листинг кода, а потом поэтапно разберем, как это все работает.

import sqlite3
from sqlite3 import Error
def create_connection(db_file):
   
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return conn
def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
def main():
    database = r"C:\sqlite\db\pythonsqlite.db"
    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """
    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    project_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (project_id) REFERENCES projects (id)
                                );"""
    # create a database connection
    conn = create_connection(database)
    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_projects_table)
        # create tasks table
        create_table(conn, sql_create_tasks_table)
    else:
        print("Error! cannot create the database connection.")
if __name__ == '__main__':
    main()

1. Создаем функцию create_connection() для соединения с базой данных, которая возвращает объект conn. Объект conn представляет базу SQLite.
2. Создаем функцию create_table(), которая принимает объект conn, и инструкцию SQL create_table_sql. Внутри нашей функции вызываем метод execute() объекта Cursor(), что бы выполнить запрос в БД.
3. Создаем функцию main(), для создания таблиц projects и tasks
4. Запускаем функцию main()

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

Безопасные запросы и транзакции в sqlite3

В этом разделе: параметры вместо f-строк, полный пример с транзакцией, типичные ошибки.

Параметры вместо f-строк

Никогда не подставляйте значения в SQL через f-строку или format(). Кавычка в данных сломает запрос, а специально подобранная строка выполнит чужой SQL. Передавайте значения отдельно через плейсхолдеры ?:

title = "Отчет за 'октябрь'"

# опасно: ошибка синтаксиса или SQL-инъекция
# conn.execute(f"INSERT INTO tasks (title) VALUES ('{title}')")

# правильно
conn.execute("INSERT INTO tasks (title) VALUES (?)", (title,))

Обратите внимание на запятую в (title,): без нее это не кортеж, а просто строка в скобках, и sqlite3 сообщит о неверном числе параметров.

Полный пример с транзакцией

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("tasks.db")) as conn:
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS tasks (
            id    INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            done  INTEGER NOT NULL DEFAULT 0
        )""")

    with conn:   # COMMIT при успехе, ROLLBACK при исключении
        conn.executemany(
            "INSERT INTO tasks (title) VALUES (?)",
            [("Купить молоко",), ("Сдать отчет",)],
        )

    for row in conn.execute("SELECT id, title FROM tasks WHERE done = ?", (0,)):
        print(row["id"], row["title"])

• closing() закрывает соединение в конце блока. Сам with conn соединение не закрывает, он только управляет транзакцией.
• row_factory = sqlite3.Row позволяет обращаться к колонкам по имени, а не по номеру.
• PRAGMA foreign_keys = ON нужно выполнять для каждого нового соединения, иначе связи между таблицами вроде projects и tasks не проверяются.
• executemany() вставляет много строк одним вызовом и работает заметно быстрее цикла с execute().

Если вставок много, держите их в одной транзакции: каждая отдельная фиксация заставляет SQLite записывать данные на диск, и десять тысяч строк с commit() после каждой могут вставляться минуты вместо долей секунды. С Python 3.12 у connect() появился параметр autocommit. Значение False включает поведение по стандарту PEP 249: транзакция открывается автоматически и всегда требует явного commit(). Старые программы продолжают работать без изменений.

Типичные ошибки

Выборка и удаление записей подробно разобраны в статье SQLite Python: выборка и удаление.

Частые вопросы

Несколько вопросов, которые часто задают начинающие, и ответы на них.

Нужно ли устанавливать sqlite3 через pip?

Нет, модуль входит в стандартную библиотеку Python и подключается строкой import sqlite3. Узнать версию самого движка SQLite можно через sqlite3.sqlite_version.

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

После execute() с INSERT прочитайте атрибут cursor.lastrowid. Начиная с SQLite 3.35 можно также добавить в запрос RETURNING id и получить значение через fetchone().

Подходит ли SQLite для веб-приложения?

Для небольших сайтов и внутренних сервисов с умеренной записью вполне. Включите режим PRAGMA journal_mode = WAL, чтобы чтение не блокировалось записью. При большом числе одновременных записей лучше перейти на PostgreSQL.

Как хранить дату и время в SQLite?

Отдельного типа для дат в SQLite нет. Храните строку ISO 8601 из datetime.isoformat() или число секунд Unix. Строки в формате ISO правильно сортируются и сравниваются в запросах.

Чем fetchone() отличается от fetchall()?

fetchone() возвращает одну следующую строку или None, fetchall() загружает в память все строки сразу. Для больших выборок перебирайте курсор в цикле for, тогда строки читаются по мере надобности.