Как интегрируется язык python sqlite

А вы знаете о том, что в Python есть встроенная СУБД?

Если вы — программист, то я полагаю, что вы, наверняка, знаете о существовании чрезвычайно компактной и нетребовательной к ресурсам СУБД SQLite, или даже пользовались ей. Эта система обладает практически всеми возможностями, которых можно ожидать от реляционной СУБД, но при этом всё хранится в единственном файле. Вот некоторые сценарии использования SQLite, упомянутые на официальном сайте этой системы:

Встраиваемые устройства и IoT.
Анализ данных.
Перенос данных из одной системы в другую.
Архивирование данных и (или) упаковка данных в контейнеры.
Хранение данных во внешней или временной БД.
Заменитель корпоративной БД, используемый в демонстрационных или испытательных целях.
Обучение, освоение начинающими практических приёмов работы с БД.
Прототипирование и исследование экспериментальных расширений языка SQL.

Данный материал посвящён использованию SQLite в Python-разработке. Поэтому для нас особенно важно то, что эта СУБД, представленная модулем sqlite3 , входит в стандартную библиотеку языка. То есть оказывается, что для работы с SQLite из Python-кода не нужно устанавливать некое клиент-серверное ПО, не нужно поддерживать работу какого-то сервиса, отвечающего за работу с СУБД. Достаточно лишь импортировать модуль sqlite3 и приступить к его использованию в программе, получив в своё распоряжение систему управления реляционными базами данных.

Импорт модуля

Выше я говорил о том, что SQLite — это СУБД, встроенная в Python. Это значит, что для того чтобы приступить к работе с ней, достаточно импортировать соответствующий модуль, не выполняя предварительно его установку с помощью команды вроде pip install . Команда импорта SQLite выглядит так:

import sqlite3 as sl

Создание подключения к БД

Для организации подключения к базе данных SQLite не нужно беспокоиться об установке драйверов, о подготовке строк подключения и о прочих подобных вещах. Создать базу данных и получить в своё распоряжение объект подключения к ней можно очень просто и быстро:

con = sl.connect('my-test.db')

Выполнив эту строку кода, мы создадим базу данных и подключимся к ней. Дело тут в том, что база данных, к которой мы подключаемся, пока не существует, поэтому система автоматически создаёт новую пустую БД. Если же база данных уже создана (предположим, это my-test.db из предыдущего примера), для того чтобы к ней подключиться, достаточно воспользоваться точно таким же кодом.

Файл только что созданной базы данных

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

Теперь давайте создадим таблицу в нашей новой БД:

with con: con.execute(""" CREATE TABLE USER ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER ); """)

Тут описано добавление в БД таблицы USER с тремя столбцами. Как видите, SQLite — это и правда очень простая в работе СУБД, но она обладает всеми основными возможностями, наличия которых можно ожидать от обычной системы управления реляционными базами данных. Речь идёт о поддержке типов данных, в том числе — типов, допускающих значение null , о поддержке первичного ключа и автоинкремента.

Если этот код функционирует так, как ожидается (вышеприведённая команда, правда, ничего не возвращает), в нашем распоряжении окажется таблица, готовая к дальнейшей работе с ней.

Вставка записей в таблицу

Вставим несколько записей в таблицу USER , которую мы только что создали. Это, кроме прочего, даст нам доказательство того, что таблица, и правда, была создана вышеприведённой командой.

Представим, что нам нужно добавить в таблицу несколько записей одной командой. В SQLite сделать это очень просто:

sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)' data = [ (1, 'Alice', 21), (2, 'Bob', 22), (3, 'Chris', 23) ]

Здесь нам нужно определить SQL-выражение со знаками вопроса ( ? ) в виде местозаполнителей. Учитывая то, что в нашем распоряжении есть объект подключения к базе данных, мы, подготовив выражение и данные, можем вставить записи в таблицу:

with con: con.executemany(sql, data)

Сообщений об ошибках после выполнения этого кода не поступает, а это значит, что данные успешно добавлены в таблицу.

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

Теперь пришло время узнать о том, правильно ли отработали команды, которые мы только что выполняли. Давайте выполним запрос к БД и попробуем получить из таблицы USER какие-то данные. Например — получим записи, относящиеся к пользователям, возраст которых не превышает 22 года:

with con: data = con.execute("SELECT * FROM USER WHERE age



 Результат выполнения запроса к БД 
Как видите, то, что было нужно, получить удалось. И сделать это было очень просто.
Кроме того, даже хотя SQLite — простая СУБД, она отличается крайне широкой поддержкой. Поэтому с ней можно работать, используя большинство SQL-клиентов.
Я пользуюсь DBeaver. Предлагаю взглянуть на то, как это выглядит.
Подключение к базе данных SQLite из SQL-клиента (DBeaver)
Я пользуюсь облачным сервисом Google Colab и хочу загрузить файл my-test.db на свой компьютер. Если же вы экспериментируете с SQLite на компьютере, то это значит, что вы, без необходимости скачивать откуда-то файл базы данных, можете подключиться к ней, используя SQL-клиент.
В случае с DBeaver для подключения к БД SQLite нужно создать новое подключение и выбрать, в качества типа базы данных, SQLite.

 Подготовка подключения в DBeaver 
Затем надо найти файл базы данных.

 Подключение файла базы данных 
После этого можно выполнять SQL-запросы к базе данных. Тут нет ничего особенного, отличающегося от работы с обычными реляционными БД.

 Выполнение запросов к базе данных 
Интеграция с pandas
Думаете, на этом мы завершим разговор о поддержке SQLite в Python? Нет, нам ещё есть о чём поговорить. А именно, так как SQLite — это стандартный Python-модуль, эта СУБД легко интегрируется с дата-фреймами pandas.
df_skill = pd.DataFrame(< 'user_id': [1,1,2,2,3,3,3], 'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning'] >) 

 Датафрейм pandas 
Для сохранения датафрейма в БД можно просто воспользоваться его методом to_sql() :
df_skill.to_sql('SKILL', con) 
Вот и всё! Нам даже не нужно заранее создавать таблицу. Типы данных и характеристики полей будут настроены автоматически, на основании характеристик датафрейма. Конечно, вы, если надо, можете настроить всё самостоятельно.
Теперь, предположим, нам нужно получить объединение таблиц USER и SKILL и записать полученные данные в датафрейм pandas. Это тоже очень просто:
df = pd.read_sql(''' SELECT s.user_id, u.name, u.age, s.skill FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id ''', con) 

 Чтение данных из БД в датафрейм pandas 
Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем USER_SKILL :
df.to_sql('USER_SKILL', con) 
С этой таблицей, конечно, можно работать и пользуясь SQL-клиентом.

 Применение SQL-клиента для работы с базой данных 
Итоги
В Python, безусловно, есть много приятных неожиданностей, которые, если специально их не искать, можно и не заметить. Специально подобные возможности никто не прятал, но из-за того, что в Python встроено очень много всего, на некоторые из таких возможностей можно просто не обратить внимания, или, откуда-то о них узнав, просто о них забыть.
Здесь я рассказал о том, как использовать встроенную в Python библиотеку sqlite3 для создания баз данных и для работы с ними. Конечно, такие БД поддерживают не только операцию добавления данных, но и операции изменения и удаления информации. Полагаю, вы, узнав о sqlite3 , испытаете всё это сами.
Очень важно то, что SQLite отлично стыкуется с pandas. Данные из БД очень легко считывать, помещая в датафреймы. Не менее проста и операция по сохранению содержимого датафреймов в базу данных. Это ещё сильнее упрощает использование SQLite.
Предлагаю всем, кто дочитал до этого места, заняться собственными исследованиями в поиске интересных возможностей Python!
Код, который я демонстрировал в этой статье, можно найти здесь.
Пользуетесь ли вы SQLite в своих Python-проектах?
Работа с базой данных: модуль sqlite3
Если вы разработчик программного обеспечения, то вы, скорее всего, слышали или даже использовали легковесную базу данных — SQLite. Она обладает всеми необходимыми функциями реляционной базы данных, но все сохраняется в одном файле. На официальном сайте представлены несколько сценариев использования SQLite:

Встраиваемые системы и Интернет вещей.
Анализ данных.
Передача данных.
Файловый архив и/или контейнер данных.
Внутренние или временные базы данных.
Замена базы данных во время демонстрации или тестирования.
Обучение и тестирование.
Экспериментальные расширения языка SQL.

Есть и другие сценарии использования SQLite, с ними вы можете ознакомиться в документации.
Что наиболее важно, Python имеет встроенную поддержку SQLite. Другими словами, вам не нужно устанавливать какое-либо серверное/клиентское программное обеспечение, достаточно просто импортировать стандартную библиотеку.
Импорт и использование

Когда мы говорим «встроенная», это означает, что вам даже не нужно запускать pip install , чтобы получить библиотеку. Просто импортируйте ее:
import sqlite3 as sl
Создание подключения к БД
Не беспокойтесь о драйверах, строках подключения и так далее. Вы можете создать базу данных SQLite и получить объект подключения к ней (Connection) следующим образом:
con = sl.connect('my-test.db')
После выполнения этой строки кода мы уже создали базу данных и подключились к ней. Это происходит потому, что база данных, к которой мы попросили Python подключиться, не существует, так что автоматически создается пустая. В противном случае мы можем использовать точно такой же код для подключения к существующей базе данных.

Создание таблицы
Далее давайте создадим таблицу.
with con: con.execute(""" CREATE TABLE USER ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER ); """)
В таблицу USER мы добавили три столбца — id, name, age. Как видите, SQLite поддерживает все основные функции обычной СУБД, такие как тип данных, nullable, первичный ключ и автоинкремент.
После выполнения этого кода будет создана таблица.
Вставка записей
Давайте вставим несколько записей в только что созданную таблицу USER , чтобы убедиться, что мы действительно ее создали.
Предположим, мы хотим вставить несколько записей за один раз. В Python мы можем легко это сделать:
sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)' data = [ (1, 'Alice', 21), (2, 'Bob', 22), (3, 'Chris', 23) ]
Нам нужно определить запрос SQL с вопросительными знаками ( ? ) для подстановки значений. Затем создать тестовые данные для вставки. После этого, с помощью объекта подключения, мы можем вставить наши тестовые строки.
with con: con.executemany(sql, data)
Запросы
Пришло время проверить все, что мы сделали. Давайте выполним запрос к таблице, чтобы получить тестовые строки.
with con: data = con.execute("SELECT * FROM USER WHERE age 

Как видите, все очень просто.
Кроме того, хотя SQLite и является легковесной, но широко используемой базой данных, большинство клиентов SQL поддерживает ее.
Давайте посмотрим на один из них — DBeaver.
Подключение к базе данных SQLite с помощью клиента SQL (DBeaver)
В DBeaver создайте новое соединение и выберите SQLite в качестве типа базы данных.

Затем перейдите к файлу БД.

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

Интеграция с Pandas

Думаете, это все? Нет. На самом деле SQLite может легко интегрироваться с Pandas Data Frame.
Давайте определим фрейм данных.
df_skill = pd.DataFrame(< 'user_id': [1,1,2,2,3,3,3], 'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning'] >)

Затем мы можем просто вызвать метод to_sql() фрейма данных, чтобы сохранить его в базе данных.
df_skill.to_sql('SKILL', con)
Нам даже не нужно создавать таблицу заранее: типы данных и длина столбцов будут определены автоматически. Конечно, вы все равно можете определить ее заранее, если хотите.
Допустим, мы хотим объединить таблицу USER и SKILL и прочитать результат во фрейме данных Pandas. Вот как это делается:
df = pd.read_sql(''' SELECT s.user_id, u.name, u.age, s.skill FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id ''', con)

Очень круто! Давайте запишем результаты в новую таблицу USER_SKILL .
df.to_sql('USER_SKILL', con)
Затем мы также можем использовать наш SQL-клиент для получения таблицы.

Заключение
В этой статье мы рассмотрели, как использовать встроенную библиотеку Python sqlite3 для создания таблиц и управления ими в базе данных SQLite. Конечно, также поддерживается обновление и удаление. Мы думаем, что вы сами можете попробовать это сделать после завершения этого туториала.
Что наиболее важно, мы можем легко прочитать таблицу из базы данных SQLite во фрейм данных Pandas или наоборот. Это позволяет нам еще проще взаимодействовать с нашей легковесной реляционной базой данных.
Вы могли заметить, что в SQLite нет аутентификации. Это спроектированное поведение, поскольку все должно быть легким.
Откройте для себя еще больше удивительных возможностей Python, наслаждайтесь!
Встроенная база данных Python
Перед началом статьи хочу сказать, что еще больше полезной и нужной информации вы найдете в нашем Telegram-канале. Подпишитесь, мне будет очень приятно.
Если вы разработчик программного обеспечения, то, скорее всего, вы знакомы с невероятно легкой базой данных SQLite или даже уже использовали ее. Она содержит практически все функции реляционной базы данных и представлена всего одним файлом. На официальном сайте можно найти несколько сценариев применения SQLite:

встроенные устройства и интернет вещей;
анализ данных;
передача данных;
архив файлов и/или контейнер данных;
внутренние или временные базы данных;
замена корпоративной базы данных в период демо-версий или тестирования;
обучение и тестирование;
экспериментальные расширения языка SQL.

Если вам нужна SQLite для каких-либо других целей, то обратитесь к документации.
Но самое главное — SQLite встроена в библиотеку Python. То есть вам не нужно устанавливать серверное или клиентское ПО и поддерживать работу какого-либо сервиса. Если вы импортировали библиотеку в Python и приступили к работе, значит вы уже используете систему управления реляционными базами данных!
Импортирование и использование
«Встроенность» предполагает, что вам не нужно запускать pip install для получения библиотеки. Просто импортируйте ее с помощью:
import sqlite3 as sl
Создание соединения с БД
Не беспокойтесь о драйверах, строках подключения и т.д. Вы можете создать базу данных SQLite и задать такой простой объект подключения, как:
После запуска этой строки кода происходит создание с БД и активируется подключение к ней. Дело в том, что базы данных, к которой мы просим подключиться Python, не существует, поэтому он автоматически создает пустую. Также мы можем ввести точно такой же код для подключения к уже существующей базе данных.
Создание таблицы
Теперь создадим таблицу:
with con: con.execute(""" CREATE TABLE USER ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER ); """)
Мы добавили три столбца в таблицу USER. Как видите, SQLite действительно легка и при этом поддерживает все основные функции обычной реляционной СУБД, такие как тип данных, обнуляемый тип, первичный ключ и автоинкремент.
После запуска этого кода создается таблица, но она ничего не выводит.
Включение записей
Вставим несколько записей в только что созданную таблицу USER, чтобы доказать, что она действительно создана.
Предположим, мы хотим вставить сразу несколько записей. Выполним:
sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)' data = [ (1, 'Alice', 21), (2, 'Bob', 22), (3, 'Chris', 23) ]
Определяем оператор SQL с вопросительными знаками ? в качестве заполнителя. Теперь создадим образцы данных для вставки, а затем вставим их с помощью объекта подключения:
with con: 
con.executemany(sql, data)
После запуска кода не появилось никаких предупреждений, значит все прошло успешно.
Запрос к таблице
Пришло время удостовериться, что все сделано правильно. Выполним запрос к таблице на возврат образцов строк.
with con: data = con.execute("SELECT * FROM USER WHERE age <= 22") for row in data: print(row)


Как видите, все очень просто!
Более того, несмотря на свою легкость SQLite является широко используемой базой данных, и большинство программного обеспечения клиентов SQL ее поддерживает.
Чаще всего я использую инструмент DBeaver. Рассмотрим его на примере.
Подключение к базе данных SQLite из клиента SQL (DBeaver)
Поскольку я использую Google Colab, я буду загружать файл my-test.db на свой компьютер. При запуске Python на локальном компьютере можно использовать клиент SQL для прямого подключения к файлу баз данных.
Создаем новое соединение в DBeaver и выбираем SQLite в качестве типа БД:
Затем переходим к файлу БД:
Теперь к базе данных можно выполнить любой SQL-запрос, как и в любых других реляционных БД:
Непрерывная интеграция с Pandas
Но это еще не все. Дело в том, что, являясь встроенной функцией Python, SQLite может легко интегрироваться с фреймом данных Pandas.
Определяем фрейм данных:
df_skill = pd.DataFrame(< 'user_id': [1,1,2,2,3,3,3], 'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning'] >)
Затем просто вызываем метод фрейма данных to_sql(), чтобы сохранить его в базе данных:
И это все, что нужно сделать! Вам даже не придется создавать таблицу заранее — типы данных и длина столбцов будут определены автоматически. Конечно, при желании вы также можете определить ее заранее.
Допустим, мы хотим объединить таблицу USER и SKILL и прочитать результат во фрейме данных Pandas. Это тоже можно выполнить без проблем.
df = pd.read_sql(''' SELECT s.user_id, u.name, u.age, s.skill FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id ''', con)
Результаты запишем в новую таблицу под названием USER_SKILL:
Теперь мы также можем использовать клиент SQL для получения таблицы:
В Python есть множество скрытых сюрпризов. Но скрыты они не специально: дело лишь в том, что в Python настолько много функций «из коробки», что невозможно раскрыть их все сразу.
В этой статье мы узнали, как использовать встроенную библиотеку Python sqlite3 для создания таблиц и манипулирования ими в базе данных SQLite. Конечно, она также поддерживает обновление и удаление, которые вы можете попробовать самостоятельно.
Но самое главное, мы можем легко прочитать таблицу из базы данных SQLite во фрейме данных Pandas и наоборот. Такая возможность еще больше упрощает взаимодействие с этой легкой реляционной базой данных.
Возможно, вы заметили, что в SQLite нет аутентификации. С ней бы данная библиотека перестала быть такой легкой.
Открывайте для себя еще больше удивительных возможностей Python!
Весь код из этой статьи находится в моем Google Colab Notebook.
Работа с базой данных из Python–приложения
Далее в лекции на конкретных примерах будет показано, как работать с базой данных из программы на языке Python. Нужно отметить, что здесь не ставится цели постичь премудрости языка запросов (это тема отдельного курса). Простые примеры позволят понять, что при программировании на Python доступ к базе данных не сложнее доступа к другим источникам данных (файлам, сетевым объектам).
Именно поэтому для демонстрации выбрана СУБД SQLite, работающая как под Unix, так и под Windows. Кроме установки собственно SQLite (сайт http://sqlite.org) и модуля сопряжения с Python (http://pysqlite.org), каких–либо дополнительных настроек проводить не требуется, так как SQLite хранит данные базы в отдельном файле: сразу приступать к созданию таблиц, занесению в них данных и произведению запросов нельзя. Выбранная СУБД (в силу своей «легкости») имеет одну существенную особенность: за одним небольшим исключением, СУБД SQLite не обращает внимания на типы данных (она хранит все данные в виде строк), поэтому модуль расширения sqlite для Python проделывает дополнительную работу по преобразованию типов. Кроме того, СУБД SQLite поддерживает достаточно большое подмножество свойств стандарта SQL92, оставаясь при этом небольшой и быстрой, что немаловажно, например, для web–приложений. Достаточно сказать, что SQLite поддерживает даже транзакции.
Еще раз стоит повторить, что выбор учебной базы данных не влияет на синтаксис использованных средств, так как модуль sqlite, который будет использоваться, поддерживает DB-API 2.0, а значит, переход на любую другую СУБД потребует минимальных изменений в вызове функции connect() и, возможно, использования более удачных типов данных, свойственных целевой СУБД.
Схематично работа с базой данных может выглядеть примерно так:
• Подключение к базе данных (вызов connect() с получением объекта–соединения).
• Создание одного или нескольких курсоров (вызов метода объекта–соединения cursor() с получением объекта–курсора).
• Исполнение команды или запроса (вызов метода execute() или его вариантов).
• Получение результатов запроса (вызов метода fetchone() или его вариантов).
• Завершение транзакции или ее откат (вызов метода объекта–соединения commit() или rollback()).
• Когда все необходимые транзакции произведены, подключение закрывается вызовом метода close() объекта–соединения.
Читать:
Как подключить компьютер к телевизору без проводов через вай фай