SQLite

Знакомство

SQLite представляет библиотеку, которая написана на языке Си и которая реализует движок реляционных баз данных. На сегодняшний день SQLite, возможно, самая используемая система баз данных. Так, ее бд можно найти в каждом устройстве на Android, iOS, Mac, Windows 10/11, ее используются большинство распространенных браузеров - Firefox, Chrome, Safari и т.д.

В отличие от других систем баз данных, как MS SQL Server, MySQL, Postgres и т.д., для SQLite не требуется сервер базы данных. SQLite представляет встраиваемый движок базы данных, который обращается напрямую к файлу базы данных на диске. Соответственно для работы с базами данных не надо явным образом устанавливать или как-то конфигурировать SQLite.

SQLite имеет полноценную поддержку большинства возможностей, которыми обладают другие реляционные СУБД - таблицы, индексы, триггеры, представления.

Для создания запросов к базе данных SQLite применяет язык SQL (точнее свою реализацию), которая в целом похожа на реализации и диалекты SQL, применяемые в других реляционных СУБД.

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

Что касается разработки приложений большинство распространенных и популярных языков программирования, таких как Python, C#, Java, и т.д., имеют поддержку для SQLite, что позволяет использовать эту СУБД в различных сценариях и самых различных типах приложений.

SQLite развивается как проект с открытым исходным кодом, который можно найти на github по адресу https://github.com/SQLite/SQLite. Кроме того, всю сопроводительную информацию по SQLite можно найти на официальном сайте - https://www.sqlite.org

Первая версия SQLite вышла в августе 2000 года. Изначальным разработчиком является Ричард Хипп (D. Richard Hipp).

Для разработки баз данных SQLite и управления ними без привязки к конкретному языку программирования, можем использовать официальный клиент - SQLite3. А также использовать ряд неофициальных графических клиентов, как например, SQLiteStudio или DB Browser for SQLite.

Ограничения

При работе с SQLite в Python существует несколько ограничений и особенностей, которые стоит учитывать:

1. Типы данных:

   • SQLite использует динамическую типизацию. Это означает, что тип данных столбца определяется значением, которое в него вставляется, а не объявленным типом столбца. Например, вы можете вставить строку в столбец, объявленный как INTEGER.

2. Многопользовательский доступ:

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

3. Сложность транзакций:

   • Хотя SQLite поддерживает транзакции, его возможности в этой области ограничены по сравнению с клиент-серверными СУБД, такими как PostgreSQL или MySQL. Например, в SQLite нет встроенной поддержки параллельных транзакций с разной степенью изоляции.

4. Функциональность SQL:

   • Некоторые продвинутые функции SQL, такие как RIGHT JOIN или FULL OUTER JOIN, не поддерживаются в SQLite. Также, некоторые функции, доступные в других СУБД, могут отсутствовать или работать иначе.

5. Безопасность:

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

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

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

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

Для подключения к базе данных в этой библиотеке определена функция connect():

import sqlite3

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)

Она принимает следующие параметры:

• database: путь к файлу базы данных. Если база данных расположена в памяти, а не на диске, то для открытия подключения используется :memory:.
• timeout: период времени в секундах, через который генерируется исключение, если файл базы данных занят другим процессом.
• detect_types: управляет сопоставлением типов SQLite с типами Python. Значение 0 отключает сопоставление.
• isolation_level: устанавливает уровень изоляции подключения и определяет процесс отрытия неявных транзакций. Возможные значения: DEFERRED (значение по умолчанию), EXCLUSIVE, IMMEDIATE или None (неявные транзакции отключены).
• check_same_thread: если равно True (значение по умолчанию), то только поток, который создал подключение, может его использовать. Если равно False, подключение может использоваться несколькими потоками.
• factory: класс фабрики, который применяется для создания подключения. Должен представлять класс, производный от Connection. По умолчанию используется класс sqlite3.Connection.
• cached_statements: количество SQL-инструкций, которые должны кэшироваться. По умолчанию равно 128.
• uri: логическое значение, если равно True, то путь к базе данных рассматривается как адрес URI.

Обязательным параметром функции является путь к базе данных. Результатом функции является объект подключения (объект класса Connection).

Например, подключение к базе данных monogenes.db, которая располагается в той же папке, что и текущий скрипт (если такая база данных отсутствует, то она автоматически создается):

import sqlite3
 
connection = sqlite3.connect("monogenes.db")

Типов данных SQLite и Python

Следует понимать, как сопоставляются типы SQLite и типы Python. По умолчанию применяются следующие сопоставления:

Python -> SQLite

• None -> NULL
• int -> INTEGER
• float -> REAL
• str -> TEXT
• bytes -> BLOB

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

Получение курсора

Для выполнения выражений SQL и получения данных из БД, необходимо создать курсор. Для этого у объекта Connection вызывается метод cursor(). Этот метод возвращает объект Cursor:

import sqlite3

connection = sqlite3.connect("monogenes.db")  # создаем подключение
cursor = connection.cursor()   # получаем курсор

Выполнение запросов к базе данных

Для выполнения запросов и получения данных класс Cursor предоставляет ряд методов:

• execute(sql, parameters=()): выполняет одну SQL-инструкцию. Через второй параметр в код SQL можно передать набор параметров в виде списка или словаря.
• executemany(sql, parameters): выполняет параметризованное SQL-инструкцию. Через второй параметр принимает наборы значений, которые передаются в выполняемый код SQL.
• executescript(sql_script): выполняет SQL-скрипт, который может включать множество SQL-инструкций.
• fetchone(): возвращает одну строку в виде кортежа из полученного из БД набора строк.
• fetchmany(size=cursor.arraysize): возвращает набор строк в виде списка. Количество возвращаемых строк передается через параметр. Если больше строк нет в наборе, то возвращается пустой список.
• fetchall(): возвращает все (оставшиеся) строки в виде списка. При отсутствии строк возвращается пустой список.

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

Для создания таблицы в SQLite применяется инструкция CREATE TABLE. Например, создадим в базе данных monogenes.db таблицу people:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# создаем таблицу people
cursor.execute("""CREATE TABLE people
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  
                name TEXT, 
                age INTEGER)
                """)

В метод cursor.execute() передается инструкция CREATE TABLE, которая создает таблицу people с тремя столбцами. Столбец id представляет идентификатор пользователя, хранит данные типа Integer, то есть число, и также представляет первичный ключ, значение которого будет автоматически генерироваться и увеличиваться на 1 с каждой новой строкой. Второй столбец - name представляет строку - имя пользователя. И третий столбец - age представляет возраст пользователя.

После выполнения скрипта можно открыть базу данных в каком-нибудь браузере баз данных SQLite, например, в DB Browser for SQLite и увидеть созданную таблицу.

Создание таблицы в базе данных sqlite в python