[Перевод] А вы знаете о том, что в Python есть встроенная СУБД?10.08.2020 16:47

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

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


В документации к SQLite можно найти и другие причины использования этой СУБД.

mltcomn5j5fcaflxxobxltxr05q.png

Данный материал посвящён использованию 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 из предыдущего примера), для того чтобы к ней подключиться, достаточно воспользоваться точно таким же кодом.

b7ca59419942cf41a5d4213da467d70e.png


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

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


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

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 <= 22")
    for row in data:
        print(row)


6d6d28ad88003fd278acc6e45f9ebc2d.png


Результат выполнения запроса к БД

Как видите, то, что было нужно, получить удалось. И сделать это было очень просто.

Кроме того, даже хотя SQLite — простая СУБД, она отличается крайне широкой поддержкой. Поэтому с ней можно работать, используя большинство SQL-клиентов.

Я пользуюсь DBeaver. Предлагаю взглянуть на то, как это выглядит.

Подключение к базе данных SQLite из SQL-клиента (DBeaver)


Я пользуюсь облачным сервисом Google Colab и хочу загрузить файл my-test.db на свой компьютер. Если же вы экспериментируете с SQLite на компьютере, то это значит, что вы, без необходимости скачивать откуда-то файл базы данных, можете подключиться к ней, используя SQL-клиент.

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

7c77ddda78195d93a0df6b90a39a17d2.png


Подготовка подключения в DBeaver

Затем надо найти файл базы данных.

0715ce8e9cc3f6a889ffe91ac7186d13.png


Подключение файла базы данных

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

6808245bcb67f018fbaf50ea93972ab8.png


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

Интеграция с 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']
})


6306c562174d3208f6006a9d39d2642a.png


Датафрейм 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)


2638c827830d2b0fcc03867cab47bdf9.png


Чтение данных из БД в датафрейм pandas

Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем USER_SKILL: 

df.to_sql('USER_SKILL', con)


С этой таблицей, конечно, можно работать и пользуясь SQL-клиентом.

deb7b957f04b37d76e1a76e295a29158.png


Применение SQL-клиента для работы с базой данных

Итоги


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

Здесь я рассказал о том, как использовать встроенную в Python библиотеку sqlite3 для создания баз данных и для работы с ними. Конечно, такие БД поддерживают не только операцию добавления данных, но и операции изменения и удаления информации. Полагаю, вы, узнав о sqlite3, испытаете всё это сами.

Очень важно то, что SQLite отлично стыкуется с pandas. Данные из БД очень легко считывать, помещая в датафреймы. Не менее проста и операция по сохранению содержимого датафреймов в базу данных. Это ещё сильнее упрощает использование SQLite.

Предлагаю всем, кто дочитал до этого места, заняться собственными исследованиями в поиске интересных возможностей Python!

Код, который я демонстрировал в этой статье, можно найти здесь.

Пользуетесь ли вы SQLite в своих Python-проектах?

oug5kh6sjydt9llengsiebnp40w.png

© Habrahabr.ru