По маршруту SQLite - Pandas: 7 основных операций

Лет десять назад, когда я занимался разработкой iOS, еще не было устоявшихся решений для работы с базами данных мобильных приложений, так что мне пришлось реализовывать свою собственную БД. Выбор пал на SQLite, упрощенный механизм для управления реляционными базами данных.

Когда я в рамках своей деятельности частично переключился на науку о данных, то с радостью узнал, что в Python также есть API для использования SQLite. Важно отметить, что pandas, ведущая библиотека для обработки данных, предоставляет соответствующие функциональности для взаимодействия с разными базами данных, в том числе и SQLite. Совместное их применение позволяет выполнять множество действий, связанных с локальным хранением и управлением данными. В данной статье мы рассмотрим ряд основных операций. 

1. Подключение к базе данных 

Для работы с базой данных SQLite воспользуемся встроенным модулем sqlite3, который предоставляет полный набор стандартных операций. В следующем примере рассмотрим способ подключения к БД: 

import sqlite3

con = sqlite3.connect("test.db")

Вызывая функцию connect мы достигаем 2 целей: 

1. Модуль подключается к базе данных test.db. Если ее не существует, он создает одноименную БД в текущей директории.  
2. Вызов данной функции создает объект Connect, представляющий БД. С этого момента все связанные с ней операции выполняются с использованием объекта Connection. 

Если вы не намерены иметь дело с физической тестовой БД, то модуль может создать базу данных в памяти, выполнив следующий код. 

con_memory = sqlite3.connect(":memory:")

В рамках данного руководства мы будем оперировать объектом con, связанным с test.db.

2. Создание новой таблицы и внесение записей 

Начнем с заполнения БД данными. Допустим, у нас должно быть 2 поля данных (имя и балл) и 4 записи, как показано ниже: 

names = ['John', 'Mike', 'Jane', 'Bella']
grades = [90, 95, 92, 98]

Следующий код показывает, как создать таблицу и соответствующим образом внести в нее записи. 

# Создаем курсор 
cur = con.cursor()

# Создаем таблицу с именем transcript 
cur.execute("CREATE TABLE transcript (name text, grade integer);")

# Вставляем записи 
cur.executemany("INSERT into transcript values (?, ?)", zip(names, grades))

# Выполняем все транзакции 
con.commit()

• Как и в случае с другими базами данных все начинается с создания курсора для выполнения инструкций SQLite. Обратите внимание, что можно использовать нестандартные методы execute и executemany непосредственно для объекта Connect, чтобы код выглядел чище. Тем не менее, руководствуясь своей внутренней логикой, Python все равно сгенерирует для вас курсор. Выбор за вами  —  создавать его явно или нет. 
• Для создания таблицы в БД SQLite применяем CREATE TABLE table_name (field0 field0_type, field1 field1_type, …). На сайте SQLite предоставлена информация о поддерживаемых типах данных. Кратко перечислим их: текст, целые числа, двоичный большой объект BLOB, позволяющий сохранять бинарные данные, и вещественный тип Real. Отметим, что SQLite не располагает логическим типом, поэтому для обозначения таких значений можно воспользоваться целыми числами 0 и 1.
• Для внесения только одной записи просто вызываем cur.execute(“INSERT into transcript values (‘John’, 90)”). Однако если в одном вызове функции нужно добавить несколько из них, то следует задействовать метод executemany. В нем шаблон инструкции SQL передается вместе с iterator, чьи элементы последовательно добавляются в шаблон.
• Чтобы отправить все транзакции для обновления базы данных, вызываем метод commit для объекта con. 

3. Запрос записей 

Аналогичным способом можно запросить записи, а именно отправить нужную инструкцию SQL, воспользовавшись методом execute. Рассмотрим пример запроса записей, упорядоченных по оценкам: 

>>> cur.execute("select * from transcript order by grade desc")
<sqlite3.Cursor object at 0x1103b5ea0>

Нельзя не заметить, что вместо ожидаемых всех записей вызов execute возвращает тот же самый объект cursor. После выполнения инструкции SELECT с курсором можно работать как с итератором. Поэтому включаем его в конструктор списка для отображения всех записей. 

>>> list(cur)
[('Bella', 98), ('Mike', 95), ('Jane', 92), ('John', 90)]

Как вариант, у объекта cursor есть встроенные методы fetchall и feathone. Первый отображает все записи, а второй  —  только одну определенную из них. Отметим, что для извлечения возвращаемого значения из вызова execute используются нижние подчеркивания. 

>>> _ = cur.execute("select * from transcript order by grade desc")
>>> cur.fetchall()
[('Bella', 98), ('Mike', 95), ('Jane', 92), ('John', 90)]
>>> _ = cur.execute("select * from transcript order by grade desc")
>>> cur.fetchone()
('Bella', 98)

4. Изменение записей 

Для обновления записей нужен следующий синтаксис инструкции SQL: update table_name set field_name=new_value, another_field=new_value where condition. Задействуем его для изменения балла Джона: 

>>> cur.execute("update transcript set grade = 100 where name = 'John'")
<sqlite3.Cursor object at 0x1103b5ea0>
>>> list(cur.execute("select * from transcript order by grade desc"))
[('John', 100), ('Bella', 98), ('Mike', 95), ('Jane', 92)]

Как видно, данная операция прошла успешно, изменив порядок имеющихся баллов. 

5. Удаление записей 

Для удаления записей предназначен вот такой синтаксис инструкции SQL: delete from table_name where condition. Важно не пропустить условие condition, иначе вы удалите все строки в таблице, что в большинстве случаев весьма не желательно. Рассмотрим пример: 

>>> _ = cur.execute("delete from transcript where name='John'")
>>> list(cur.execute("select * from transcript order by grade desc"))
[('Bella', 98), ('Mike', 95), ('Jane', 92)]

Как видно, были удалены записи, связанные с John. 

6. Чтение данных SQLite с помощью Pandas 

С pandas управление базой данных SQLite превращается в увлекательный процесс. Она предоставляет функцию read_sql, позволяющую напрямую выполнять инструкции SQL, не заботясь о внутренней инфраструктуре. 

>>> import pandas as pd
>>> df = pd.read_sql("select * from transcript", con)
>>> df
    name  grade
0   Mike     95
1   Jane     92
2  Bella     98

По сути, вызов данной функции создает DataFrame, откуда вы можете извлекать разнообразные методы, предоставляемые библиотекой pandas. 

Кто-то, возможно, сталкивался с такими функциями pandas, как read_sql_table и read_sql_query. Фактически функция read_sql  —  это обертка вокруг них. Она анализирует входные данные и вызывает соответствующую функцию. Таким образом, на повседневной основе можно просто задействовать read_sql, доверив pandas всю тяжелую работу. 

7. Обратная запись DataFrame в SQLite

После обработки данных с помощью pandas самое время осуществить обратную запись DataFrame в БД SQLite для долгосрочного хранения. На этот случай в pandas есть метод to_sql. Обратимся к соответствующему примеру: 

>>> df['gpa'] = [4.0, 3.8, 3.9]
>>> df.to_sql("transcript", con, if_exists="replace", index=False)
>>> list(cur.execute("select * from transcript order by grade desc"))
[('Bella', 98, 3.9), ('Mike', 95, 4.0), ('Jane', 92, 3.8)]

• В отличие от read_sql, функции из библиотеки pandas, to_sql является методом класса DataFrame, вследствие чего он непосредственно вызывается объектом DataFrame. 
• В методе to_sql указывается таблица, в которую сохраняется DataFrame. 
• Отметим важность параметра if_exists, так как по умолчанию ему задается значение “fail”. Это значит, что если таблица уже существует, вы не сможете записать в нее текущий DataFrame, поскольку будет вызвана ошибка ValueError. В рассматриваемом примере требуется заменить существующую таблицу из-за изменения средних баллов оценок, поэтому параметру if_exisits устанавливается значение “replace”.  
• В результате установки index=False индекс объекта DataFrame просто игнорируется при сохранении в таблицу. По своему принципу данное действие аналогично методу to_csv, с которым вы наверняка знакомы лучше. 

Заключение 

В статье был проведен обзор основных операций с базой данных SQLite при непосредственном участии встроенной библиотекой Python sqlite3. Как мы уже убедились, с помощью предоставляемых методов можно без труда выполнять стандартные операции SQL, такие как внесение, изменение и удаление записей. По сути, уже имеющиеся знания SQL во многом облегчают процесс обучения.  

Не обошли мы своим вниманием и принцип взаимодействия pandas с БД SQLite. Запомнить его легко: read_sql предназначен для извлечения данных из нее, а to_sql выгружает их туда обратно. Иначе говоря, если предположить, что вашим основным инструментом обработки данных является pandas, то относительно базы данных SQLite read_sql служит для вывода данных, а to_sql  —  для ввода.

Читайте также:

• 4 способа добавления колонок в датафреймы Pandas
• 9 первоклассных функций Pandas Python для работы с данными
• 6 упущений в курсе науки о данных

Читайте нас в Telegram, VK и Дзен

---

Перевод статьи Yong Cui: From SQLite to Pandas — 7 Essential Operations You Need to Know