Работа с базой данных: модуль sqlite3

Если вы разработчик программного обеспечения, то вы, скорее всего, слышали или даже использовали легковесную базу данных — SQLite. Она обладает всеми необходимыми функциями реляционной базы данных, но все сохраняется в одном файле. На официальном сайте представлены несколько сценариев использования SQLite:

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

Есть и другие сценарии использования SQLite, с ними вы можете ознакомиться в документации.

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


Импорт и использование

Когда мы говорим «встроенная», это означает, что вам даже не нужно запускать pip install, чтобы получить библиотеку. Просто импортируйте ее:

import sqlite3 as sl

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

Не беспокойтесь о драйверах, строках подключения и так далее. Вы можете создать базу данных SQLite и получить объект подключения к ней (Connection) следующим образом:

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

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

модуль sqlite3

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

Далее давайте создадим таблицу.

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 <= 22")
    for row in data:
        print(row)
модуль sqlite3

Как видите, все очень просто.

Кроме того, хотя 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, наслаждайтесь!