Встроенная база данных Python
Перед началом статьи хочу сказать, что еще больше полезной и нужной информации вы найдете в нашем Telegram-канале. Подпишитесь, мне будет очень приятно.
Если вы разработчик программного обеспечения, то, скорее всего, вы знакомы с невероятно легкой базой данных SQLite или даже уже использовали ее. Она содержит практически все функции реляционной базы данных и представлена всего одним файлом. На официальном сайте можно найти несколько сценариев применения SQLite:
- встроенные устройства и интернет вещей;
- анализ данных;
- передача данных;
- архив файлов и/или контейнер данных;
- внутренние или временные базы данных;
- замена корпоративной базы данных в период демо-версий или тестирования;
- обучение и тестирование;
- экспериментальные расширения языка SQL.
Если вам нужна SQLite для каких-либо других целей, то обратитесь к документации.
Но самое главное — SQLite встроена в библиотеку Python. То есть вам не нужно устанавливать серверное или клиентское ПО и поддерживать работу какого-либо сервиса. Если вы импортировали библиотеку в Python и приступили к работе, значит вы уже используете систему управления реляционными базами данных!
«Встроенность» предполагает, что вам не нужно запускать pip install для получения библиотеки. Просто импортируйте ее с помощью:
import sqlite3 as sl
Не беспокойтесь о драйверах, строках подключения и т.д. Вы можете создать базу данных SQLite и задать такой простой объект подключения, как:
После запуска этой строки кода происходит создание с БД и активируется подключение к ней. Дело в том, что базы данных, к которой мы просим подключиться Python, не существует, поэтому он автоматически создает пустую. Также мы можем ввести точно такой же код для подключения к уже существующей базе данных.
Теперь создадим таблицу:
Мы добавили три столбца в таблицу USER. Как видите, SQLite действительно легка и при этом поддерживает все основные функции обычной реляционной СУБД, такие как тип данных, обнуляемый тип, первичный ключ и автоинкремент.
После запуска этого кода создается таблица, но она ничего не выводит.
Вставим несколько записей в только что созданную таблицу USER, чтобы доказать, что она действительно создана.
Предположим, мы хотим вставить сразу несколько записей. Выполним:
Определяем оператор SQL с вопросительными знаками ? в качестве заполнителя. Теперь создадим образцы данных для вставки, а затем вставим их с помощью объекта подключения:
with con:
con.executemany(sql, data)
После запуска кода не появилось никаких предупреждений, значит все прошло успешно.
Пришло время удостовериться, что все сделано правильно. Выполним запрос к таблице на возврат образцов строк.
Как видите, все очень просто!
Более того, несмотря на свою легкость SQLite является широко используемой базой данных, и большинство программного обеспечения клиентов SQL ее поддерживает.
Чаще всего я использую инструмент DBeaver. Рассмотрим его на примере.
Поскольку я использую Google Colab, я буду загружать файл my-test.db на свой компьютер. При запуске Python на локальном компьютере можно использовать клиент SQL для прямого подключения к файлу баз данных.
Создаем новое соединение в DBeaver и выбираем SQLite в качестве типа БД:
Затем переходим к файлу БД:
Теперь к базе данных можно выполнить любой SQL-запрос, как и в любых других реляционных БД:
Но это еще не все. Дело в том, что, являясь встроенной функцией Python, SQLite может легко интегрироваться с фреймом данных Pandas.
Определяем фрейм данных:
Затем просто вызываем метод фрейма данных to_sql(), чтобы сохранить его в базе данных:
И это все, что нужно сделать! Вам даже не придется создавать таблицу заранее — типы данных и длина столбцов будут определены автоматически. Конечно, при желании вы также можете определить ее заранее.
Допустим, мы хотим объединить таблицу USER и SKILL и прочитать результат во фрейме данных Pandas. Это тоже можно выполнить без проблем.
Результаты запишем в новую таблицу под названием USER_SKILL:
Теперь мы также можем использовать клиент SQL для получения таблицы:
В Python есть множество скрытых сюрпризов. Но скрыты они не специально: дело лишь в том, что в Python настолько много функций «из коробки», что невозможно раскрыть их все сразу.
В этой статье мы узнали, как использовать встроенную библиотеку Python sqlite3 для создания таблиц и манипулирования ими в базе данных SQLite. Конечно, она также поддерживает обновление и удаление, которые вы можете попробовать самостоятельно.
Но самое главное, мы можем легко прочитать таблицу из базы данных SQLite во фрейме данных Pandas и наоборот. Такая возможность еще больше упрощает взаимодействие с этой легкой реляционной базой данных.
Возможно, вы заметили, что в SQLite нет аутентификации. С ней бы данная библиотека перестала быть такой легкой.
Учебник по SQLite3 в Python
SQLite – это C библиотека, реализующая легковесную дисковую базу данных (БД), не требующую отдельного серверного процесса и позволяющую получить доступ к БД с использованием языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создать прототип приложения с использованием SQLite, а затем перенести код в более многофункциональную БД, такую как PostgreSQL или Oracle.
Модуль sqlite3 реализует интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249.
Создание соединения
Чтобы воспользоваться SQLite3 в Python необходимо импортировать модуль sqlite3, а затем создать объект подключения к БД.
Объект подключения создается с помощью метода connect():
Курсор SQLite3
Для выполнения операторов SQL, нужен объект курсора, создаваемый методом cursor().
Курсор SQLite3 – это метод объекта соединения. Для выполнения операторов SQLite3 сначала устанавливается соединение, а затем создается объект курсора с использованием объекта соединения следующим образом:
Теперь можно использовать объект курсора для вызова метода execute() для выполнения любых запросов SQL.
Создание базы данных
После создания соединения с SQLite, файл БД создается автоматически, при условии его отсутствия. Этот файл создается на диске, но также можно создать базу данных в оперативной памяти, используя параметр «:memory:» в методе connect. При этом база данных будет называется инмемори.
Рассмотрим приведенный ниже код, в котором создается БД с блоками try, except и finally для обработки любых исключений:
Сначала импортируется модуль sqlite3, затем определяется функция с именем sql_connection. Внутри функции определен блок try, где метод connect() возвращает объект соединения после установления соединения.
Затем определен блок исключений, который в случае каких-либо исключений печатает сообщение об ошибке. Если ошибок нет, соединение будет установлено, тогда скрипт распечатает текст «Connection is established: Database is created in memory».
Далее производится закрытие соединения в блоке finally. Закрытие соединения необязательно, но это хорошая практика программирования, позволяющая освободить память от любых неиспользуемых ресурсов.
Создание таблицы
Чтобы создать таблицу в SQLite3, выполним запрос Create Table в методе execute(). Для этого выполним следующую последовательность шагов:
- Создание объекта подключения
- Объект Cursor создается с использованием объекта подключения
- Используя объект курсора, вызывается метод execute с запросом create table в качестве параметра.
Давайте создадим таблицу Employees со следующими колонками:
Код будет таким:
В приведенном выше коде определено две функции: первая устанавливает соединение; а вторая — используя объект курсора выполняет SQL оператор create table.
Метод commit() сохраняет все сделанные изменения. В конце скрипта производится вызов обеих функций.
Для проверки существования таблицы воспользуемся браузером БД для sqlite.
Вставка данных в таблицу
Чтобы вставить данные в таблицу воспользуемся оператором INSERT INTO. Рассмотрим следующую строку кода:
Также можем передать значения / аргументы в оператор INSERT в методе execute (). Также можно использовать знак вопроса (?) в качестве заполнителя для каждого значения. Синтаксис INSERT будет выглядеть следующим образом:
Где картеж entities содержат значения для заполнения одной строки в таблице:
Код выглядит следующим образом:
Обновление таблицы
Предположим, что нужно обновить имя сотрудника, чей идентификатор равен 2. Для обновления будем использовать инструкцию UPDATE. Также воспользуемся предикатом WHERE в качестве условия для выбора нужного сотрудника.
Рассмотрим следующий код:
Это изменит имя Эндрю на Роджерс.
Оператор SELECT
Оператор SELECT используется для выборки данных из одной или более таблиц. Если нужно выбрать все столбцы данных из таблицы, можете использовать звездочку (*). SQL синтаксис для этого будет следующим:
В SQLite3 инструкция SELECT выполняется в методе execute объекта курсора. Например, выберем все стрики и столбцы таблицы employee:
Если нужно выбрать несколько столбцов из таблицы, укажем их, как показано ниже:
Оператор SELECT выбирает все данные из таблицы employees БД.
Выборка всех данных
Чтобы извлечь данные из БД выполним инструкцию SELECT, а затем воспользуемся методом fetchall() объекта курсора для сохранения значений в переменной. При этом переменная будет являться списком, где каждая строка из БД будет отдельным элементом списка. Далее будет выполняться перебор значений переменной и печатать значений.
Код будет таким:
Также можно использовать fetchall() в одну строку:
Если нужно извлечь конкретные данные из БД, воспользуйтесь предикатом WHERE. Например, выберем идентификаторы и имена тех сотрудников, чья зарплата превышает 800. Для этого заполним нашу таблицу большим количеством строк, а затем выполним запрос.
Можете использовать оператор INSERT для заполнения данных или ввести их вручную в программе браузера БД.
Теперь, выберем имена и идентификаторы тех сотрудников, у кого зарплата больше 800:
В приведенном выше операторе SELECT вместо звездочки (*) были указаны атрибуты id и name.
SQLite3 rowcount
Счетчик строк SQLite3 используется для возврата количества строк, которые были затронуты или выбраны последним выполненным запросом SQL.
Когда вызывается rowcount с оператором SELECT, будет возвращено -1, поскольку количество выбранных строк неизвестно до тех пор, пока все они не будут выбраны. Рассмотрим пример:
Поэтому, чтобы получить количество строк, нужно получить все данные, а затем получить длину результата:
Когда оператор DELETE используется без каких-либо условий (предложение where), все строки в таблице будут удалены, а общее количество удаленных строк будет возвращено rowcount.
Если ни одна строка не удалена, будет возвращено 0.
Список таблиц
Чтобы вывести список всех таблиц в базе данных SQLite3, нужно обратиться к таблице sqlite_master, а затем использовать fetchall() для получения результатов из оператора SELECT.
Sqlite_master — это главная таблица в SQLite3, в которой хранятся все таблицы.
Проверка существования таблицы
При создании таблицы необходимо убедиться, что таблица еще не существует. Аналогично, при удалении таблицы она должна существовать.
Чтобы проверить, если таблица еще не существует, используем «if not exists» с оператором CREATE TABLE следующим образом:
Точно так же, чтобы проверить, существует ли таблица при удалении, мы используем «if not exists» с инструкцией DROP TABLE следующим образом:
Также проверим, существует ли таблица, к которой нужно получить доступ, выполнив следующий запрос:
Если указанное имя таблицы не существует, будет возвращен пустой массив.
Удаление таблицы
Удаление таблицы выполняется с помощью оператора DROP. Синтаксис оператора DROP выглядит следующим образом:
Чтобы удалить таблицу, таблица должна существовать в БД. Поэтому рекомендуется использовать «if exists» с оператором DROP. Например, удалим таблицу employees:
Исключения SQLite3
Исключением являются ошибки времени выполнения скрипта. При программировании на Python все исключения являются экземплярами класса производного от BaseException.
В SQLite3 у есть следующие основные исключения Python:
DatabaseError
Любая ошибка, связанная с базой данных, вызывает ошибку DatabaseError.
IntegrityError
IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных, например, когда внешние данные не обновляются во всех таблицах, что приводит к несогласованности данных.
ProgrammingError
Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с неправильным количеством параметров / аргументов.
OperationalError
Это исключение возникает при сбое операций базы данных, например, при необычном отключении. Не по вине программиста.
NotSupportedError
При использовании некоторых методов, которые не определены или не поддерживаются базой данных, возникает исключение NotSupportedError.
Массовая вставка строк в Sqlite
Для вставки нескольких строк одновременно использовать оператор executemany.
Рассмотрим следующий код:
Здесь создали таблицу с двумя столбцами, тогда у «данных» есть четыре значения для каждого столбца. Эта переменная передается методу executemany() вместе с запросом.
Обратите внимание, что использовался заполнитель для передачи значений.
Закрытие соединения
Когда работа с БД завершена, рекомендуется закрыть соединение. Соединение может быть закрыто с помощью метода close ().
Чтобы закрыть соединение, используйте объект соединения с вызовом метода close() следующим образом:
SQLite3 datetime
В базе данных Python SQLite3 можно легко сохранять дату или время, импортируя модуль datatime. Следующие форматы являются наиболее часто используемыми форматами для даты и времени:
Рассмотрим следующий код:
В этом коде модуль datetime импортируется первым, далее создали таблицу с именем assignments с тремя столбцами.
Тип данных третьего столбца — дата. Чтобы вставить дату в столбец, воспользовались datetime.date. Точно так же можно использовать datetime.time для обработки времени.
Вывод
SQLite можно использовать в своих разработках, но с учетом особенностей этой БД. SQLite прекрасно подойдет для проектов у которых мало операций записи, не нужна система прав доступа к БД и ограниченны ресурсы сервера.
А вы знаете о том, что в Python есть встроенная СУБД?
Если вы — программист, то я полагаю, что вы, наверняка, знаете о существовании чрезвычайно компактной и нетребовательной к ресурсам СУБД SQLite, или даже пользовались ей. Эта система обладает практически всеми возможностями, которых можно ожидать от реляционной СУБД, но при этом всё хранится в единственном файле. Вот некоторые сценарии использования SQLite, упомянутые на официальном сайте этой системы:
- Встраиваемые устройства и IoT.
- Анализ данных.
- Перенос данных из одной системы в другую.
- Архивирование данных и (или) упаковка данных в контейнеры.
- Хранение данных во внешней или временной БД.
- Заменитель корпоративной БД, используемый в демонстрационных или испытательных целях.
- Обучение, освоение начинающими практических приёмов работы с БД.
- Прототипирование и исследование экспериментальных расширений языка SQL.
Данный материал посвящён использованию SQLite в Python-разработке. Поэтому для нас особенно важно то, что эта СУБД, представленная модулем sqlite3 , входит в стандартную библиотеку языка. То есть оказывается, что для работы с SQLite из Python-кода не нужно устанавливать некое клиент-серверное ПО, не нужно поддерживать работу какого-то сервиса, отвечающего за работу с СУБД. Достаточно лишь импортировать модуль sqlite3 и приступить к его использованию в программе, получив в своё распоряжение систему управления реляционными базами данных.
Импорт модуля
Выше я говорил о том, что SQLite — это СУБД, встроенная в Python. Это значит, что для того чтобы приступить к работе с ней, достаточно импортировать соответствующий модуль, не выполняя предварительно его установку с помощью команды вроде pip install . Команда импорта SQLite выглядит так:
Создание подключения к БД
Для организации подключения к базе данных SQLite не нужно беспокоиться об установке драйверов, о подготовке строк подключения и о прочих подобных вещах. Создать базу данных и получить в своё распоряжение объект подключения к ней можно очень просто и быстро:
Выполнив эту строку кода, мы создадим базу данных и подключимся к ней. Дело тут в том, что база данных, к которой мы подключаемся, пока не существует, поэтому система автоматически создаёт новую пустую БД. Если же база данных уже создана (предположим, это my-test.db из предыдущего примера), для того чтобы к ней подключиться, достаточно воспользоваться точно таким же кодом.
Файл только что созданной базы данных
Создание таблицы
Теперь давайте создадим таблицу в нашей новой БД:
Тут описано добавление в БД таблицы USER с тремя столбцами. Как видите, SQLite — это и правда очень простая в работе СУБД, но она обладает всеми основными возможностями, наличия которых можно ожидать от обычной системы управления реляционными базами данных. Речь идёт о поддержке типов данных, в том числе — типов, допускающих значение null , о поддержке первичного ключа и автоинкремента.
Если этот код функционирует так, как ожидается (вышеприведённая команда, правда, ничего не возвращает), в нашем распоряжении окажется таблица, готовая к дальнейшей работе с ней.
Вставка записей в таблицу
Вставим несколько записей в таблицу USER , которую мы только что создали. Это, кроме прочего, даст нам доказательство того, что таблица, и правда, была создана вышеприведённой командой.
Представим, что нам нужно добавить в таблицу несколько записей одной командой. В SQLite сделать это очень просто:
Здесь нам нужно определить SQL-выражение со знаками вопроса ( ? ) в виде местозаполнителей. Учитывая то, что в нашем распоряжении есть объект подключения к базе данных, мы, подготовив выражение и данные, можем вставить записи в таблицу:
Сообщений об ошибках после выполнения этого кода не поступает, а это значит, что данные успешно добавлены в таблицу.
Выполнение запросов к базе данных
Теперь пришло время узнать о том, правильно ли отработали команды, которые мы только что выполняли. Давайте выполним запрос к БД и попробуем получить из таблицы USER какие-то данные. Например — получим записи, относящиеся к пользователям, возраст которых не превышает 22 года:
Результат выполнения запроса к БД
Как видите, то, что было нужно, получить удалось. И сделать это было очень просто.
Кроме того, даже хотя SQLite — простая СУБД, она отличается крайне широкой поддержкой. Поэтому с ней можно работать, используя большинство SQL-клиентов.
Я пользуюсь DBeaver. Предлагаю взглянуть на то, как это выглядит.
Подключение к базе данных SQLite из SQL-клиента (DBeaver)
Я пользуюсь облачным сервисом Google Colab и хочу загрузить файл my-test.db на свой компьютер. Если же вы экспериментируете с SQLite на компьютере, то это значит, что вы, без необходимости скачивать откуда-то файл базы данных, можете подключиться к ней, используя SQL-клиент.
В случае с DBeaver для подключения к БД SQLite нужно создать новое подключение и выбрать, в качества типа базы данных, SQLite.
Подготовка подключения в DBeaver
Затем надо найти файл базы данных.
Подключение файла базы данных
После этого можно выполнять SQL-запросы к базе данных. Тут нет ничего особенного, отличающегося от работы с обычными реляционными БД.
Выполнение запросов к базе данных
Интеграция с pandas
Думаете, на этом мы завершим разговор о поддержке SQLite в Python? Нет, нам ещё есть о чём поговорить. А именно, так как SQLite — это стандартный Python-модуль, эта СУБД легко интегрируется с дата-фреймами pandas.
Датафрейм pandas
Для сохранения датафрейма в БД можно просто воспользоваться его методом to_sql() :
Вот и всё! Нам даже не нужно заранее создавать таблицу. Типы данных и характеристики полей будут настроены автоматически, на основании характеристик датафрейма. Конечно, вы, если надо, можете настроить всё самостоятельно.
Теперь, предположим, нам нужно получить объединение таблиц USER и SKILL и записать полученные данные в датафрейм pandas. Это тоже очень просто:
Чтение данных из БД в датафрейм pandas
Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем USER_SKILL :
С этой таблицей, конечно, можно работать и пользуясь SQL-клиентом.
Применение SQL-клиента для работы с базой данных
Итоги
В Python, безусловно, есть много приятных неожиданностей, которые, если специально их не искать, можно и не заметить. Специально подобные возможности никто не прятал, но из-за того, что в Python встроено очень много всего, на некоторые из таких возможностей можно просто не обратить внимания, или, откуда-то о них узнав, просто о них забыть.
Здесь я рассказал о том, как использовать встроенную в Python библиотеку sqlite3 для создания баз данных и для работы с ними. Конечно, такие БД поддерживают не только операцию добавления данных, но и операции изменения и удаления информации. Полагаю, вы, узнав о sqlite3 , испытаете всё это сами.
Очень важно то, что SQLite отлично стыкуется с pandas. Данные из БД очень легко считывать, помещая в датафреймы. Не менее проста и операция по сохранению содержимого датафреймов в базу данных. Это ещё сильнее упрощает использование SQLite.
Предлагаю всем, кто дочитал до этого места, заняться собственными исследованиями в поиске интересных возможностей Python!