Чтение и запись SQL-файлов в Pandas

Обзор SQL и Pandas

SQL (язык структурированных запросов) — это язык программирования, используемый для управления реляционными базами данных и манипулирования ими. С другой стороны, Pandas — это библиотека Python, используемая для манипулирования и анализа данных.

Анализ данных предполагает работу с большими объемами данных, и для хранения этих данных часто используются базы данных. SQL и Pandas предоставляют мощные инструменты для работы с базами данных, позволяющие аналитикам данных эффективно извлекать, манипулировать и анализировать данные. Используя эти инструменты, аналитики данных могут получить ценную информацию из данных, которые в противном случае было бы трудно получить.

В этой статье мы рассмотрим, как использовать SQL и Pandas для чтения и записи в базу данных.

Подключение к БД

Установка библиотек

Сначала мы должны установить необходимые библиотеки, прежде чем мы сможем подключиться к базе данных SQL с помощью Pandas. Требуются две основные библиотеки: Pandas и SQLAlchemy.

Pandas — это популярная библиотека манипулирования данными, которая, как упоминалось во введении, позволяет хранить большие структуры данных. Напротив, SQLAlchemy предоставляет API для подключения к базе данных SQL и взаимодействия с ней.

Мы можем установить обе библиотеки с помощью менеджера пакетов Python pip, выполнив следующие команды в командной строке.

$ pip install pandas
$ pip install sqlalchemy

Установление связи

Установив библиотеки, мы теперь можем использовать Pandas для подключения к базе данных SQL.

Для начала мы создадим объект механизма SQLAlchemy с расширением create_engine(). Функция create_engine() подключает код Python к базе данных. В качестве аргумента он принимает строку подключения, в которой указывается тип базы данных и сведения о соединении. В этом примере мы будем использовать тип базы данных SQLite и путь к файлу базы данных.

Создайте объект механизма для базы данных SQLite, используя приведенный ниже пример:

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

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

engine = create_engine('sqlite:///student.db')

Чтение SQL-файлов с помощью Pandas

Давайте прочитаем данные теперь, когда мы установили соединение. В этом разделе мы рассмотрим функции read_sql, read_sql_table и read_sql_query и то, как их использовать для работы с базой данных.

Выполнение SQL-запросов с использованием функции read_sql() Panda 

Это read_sql() функция библиотеки Pandas, которая позволяет нам выполнять SQL-запрос и получать результаты в кадр данных Pandas. Функция read_sql() соединяет SQL и Python, позволяя нам воспользоваться возможностями обоих языков. Функция оборачивает read_sql_table() и read_sql_query(). Функция read_sql() внутренне маршрутизируется на основе предоставленных входных данных. Это означает, что если входные данные предназначены для выполнения SQL-запроса, они будут перенаправлены в read_sql_query(), а если это таблица базы данных, они будут перенаправлены в read_sql_table().

Синтаксис read_sql() следующий:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Обязательны параметры SQL и con; остальные не являются обязательными. Однако мы можем манипулировать результатом, используя эти необязательные параметры. Давайте подробнее рассмотрим каждый параметр.

• sql: имя SQL-запроса или таблицы базы данных.
• con: объект подключения или URL-адрес подключения.
• index_col: этот параметр позволяет нам использовать один или несколько столбцов из результата запроса SQL в качестве индекса фрейма данных. Он может принимать либо один столбец, либо список столбцов.
• coerce_float: этот параметр указывает, следует ли преобразовывать нечисловые значения в числа с плавающей запятой или оставить их в виде строк. По умолчанию установлено значение true. Если возможно, он преобразует нечисловые значения в типы с плавающей запятой.
• params: параметры предоставляют безопасный метод передачи динамических значений в запрос SQL. Мы можем использовать параметр params для передачи словаря, кортежа или списка. В зависимости от базы данных синтаксис параметров различается.
• parse_dates: это позволяет нам указать, какой столбец в результирующем кадре данных будет интерпретироваться как дата. Он принимает один столбец, список столбцов или словарь с ключом в качестве имени столбца и значением в качестве формата столбца.
• columns: это позволяет нам выбирать только выбранные столбцы из списка.
• chunksize: при работе с большим набором данных важен размер фрагмента. Он извлекает результат запроса меньшими порциями, повышая производительность.

Вот пример того, как использовать read_sql():

Код:

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

# Fetch all records from Student table and manipulate the result
df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype)

# Close the Database connection
engine.dispose()

Вывод:

           firstName lastName                  email  dateOfBirth
rollNumber
1               Mark   Simson   marksimson@gmail.com   2000-02-23
2              Peter  Griffen petergriffen@gmail.com   2001-04-15
3                Meg  Aniston   meganiston@gmail.com   2001-09-20
Date type of dateOfBirth: datetime64[ns]

После подключения к базе данных выполняем запрос, который возвращает все записи из таблицы Studentи сохраняет их в DataFrame df. Столбец «Roll Number» преобразуется в индекс с помощью этого index_colпараметра, а тип данных «dateOfBirth» — «datetime64[ns]» из-за parse_dates. Мы можем использовать его read_sql()не только для получения данных, но и для выполнения других операций, таких как вставка, удаление и обновление. read_sql()является общей функцией.

Загрузка определенных таблиц или представлений из БД

Загрузка определенной таблицы или представления с помощью Pandas read_sql_table()— это еще один метод чтения данных из базы данных в кадр данных Pandas.

Что такое read_sql_table ?

Библиотека Pandas предоставляет read_sql_tableфункцию, которая специально разработана для чтения всей таблицы SQL без выполнения каких-либо запросов и возврата результата в виде кадра данных Pandas.

Синтаксис read_sql_table() следующий:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

За исключением table_name и схемы, параметры объясняются так же, как и read_sql().

• table_name: параметр table_name представляет собой имя таблицы SQL в базе данных.
• schema: этот необязательный параметр представляет собой имя схемы, содержащей имя таблицы.

После создания соединения с базой данных мы воспользуемся read_sql_tableфункцией для загрузки Student таблицы в DataFrame Pandas.

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

# Load Student Table from database
df = pd.read_sql_table('Student', engine)
print(df.head())

# Close the Database connection
engine.dispose()

Вывод:

   rollNumber firstName lastName                  email  dateOfBirth
0           1      Mark   Simson   marksimson@gmail.com   2000-02-23
1           2     Peter  Griffen petergriffen@gmail.com   2001-04-15
2           3       Meg  Aniston   meganiston@gmail.com   2001-09-20

Предположим, что это большая таблица, которая может занимать много памяти. Давайте рассмотрим, как мы можем использовать этот chunksizeпараметр для решения этой проблемы.

Код:

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

# Load student table from database
df_iterator = pd.read_sql_table('Student', engine, chunksize = 1)

# Iterate the dataframe
for df in df_iterator:
  print(df.head())

# Close the Database connection
engine.dispose()

Вывод:

   rollNumber firstName lastName                  email  dateOfBirth
0           1      Mark   Simson   marksimson@gmail.com   2000-02-23
0           2     Peter  Griffen petergriffen@gmail.com   2001-04-15
0           3       Meg  Aniston   meganiston@gmail.com   2001-09-20

Имейте в виду, что chunksizeя использую здесь значение 1, потому что в моей таблице только 3 записи.

Непосредственный запрос к БД с помощью синтаксиса SQL Pandas

Извлечение информации из базы данных является важной частью для аналитиков данных и ученых. Для этого мы воспользуемся этой read_sql_query() функцией.

Что такое read_sql_query()?

Используя функцию Pandas read_sql_query(), мы можем запускать SQL-запросы и получать результаты непосредственно в DataFrame. Функция read_sql_query()создана специально для SELECTоператоров. Его нельзя использовать для каких-либо других операций, таких как DELETEили UPDATE.

Синтаксис:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

Все описания параметров такие же, как и у read_sql()функции. Вот пример read_sql_query():

Код:

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

# Query the Student table
df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df)

# Close the Database connection
engine.dispose()

Вывод:

   firstName lastName
0       Mark   Simson

Написание SQL-файлов с помощью Pandas

Предположим, что при анализе данных мы обнаружили, что необходимо изменить несколько записей или требуется новая таблица или представление с данными. Чтобы обновить или вставить новую запись, можно использовать read_sql()и написать запрос. Однако этот метод может быть длительным. Pandas предоставляет отличный метод, предназначенный to_sql()для подобных ситуаций.

В этом разделе мы сначала создадим новую таблицу в базе данных, а затем отредактируем существующую.

Создание новой таблицы в базе данных SQL

Прежде чем создавать новую таблицу, давайте сначала обсудим ее to_sql() подробно.

Что такое to_sql() ?

Функция to_sql() библиотеки Pandas позволяет нам писать или обновлять базу данных. Функция to_sql() может сохранять данные DataFrame в базе данных SQL.

Синтаксис для to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Только параметры name и con обязательны для запуска to_sql(); однако другие параметры обеспечивают дополнительную гибкость и возможности настройки. Давайте подробно обсудим каждый параметр:

• name: имя таблицы SQL, которую необходимо создать или изменить.
• con: объект подключения к базе данных.
• schema: Схема таблицы (необязательно).
• if_exists: Значение этого параметра по умолчанию — «не выполнено». Этот параметр позволяет нам решить, какое действие следует предпринять, если таблица уже существует. Возможные варианты: «провалить», «заменить» и «добавить».
• index: параметр index принимает логическое значение. По умолчанию для него установлено значение True, что означает, что индекс DataFrame будет записан в таблицу SQL.
• index_label: этот необязательный параметр позволяет нам указать метку столбца для индексных столбцов. По умолчанию индекс записывается в таблицу, но с помощью этого параметра можно задать конкретное имя.
• chunksize: количество строк, которые будут записываться одновременно в базу данных SQL.
• dtype: этот параметр принимает словарь с ключами в качестве имен столбцов и значениями в качестве типов данных.
• method: параметр метода позволяет указать метод, используемый для вставки данных в SQL. По умолчанию для него установлено значение «Нет», что означает, что панды найдут наиболее эффективный способ на основе базы данных. Существует два основных варианта параметров метода:
• multi: позволяет вставлять несколько строк в один SQL-запрос. Однако не все базы данных поддерживают многострочную вставку.
• Вызываемая функция : здесь мы можем написать собственную функцию для вставки и вызывать ее, используя параметры метода.

Вот пример использования to_sql():

import pandas as pd
from sqlalchemy import create_engine

# Create an engine object
engine = create_engine('sqlite:///C/SQLite/student.db')

# Create a new Dataframe that will be our new table
data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data)

# Create a new table called Customer
df.to_sql('Customer', con=engine, if_exists='fail')

# Close the Database connection
engine.dispose()

В базе данных создается новая таблица «Клиент» с двумя полями «Имя» и «Возраст».

Снимок базы данных:

Обновление существующих таблиц с помощью фреймов данных Pandas

Обновление данных в базе данных — сложная задача, особенно при работе с большими данными. Однако использование этой to_sql() функции в Pandas может значительно упростить эту задачу.

Чтобы обновить существующую таблицу в базе данных, to_sql() функцию можно использовать с if_exists параметром, установленным на «заменить». Это приведет к перезаписи существующей таблицы новыми данными.

Вот пример to_sql() обновления ранее созданной Customer таблицы. Предположим, в Customerтаблице мы хотим обновить возраст клиента по имени Пол с 9 до 10. Для этого сначала мы можем изменить соответствующую строку в DataFrame, а затем использовать функцию для обновления to_sql() базы данных.

Код:

import pandas as pd
from sqlalchemy import create_engine

# Create a connection to the SQLite database
engine = create_engine('sqlite:///C/SQLite/student.db')

# Load Customer Table into a Dataframe
df = pd.read_sql_table('Customer', engine)

# Modify the age of the customer named Paul
df.loc[df['Name'] == 'Paul', 'Age'] = 10

# Update the Customer table with the modified DataFrame
df.to_sql('Customer', con=engine, if_exists='replace')

# Close the Database connection
engine.dispose()

В базе данных обновляется возраст Павла:

Заключение

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

Библиотека Pandas упрощает манипулирование данными в кадре данных, тогда как SQL предоставляет мощный язык для запроса данных в базе данных. Использование Pandas и SQL для чтения и записи данных может сэкономить время и усилия при выполнении задач анализа данных, особенно если данные очень большие. В целом совместное использование SQL и Pandas может помочь аналитикам данных и ученым оптимизировать свой рабочий процесс.