Начало работы с SQLite в Python — создание таблицы, ошибки и бекапы

Цель этого руководства — продемонстрировать принципы разработки приложений на Python с использованием базы данных SQLite.

Подключение к SQLite в Python

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

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

Для подключения к SQLite нужно выполнить следующие шаги

Использовать метод connect() из модуля sqlite3 и передать в качестве аргумента название базы данных.
Создать объект cursor с помощью объекта соединения, который вернул прошлый метод для выполнения SQLite-запросов из Python.
Закрыть объект cursor после завершения работы.
Перехватить исключение базы данных, если в процессе подключения произошла ошибка.

Следующая программа создает файл базы данных sqlite_python.db и выводит подробности о версии SQLite.

Копировать



import sqlite3



try:

    sqlite_connection = sqlite3.connect('sqlite_python.db')

    cursor = sqlite_connection.cursor()

    print("База данных создана и успешно подключена к SQLite")



    sqlite_select_query = "select sqlite_version();"

    cursor.execute(sqlite_select_query)

    record = cursor.fetchall()

    print("Версия базы данных SQLite: ", record)

    cursor.close()



except sqlite3.Error as error:

    print("Ошибка при подключении к sqlite", error)

finally:

    if (sqlite_connection):

        sqlite_connection.close()

        print("Соединение с SQLite закрыто")

После подключения должно появиться следующее сообщение.

База данных создана и успешно подключена к SQLite
Версия базы данных SQLite:  [('3.31.1',)]
Соединение с SQLite закрыто

Понимание SQLite-подключения в подробностях

import sqlite3

Эта строка импортирует в программу модуль sqlite3. С помощью классов и методов из этого модуля можно взаимодействовать с базой данных SQLite.

sqlite3.connect()

С помощью метода connect() выполняется подключение к базе данных. Этот метод возвращает объект подключения SQLite.
Объект connection не является потокобезопасным. Модуль sqlite3 не позволяет делиться подключением между потоками. Если попытаться сделать это, то можно получить исключение.
Метод connect() принимает разные аргументы. В этом примере передается название базы данных.

cursor=sqliteConnection.cursor()

С помощью объекта соединения создается объект cursor, который позволяет выполнять SQLite-запросы из Python.
Для одного соединения можно создать неограниченное количество cursor. Он также не является потокобезопасным. Модуль не позволяет делиться объектами cursor между потоками. Если это сделать, то будет ошибка.

После этого создается запрос для получения версии базы данных.

cursor.execute()

С помощью метода execute объекта cursor можно выполнить запрос в базу данных из Python. Он принимает SQLite-запрос в качестве параметра и возвращает resultSet — то есть, строки базы данных
Получить результат запроса из resultSet можно с помощью методов, например, fetchAll()
В этом примере SELECT version(); выполняется для получения версии базы данных SQLite.

Блок try-except-finally: весь код расположен в блоке try-except, что позволит перехватить исключения и ошибки базы данных, которые могут появиться в процессе.

С помощью класса sqlite3.Error можно обработать любую ошибку и исключение, которые могут появиться при работе с SQLite из Python.
Это позволит сделать приложение более отказоустойчивым. Класс sqlite3.Error позволит понять суть ошибки. Он возвращает сообщение и код ошибки.

cursor.close() и connection.close()

Хорошей практикой считается закрывать объекты connection и curosor после завершения работы, чтобы избежать проблем с базой данных.

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

В этом разделе разберемся, как создавать таблицы в базе данных SQLite с помощью Python и модуля sqlite3. Создание таблицы — это DDL-запрос, выполняемый из Python.

В этом примере создадим базу sqlitedb_developers в базе данных sqlite_python.db.

Шаги для создания таблицы в SQLite с помощью Python:

Соединиться с базой данных с помощью sqlite3.connect(). Речь об этом шла в первом разделе.
Подготовить запрос создания таблицы.
Выполнить запрос с помощью cursor.execute(query).
Закрыть соединение с базой и объектом cursor.

Копировать



import sqlite3



try:

    sqlite_connection = sqlite3.connect('sqlite_python.db')

    sqlite_create_table_query = '''CREATE TABLE sqlitedb_developers (

                                id INTEGER PRIMARY KEY,

                                name TEXT NOT NULL,

                                email text NOT NULL UNIQUE,

                                joining_date datetime,

                                salary REAL NOT NULL);'''



    cursor = sqlite_connection.cursor()

    print("База данных подключена к SQLite")

    cursor.execute(sqlite_create_table_query)

    sqlite_connection.commit()

    print("Таблица SQLite создана")



    cursor.close()



except sqlite3.Error as error:

    print("Ошибка при подключении к sqlite", error)

finally:

    if (sqlite_connection):

        sqlite_connection.close()

        print("Соединение с SQLite закрыто")

База данных подключена к SQLite
Таблица SQLite создана
Соединение с SQLite закрыто

Типы данных SQLite и соответствие типам Python

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

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

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

NULL — значение NULL
INTEGER — числовые значения. Целые числа хранятся в 1, 2, 3, 4, 6 и 8 байтах в зависимости от величины
REAL — числа с плавающей точкой, например, 3.14, число Пи
TEXT — текстовые значения. Могут храниться в кодировке UTF-8, UTF-16BE или UTF-16LE
BLOB — бинарные данные. Для хранения изображений и файлов

Следующие типы данных из Python без проблем конвертируются в SQLite. Для конвертации достаточно лишь запомнить эту таблицу.

Тип Python	Тип SQLite
`None`	NULL
`int`	INTEGER
`float`	REAL
`str`	TEXT
`bytes`	BLOB

Выполнение SQL запросов с помощью функции executescript

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

Дальше несколько распространенных сценариев использования SQL-скриптов

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

Выполнить скрипт из командной строки SQLite можно с помощью команды .read:

sqlite> .read sqlitescript.sql

Например, этот простой скрипт создает две таблицы.

CREATE TABLE fruits (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 price REAL NOT NULL
);

CREATE TABLE drinks (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 price REAL NOT NULL
);

Теперь посмотрим, как выполнить его из Python.

Копировать



import sqlite3



try:

    sqlite_connection = sqlite3.connect('sqlite_python.db')

    cursor = sqlite_connection.cursor()

    print("База данных подключена к SQLite")



    with open('sqlite_create_tables.sql', 'r') as sqlite_file:

        sql_script = sqlite_file.read()



    cursor.executescript(sql_script)

    print("Скрипт SQLite успешно выполнен")

    cursor.close()



except sqlite3.Error as error:

    print("Ошибка при подключении к sqlite", error)

finally:

    if (sqlite_connection):

        sqlite_connection.close()

        print("Соединение с SQLite закрыто")

Выполнение SQL запросов с помощью функции executescript

Таблицы SQLite создаются за счет выполнения скрипта из Python. Вывод:

База данных подключена к SQLite
Скрипт SQLite успешно выполнен
Соединение с SQLite закрыто

Примечание: после соединения с SQLite все содержимое файла сохраняется в переменной. Затем используется команда cursor.executescript(script) для выполнения всех инструкций за раз.

Исключения базы данных SQLite

sqlite3.Warning. Подкласс Exception. Его можно игнорировать, если нужно, чтобы оно не останавливало выполнение.
sqlite3.Error. Базовый класс для остальных исключений модуля sqlite3. Подкласс Exception.
sqlite3.DatabaseError. Исключение, которое возвращается при ошибках базы данных. Например, если попытаться открыть файл как базу sqite3, хотя он ею не является, то вернется ошибка «sqlite3.DatabaseError: file is encrypted or is not a database».
sqlite3.IntegrityError. Подкласс DatabaseError. Эта ошибка возвращается, когда затрагиваются отношения в базе, например, например, не проходит проверка внешнего ключа.
sqlite3.ProgrammingError. Подкласс DatabaseError. Эта ошибка возникает из-за ошибок программиста: создание таблицы с именем, которое уже занято, синтаксическая ошибка в SQL-запросах.
sqlite3.OperationalError. Подкласс DatabaseError. Эту ошибку невозможно контролировать. Она появляется в ситуациях, которые касаются работы базы данных, например, обрыв соединения, неработающий сервер, проблемы с источником данных и так далее.
sqlite3.NotSupportedError. Это исключение появляется при попытке использовать неподдерживаемое базой данных API. Пример: вызов метода rollback() для соединения, которое не поддерживает транзакции. Вызов коммита после команды создания таблицы.

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

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

Копировать



import sqlite3

import traceback

import sys



try:

    sqlite_connection = sqlite3.connect('sqlite_python.db')

    cursor = sqlite_connection.cursor()

    print("База данных подключена к SQLite")



    sqlite_insert_query = """INSERT INTO unknown_table_1

                          (id, text)  VALUES  (1, 'Демо текст')"""



    count = cursor.execute(sqlite_insert_query)

    sqlite_connection.commit()

    print("Запись успешно вставлена в таблицу sqlitedb_developers ", cursor.rowcount)

    cursor.close()



except sqlite3.Error as error:

    print("Не удалось вставить данные в таблицу sqlite")

    print("Класс исключения: ", error.__class__)

    print("Исключение", error.args)

    print("Печать подробноcтей исключения SQLite: ")

    exc_type, exc_value, exc_tb = sys.exc_info()

    print(traceback.format_exception(exc_type, exc_value, exc_tb))

finally:

    if (sqlite_connection):

        sqlite_connection.close()

        print("Соединение с SQLite закрыто")

База данных подключена к SQLite
Не удалось вставить данные в таблицу sqlite
Класс исключения:  <class 'sqlite3.OperationalError'>
Исключение ('no such table: unknown_table_1',)
Печать подробноcтей исключения SQLite: 
['Traceback (most recent call last):\n', '  File "C:\\Users\\demo\\AppData\\Local\\Programs\\Python\\Python38\\sqlitet.py", line 13, in <module>\n    count = cursor.execute(sqlite_insert_query)\n', 'sqlite3.OperationalError: no such table: unknown_table_1\n']
Соединение с SQLite закрыто

Изменения timeout при подключении из Python

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

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

По умолчанию значение этого параметра равно 5.0 (5 секунд). Его не нужно задавать, потому что это значение по умолчанию. Таким образом при подключении к базе данных из Python, если ответ не будет получен в течение 5 секунд, вернется исключение. Однако параметр все-таки можно задать в функции sqlite3.connect.

Посмотрим, как это сделать из Python.

Копировать



import sqlite3



def read_sqlite_table():

    try:

        sqlite_connection= sqlite3.connect('sqlite_python.db', timeout=20)

        cursor = sqlite_connection.cursor()

        print("Подключен к SQLite")



        sqlite_select_query = """SELECT count(*) from sqlitedb_developers"""

        cursor.execute(sqlite_select_query)

        total_rows = cursor.fetchone()

        print("Всего строк:  ", total_rows)

        cursor.close()



    except sqlite3.Error as error:

        print("Ошибка при подключении к sqlite", error)

    finally:

        if (sqlite_connection):

            sqlite_connection.close()

            print("Соединение с SQLite закрыто")



read_sqlite_table()

Вывод:

Подключен к SQLite
Всего строк:   (0,)
Соединение с SQLite закрыто

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

Для статистики может потребоваться найти количество строк базы данных, которые были вставлены, удалены или изменены с момента открытия соединения. Для этого используется функция connection.total_changes модуля sqlite3.

Этот метод возвращается общее количество строк, которые были затронуты. Рассмотрим пример.

Копировать



import sqlite3



try:

    sqlite_connection = sqlite3.connect('sqlite_python.db')

    cursor = sqlite_connection.cursor()

    print("Подключен к SQLite")



    sqlite_insert_query = """INSERT INTO sqlitedb_developers

                          (id, name, email, joining_date, salary)

                          VALUES (4, 'Alex', 'sale@gmail.com', '2020-11-20', 8600);"""

    cursor.execute(sqlite_insert_query)



    sql_update_query = """Update sqlitedb_developers set salary = 10000 where id = 4"""

    cursor.execute(sql_update_query)



    sql_delete_query = """DELETE from sqlitedb_developers where id = 4"""

    cursor.execute(sql_delete_query)



    sqlite_connection.commit()

    cursor.close()



except sqlite3.Error as error:

    print("Ошибка при работе с SQLite", error)

finally:

    if (sqlite_connection):

        print("Всего строк, измененных после подключения к базе данных: ", sqlite_connection.total_changes)

        sqlite_connection.close()

        print("Соединение с SQLite закрыто")

Подключен к SQLite
Всего строк, измененных после подключения к базе данных:  3
Соединение с SQLite закрыто

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

Модуль sqlite3 в Python предоставляет функцию для сохранения резервной копии базы данных SQLite. С помощью метода connection.backup() можно сделать резервную копию базы SQLite.

connection.backup(target, *, pages=0, progress=None, name="main", sleep=0.250)

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

По умолчанию когда параметр pages равен 0 или отрицательному числу, вся база данных копируется в один шаг. В противном случае метод выполняет цикл, копируя заданное количество страниц за раз.

Аргумент name определяет базу данных, резервную копию которой нужно сделать. Аргумент sleep — количество секунд между последовательными попытками сохранить оставшиеся страницы. Аргумент sleep можно задать как в качестве целого числа, так и в виде числа с плавающей точкой.

Рассмотрим один пример копирования базы данных в другую.

Копировать



import sqlite3



def progress(status, remaining, total):

    print(f'Скопировано {total-remaining} из {total}...')



try:

    sqlite_con = sqlite3.connect('sqlite_python.db')

    backup_con = sqlite3.connect('sqlite_backup.db')

    with backup_con:

        sqlite_con.backup(backup_con, pages=3, progress=progress)

    print("Резервное копирование выполнено успешно")

except sqlite3.Error as error:

    print("Ошибка при резервном копировании: ", error)

finally:

    if(backup_con):

        backup_con.close()

        sqlite_con.close()

Скопировано 3 из 5...
Скопировано 5 из 5...
Резервное копирование выполнено успешно

Примечания:

После подключения к SQLite обе базы данных были открыты с помощью двух разных подключений
Дальше выполняется метод connection.backup() с помощью экземпляра первого подключения. Также задано количество страниц, которые нужно скопировать за одну итерацию.

Введение в SQLite Python

Подключение к SQLite в Python

Понимание SQLite-подключения в подробностях

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

Типы данных SQLite и соответствие типам Python

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

Выполнение SQL запросов с помощью функции executescript

Исключения базы данных SQLite

Изменения timeout при подключении из Python

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

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

Введение в SQLite Python

Вставка данных в таблицу SQLite

Получение данных из таблицы SQLite

Обновление данных в SQLite-таблице

Удаление записей из таблицы SQLite

Создание или переопределение SQL-функций в SQLite

Работа с изображениями и файлами в SQLite

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

Подключение к SQLite в Python

Понимание SQLite-подключения в подробностях

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

Типы данных SQLite и соответствие типам Python

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

Выполнение SQL запросов с помощью функции executescript

Исключения базы данных SQLite

Изменения timeout при подключении из Python

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

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

Статьи по теме