Pandas - это библиотека Python, предоставляющая широкие возможности для анализа данных. С ее помощью очень удобно загружать, обрабатывать и анализировать табличные данные. В современном мире огромные массивы данных хранятся в базах данных. В курсе OpenDataScience во практически во всех домашних заданиях и семенарах данные загружаются из csv файлов. Я в своей статье попробую приоткрыть тему доступа к данным находящихся в популярных реляционных СУБД. Чтобы использовать данные на полную катушку, нужно обладать знаниями языка SQL. Даже если вы не знакомы с SQL, думаю, не составит труда разобраться с данным материалом. Не хотел браться за написание данного материала т.к. мне он представляется достаточно тревиальным. Но видя уже вторую ссесию подряд данный вопрос в возможных темах для тьюториалов и видя непонимание в глазах некоторых IT-шников, глядящих на какой нибудь не сильно сложный join таки решился на это. Надеюсь, данная работа кому-нибудь таки пригодится.
Python достаточно давно и хорошо работает с различными СУБД посредством модулей, реализующими работу с конкретными базами данных. Чтобы пользователю не нужно было разбираться в деталях реализации той или оной СУБД для python был разработан программный интерфейс DB-API. Python DB-API – это не конкретная библиотека, а набор правил, которым подчиняются отдельные модули, реализующие работу с конкретными базами данных. Отдельные нюансы реализации для разных баз могут отличаться, но общие принципы позволяют использовать один и тот же подход при работе с разными базами данных.
В pandas встроена поддержка SQLAlchemy которая представляет нам еще более удобный механизм доступа к данным в базе. SQLAlchemy Engine это слой абстракции над DB-API. Он содержит DB-API драйвера для разных СУБД, их можно указать в строке подключения. Engine.execute() и Engine.connect() два основных метода. Так же у него есть свой пул соединений. create_engine() - фабричная функция для создания Engine.
Т.е. для нас вся работа с СУБД сводится к созданию engine к нужной базе и всё. А далее методами pandas to_sql - сохраняем данные в базу, а методом -получаем данные из базы.
Обратимся к документации pandas где указаны примеры подключения к БД:
#from sqlalchemy import create_engine
#engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')
#engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
#engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
#engine = create_engine('mssql+pyodbc://mydsn')
# sqlite://<nohostname>/<path>
# where <path> is relative:
#engine = create_engine('sqlite:///foo.db')
# or absolute, starting with a slash:
#engine = create_engine('sqlite:////absolute/path/to/foo.db')
from __future__ import (absolute_import, division,
print_function, unicode_literals)
# отключим предупреждения
import warnings
warnings.simplefilter('ignore')
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
Используем датасет mlbootcamp5 из первых домашних работ '../../data/mlbootcamp5_train.csv'
df = pd.read_csv('../../data/mlbootcamp5_train.csv', sep=';', index_col='id')
df.head()
age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
id | ||||||||||||
0 | 18393 | 2 | 168 | 62.0 | 110 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
4 | 17474 | 1 | 156 | 56.0 | 100 | 60 | 1 | 1 | 0 | 0 | 0 | 0 |
Сохраним данные из датафрейма df в MySQL базу:
# для того, чтобы воспользоваться данной СУБД у Вас в системе должен стоять драйвер,
#на моей системе он ставился следующей командой:
#apt-get install python3-mysqldb
# хост БД: localhost
#пользователь БД testuser
#пароль: 321
# БД: edu
mysql_conn = create_engine('mysql+mysqldb://testuser:321@localhost:3306/edu')
# сохраним наш датафрейм df в базу в таблицу train:
df.to_sql(name='train', con=mysql_conn,if_exists='replace',index=True, index_label ='id_s')
таблица в которую мы пишем может уже существовать в БД, поэтому мы должны определить поведение системы с помощью параметра if_exists
if_exists : {'fail', 'replace', 'append'}, default 'fail'
-параметр if_exists может принимать следующие 3 значения:
fail:- If table exists, do nothing. - ничего не делать, если таблица существует,
replace-: If table exists, drop it, recreate it, and insert data. -если таблица существует пересоздать её и вставить данные,
append-: If table exists, insert data. Create if does not exist. - если таблица существует, то дописать данные в конец. Если таблица не существует, создать её.
A теперь вытащим данные из таблицы sql запросом. Например, создадим новый датафрейм со всеми людьми имеющими сердечно-сосудистые заболения
sql='SELECT * FROM train WHERE cardio=1'
mysql_df = pd.read_sql_query(sql, mysql_conn)
mysql_df.head()
id_s | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
1 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
2 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
3 | 12 | 22584 | 2 | 178 | 95.0 | 130 | 90 | 3 | 3 | 0 | 0 | 1 | 1 |
4 | 24 | 16782 | 2 | 172 | 112.0 | 120 | 80 | 1 | 1 | 0 | 0 | 0 | 1 |
#сравним с исходным df
print(mysql_df.shape, df[df['cardio']==1].shape)
(34979, 13) (34979, 12)
Как видим, оба датафрейма вовзращают одинаковое кол-во строк 34979 только вот кол-во столбцов отличается на 1 т.к. при добавлении данных в таблицу также, по умолчанию, добавляется столбец с индексом. Это поведение можно изменить добавив параметр index=False при вызове функции to_sql.
Давайте убедимся, что независимо от реализации СУБД работа с ней в pandas не меняется. Сделаем тоже самое уже для PostgreSQL:
# для того, чтобы воспользоваться данной СУБД у Вас в системе должен стоять драйвер psycopg2,
#на моей системе он ставился следующей командой:
#!pip3 install psycopg2
# хост БД: localhost
#пользователь БД testuser
#пароль: 4321
# БД: edu
pgsql_conn = create_engine('postgresql://testuser:4321@localhost:5432/edu')
#пишем в базу:
df.to_sql(name='train', con=pgsql_conn,if_exists='replace')
# достаём из базы:
sql='SELECT * FROM train WHERE cardio=1'
pgsql_df = pd.read_sql_query(sql, pgsql_conn)
pgsql_df.head()
id | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
1 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
2 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
3 | 12 | 22584 | 2 | 178 | 95.0 | 130 | 90 | 3 | 3 | 0 | 0 | 1 | 1 |
4 | 24 | 16782 | 2 | 172 | 112.0 | 120 | 80 | 1 | 1 | 0 | 0 | 0 | 1 |
# всё тоже самое, ничего нового:
print(mysql_df.shape, pgsql_df.shape , df[df['cardio']==1].shape)
(34979, 13) (34979, 13) (34979, 12)
Работа с mySQL и c PostgreSQL требует наличия соответствующих СУБД, созданных в них баз данных, пользователей к БД, установленных драйверов СУБД. Уверен, что большинству, как и мне, было бы лень ставить соответствующий софт, чтобы погонять игрушечные примеры. Для таких случает есть "лайтовый" вариант SQLite - компактная встраиваемая СУБД. Данная СУБД может создаваться "на лету" в виде файла. Поддержка SQLite встроена в python и не потребует установки отдельных драйверов.
#создадим новую базу и коннект к ней
sqlite_conn = create_engine('sqlite:///../../data/mybase.db')
#пишем в базу:
df.to_sql(name='train', con=sqlite_conn,if_exists='replace')
# достаём из базы:
sql='SELECT * FROM train WHERE cardio=1'
sqlite_df = pd.read_sql_query(sql, sqlite_conn)
sqlite_df.head()
id | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
1 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
2 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
3 | 12 | 22584 | 2 | 178 | 95.0 | 130 | 90 | 3 | 3 | 0 | 0 | 1 | 1 |
4 | 24 | 16782 | 2 | 172 | 112.0 | 120 | 80 | 1 | 1 | 0 | 0 | 0 | 1 |
# сравним выдачу, ничего нового:
print(mysql_df.shape, pgsql_df.shape , sqlite_df.shape ,df[df['cardio']==1].shape)
(34979, 13) (34979, 13) (34979, 13) (34979, 12)
Список подддерживаемых SQLAlchemy диалектов СУБД можно посмотреть здесь, а это практически все популярные СУБД.
Для разнообразия, воспользуемся встроенным в python модулем для работы с SQLite базами
import sqlite3
# создадим новую БД:
db='../../data/mybase.sqlite'
conn = sqlite3.connect(db)
# запишем в неё весь датафрейм df:
df.to_sql("train", conn, if_exists="replace", index=False)
sql = "SELECT * FROM train"
df_sql = pd.read_sql_query(sql, conn)
df_sql.head()
age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 18393 | 2 | 168 | 62.0 | 110 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
4 | 17474 | 1 | 156 | 56.0 | 100 | 60 | 1 | 1 | 0 | 0 | 0 | 0 |
# сверим размерности
print (df.shape ,df_sql.shape)
(70000, 12) (70000, 12)
Теперь можно манипулировать данными не используя синтаксис pandas, а используя более привычный, по крайней мере для меня, sql-синтаксис. Составим запрос:
sql_string = 'SELECT age,'
sql_string += 'age/365.25 as years, ' # преобразуем возраст в года
sql_string += 'cast(age/365.25 as int) as years_full, ' # а также посчитаем число полных лет
# значения пола 1 и 2 как то не привычно. Переделаем на "классическое" представление :
sql_string += 'CASE WHEN gender=2 THEN 1 ELSE 0 END as gender_b , ' #1-мужской, и 0 -женский
sql_string += 'CASE WHEN gender=2 THEN upper("m") ELSE "W" END as gender_chr , ' # ну или в символьное M/W
sql_string += 'height, weight, ap_hi, ap_lo, cholesterol, gluc, smoke,alco, active, cardio ' # добавим остальные поля
sql_string += ' FROM train ' # укажем таблицу
sql_string += ' WHERE ap_hi> ap_lo ' # отфильтруем ошибочные данные там где нижнее давление больше или равно верхнему.
sql_string += ' ORDER BY cast(age/365.25 as int) , cholesterol ' # упорядочим выборку по кол-ву полных лет и уровню холестерина
print(sql_string) # получился такой вот sql запрос
SELECT age,age/365.25 as years, cast(age/365.25 as int) as years_full, CASE WHEN gender=2 THEN 1 ELSE 0 END as gender_b , CASE WHEN gender=2 THEN upper("m") ELSE "W" END as gender_chr , height, weight, ap_hi, ap_lo, cholesterol, gluc, smoke,alco, active, cardio FROM train WHERE ap_hi> ap_lo ORDER BY cast(age/365.25 as int) , cholesterol
# теперь результатом его выполнения можно использовать как dataframe
df_new = pd.read_sql_query(sql_string, conn)
df_new.head(10)
age | years | years_full | gender_b | gender_chr | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 10878 | 29.782341 | 29 | 0 | W | 175 | 59.0 | 120 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
1 | 10798 | 29.563313 | 29 | 1 | M | 175 | 92.0 | 100 | 60 | 1 | 1 | 0 | 0 | 1 | 0 |
2 | 10859 | 29.730322 | 29 | 0 | W | 159 | 59.0 | 120 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
3 | 10964 | 30.017796 | 30 | 0 | W | 160 | 59.0 | 110 | 70 | 1 | 1 | 0 | 0 | 1 | 0 |
4 | 14532 | 39.786448 | 39 | 1 | M | 181 | 95.0 | 130 | 90 | 1 | 1 | 1 | 1 | 1 | 0 |
5 | 14574 | 39.901437 | 39 | 1 | M | 166 | 66.0 | 120 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
6 | 14605 | 39.986311 | 39 | 0 | W | 159 | 60.0 | 110 | 70 | 1 | 1 | 0 | 0 | 1 | 0 |
7 | 14499 | 39.696099 | 39 | 1 | M | 164 | 48.0 | 110 | 70 | 1 | 2 | 1 | 1 | 1 | 1 |
8 | 14321 | 39.208761 | 39 | 1 | M | 173 | 75.0 | 110 | 70 | 1 | 1 | 0 | 0 | 1 | 0 |
9 | 14493 | 39.679671 | 39 | 0 | W | 167 | 66.0 | 110 | 70 | 1 | 1 | 0 | 0 | 1 | 0 |
# проверим размерности отфильтрованных данных:
print (df[df['ap_hi']>df['ap_lo']].shape ,df_new.shape)
(68764, 12) (68764, 15)
как мы видим в выборке у нас появились новые столбцы. А также кол-во записей уменьшилось из-за того что мы отбросили некорректные данные. Ну и на последок еще одни пример на JOIN, собственно то, ради чего и нужен sql.
# перезапишем таблицу с исходной выборкой train добавим в нее весь датаблок, а также добавим столбец с индексом
df.to_sql("train", conn, if_exists="replace", index=True)
sql = "SELECT * FROM train"
df_sql = pd.read_sql_query(sql, conn)
df_sql.head()
id | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 18393 | 2 | 168 | 62.0 | 110 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
1 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
2 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
3 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
4 | 4 | 17474 | 1 | 156 | 56.0 | 100 | 60 | 1 | 1 | 0 | 0 | 0 | 0 |
# создадим новую таблицу 'x' куда попадут все столбцы кроме cardio
sql = "SELECT id, age, gender, height, weight, ap_hi, ap_lo,cholesterol, gluc, smoke, alco, active FROM train"
df_sql2 = pd.read_sql_query(sql, conn)
df_sql2.to_sql("x", conn, if_exists="replace", index=False)
df_sql2.head()
id | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 18393 | 2 | 168 | 62.0 | 110 | 80 | 1 | 1 | 0 | 0 | 1 |
1 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 |
2 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 |
3 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 |
4 | 4 | 17474 | 1 | 156 | 56.0 | 100 | 60 | 1 | 1 | 0 | 0 | 0 |
# создадим новую таблицу 'y' в котором будут только столбцы id и cardio, при этом отфильтруем только те записи, где cardio=1
sql = "SELECT id, cardio FROM train WHERE cardio=1"
df_sql3 = pd.read_sql_query(sql, conn)
df_sql3.to_sql("y", conn, if_exists="replace", index=False)
df_sql3.head()
id | cardio | |
---|---|---|
0 | 1 | 1 |
1 | 2 | 1 |
2 | 3 | 1 |
3 | 12 | 1 |
4 | 24 | 1 |
#сравним размерности новых таблиц
print(df_sql2.shape,df_sql3.shape)
(70000, 12) (34979, 2)
Как видим, они не совпадают по кол-ву строк. И восстановить данные средствами pandas было бы проблемматично. А средствами sql это сделать достаточно легко. Нужно просто написать соответствующий запрос:
sql='SELECT x.*, COALESCE(y.cardio,0) as cardio FROM x LEFT JOIN y ON x.id=y.id' # вот и весь запрос.
df_result = pd.read_sql_query(sql, conn)
df_result.to_sql("new_train", conn, if_exists="replace", index=False) # сохраним данные в таблицу new_train
df_result.head()
id | age | gender | height | weight | ap_hi | ap_lo | cholesterol | gluc | smoke | alco | active | cardio | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 18393 | 2 | 168 | 62.0 | 110 | 80 | 1 | 1 | 0 | 0 | 1 | 0 |
1 | 1 | 20228 | 1 | 156 | 85.0 | 140 | 90 | 3 | 1 | 0 | 0 | 1 | 1 |
2 | 2 | 18857 | 1 | 165 | 64.0 | 130 | 70 | 3 | 1 | 0 | 0 | 0 | 1 |
3 | 3 | 17623 | 2 | 169 | 82.0 | 150 | 100 | 1 | 1 | 0 | 0 | 1 | 1 |
4 | 4 | 17474 | 1 | 156 | 56.0 | 100 | 60 | 1 | 1 | 0 | 0 | 0 | 0 |
# ни одной строчки не потеряли:
print(df_result.shape)
(70000, 13)
Проверим что столбец cardio в обеих таблицах идентичен
sql='SELECT train.ID, train.cardio,new_train.cardio FROM train INNER JOIN new_train ON train.id=new_train.id WHERE train.cardio=new_train.cardio' # вот и весь запрос.
df_compare = pd.read_sql_query(sql, conn)
print(df_compare.shape) # получаем 70000 строк т.е. таблицы идентичны
(70000, 3)
df_compare.head()
id | cardio | cardio | |
---|---|---|---|
0 | 0 | 0 | 0 |
1 | 1 | 1 | 1 |
2 | 2 | 1 | 1 |
3 | 3 | 1 | 1 |
4 | 4 | 0 | 0 |
На этом хочу поставить точку.