Базы данных. Вы любите их, нужны им, но давайте сталкиваемся … Вы уже освоили работу с ними. Есть только столько веселье, чтобы быть в бизнесе открытия соединений базы данных, тянущие ряды, и отложив их откуда, откуда они пришли. Не было бы здорово, если бы мы могли пропустить скучные вещи и работать с данными?
Пандас и SQLALCHEMY – это матч, сделанный на небесах Python. Они индивидуально среди наиболее часто используемых библиотек Python. Вместе они больше, чем сумма их частей, благодаря встроенной интеграции SQLAlchemy Pandas.
Создать соединение SQLALCHEMY
Как вы можете себе представить, первые две библиотеки нам нужно установить, являются панды и SQLALCHEMY. Нам необходимо установить разъем баз данных в качестве нашей третьей и окончательной библиотеки, но библиотека, которая вам нужна зависит от типа базы данных, к которой вы подключаетесь. Если вы подключаетесь к MySQL, я рекомендую устанавливать Pymysql ( PIP Установка PymySQL ). Если вы подключаетесь к Postgres, иди с Psycopg2 ( PIP Установите PSYCOPG2 ). Единственный раз, когда мы будем использовать любой из этих библиотек, заключается в том, когда мы устанавливаем соединение базы данных с SQLALCHEMY.
SQLALCHEMY URIS.
URI (или строка подключения), просто строка, содержащая информацию, необходимую для подключения к чему-то вроде базы данных. Вот пример:
postgres+psycopg2://myuser:mypassword@hackersdb.example.com:5432/mydatabase
Первая часть нашей строки – postgres + psycop2 , что является комбинацией нашей целевой базы данных и нашего разъема. Если вы подключаетесь к MySQL, замените это с помощью mysql + pymysql Отказ В случае, если остальная часть URI не является ошибкой, вот разбивка каждой части этой строки:
[DB_FLAVOR]+[DB_PYTHON_LIBRARY]://[USERNAME]:[PASSWORD]@[DB_HOST]:[PORT]/[DB_NAME]
SQLalchemy двигатели
«Двигатель» – это объект, используемый для подключения к базам данных с использованием информации на нашем URI. Как только мы создам двигатель, загрузка и загрузка данных так же просто, как передавать этот объект к Pandas:
from os import environ
from sqlalchemy import create_engine
db_uri = environ.get('SQLALCHEMY_DATABASE_URI')
self.engine = create_engine(db_uri, echo=True)
Помимо принятия URI, create_engine () может принять несколько дополнительных кварг. Я решил установить эхо = Правда , который будет регистрировать все запросы нашу базу данных SQL выполнять к терминалу. Если ваша база данных требует SSL, вам может потребоваться использовать connect_args Параметр для передачи сертификата.
Поверьте, или нет, мы уже сделали дело с настройкой базы данных! Отсюда вперед мы можем вытащить или загружать данные в Pandas через легкие одноиналиры.
Создайте таблицу SQL из DataFrame
Для нашего первого трюка давайте создадим таблицу SQL от данных в CSV. Я скачал CSV, содержащий NYC Вакансии Что я буду использовать, чтобы продемонстрировать:
Мы собираемся создать Dataframe из этого CSV, так как мы сделали миллион раз перед:
import pandas as pd
jobs_df = pd.read_csv('data/nyc-jobs.csv')
Теперь у нас есть DataFrame, готовый для сохранения в качестве таблицы SQL! Мы можем достичь этого с одним методом, встроенным для всех данных DataFrames, называемых to_sql () Отказ Как следует из названия, to_sql () Позволяет загружать нашу DataFrame в базу данных SQL в качестве таблицы SQL. Давайте увидимся в действии:
from sqlalchemy.types import Integer, Text, String, DateTime
...
table_name = 'nyc_jobs'
jobs_df.to_sql(
table_name,
engine,
if_exists='replace',
index=False,
chunksize=500,
dtype={
"job_id": Integer,
"agency": Text,
"business_title": Text,
"job_category": Text,
"salary_range_from": Integer,
"salary_range_to": Integer,
"salary_frequency": String(50),
"work_location": Text,
"division/work_unit": Text,
"job_description": Text,
"posting_date": DateTime,
"posting_updated": DateTime
}
)
Здесь довольно многое! to_sql () Попытки создать таблицу с именем nyc_jobs в базе данных, связанной с двигатель . Эти два позиционных аргумента технически являются единственными необходимыми параметрами, которые нам нужно пройти, но очень хорошая идея воспользоваться способностью к пандам быть более конкретным в создании таблицы:
if_exists: Этот аргумент определяет, что делать в ситуации, когда таблица базы данных с именем nyc_jobs Уже существует в базе данных. По умолчанию Pandas бросит ошибку, которая не очень полезна, если мы не заботимся только о создании этой таблицы в первый раз. Прохождение заменить Для этого аргумента будет отбросить существующую таблицу и заменить его с помощью типов данных и данных, связанных с текущим dataframe. Добавить Сохраняйте существующую таблицу одинаково, но добавьте все строки в DataFrame на существующую таблицу.Схема: Принимает имя схемы Postgres, чтобы сохранить ваш стол.Индекс: Когда Правда Полученную таблицу почитают индекс вашего DataFrame для создания столбца с соответствующей клавишей в вашей базе данных.Chunksize: Передача ряд к этому параметру попытается загрузить данные в виде потока “кусков” n Строки за один раз, в отличие от всех сразу. Прохождение Chunksize полезна для особенно больших наборов данных, которые могут подвергаться риску прерывания во время загрузки.Dtype: Прохождение словаря Python в Dtype Давайте явно устанавливаем данные о данных каждого столбца в нашей базе данных, где каждый ключ это Название столбца и каждый ценность это Тип данных (Я Высоко рекомендую сделать это). Вы заметите, что мы импортируем различные типы данных из sqlalchemy.types , который мы тогда связываем с именем каждого столбца. Если целевая таблица SQL пока не существует, прохождение этих данных на основе данных будет гарантировать, что каждая колонка SQL создана с соответствующим ограничением данных, в отличие от каждого столбца, отображаемого просто как «текст. «Если целевая таблица SQL есть существуют, эти типы данных ДОЛЖЕН Сопоставить типы существующей таблицы, или вы получите ошибку SQL во время загрузки.
Так как мы устанавливаем Sqlalchemy’s эхо Параметр на Правда , Я могу точно видеть, какая моя база данных с этим dataframe:
2020-06-11 23:49:21,082 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2020-06-11 23:49:21,082 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,396 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2020-06-11 23:49:21,396 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,432 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2020-06-11 23:49:21,432 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,470 INFO sqlalchemy.engine.base.Engine show collation where "Charset" = 'utf8mb4' and "Collation" = 'utf8mb4_bin'
2020-06-11 23:49:21,470 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,502 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2020-06-11 23:49:21,502 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,523 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2020-06-11 23:49:21,523 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,537 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2020-06-11 23:49:21,537 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,587 INFO sqlalchemy.engine.base.Engine DESCRIBE "nyc_jobs"
2020-06-11 23:49:21,588 INFO sqlalchemy.engine.base.Engine {}
2020-06-11 23:49:21,654 INFO sqlalchemy.engine.base.Engine ROLLBACK
2020-06-11 23:49:21,691 INFO sqlalchemy.engine.base.Engine
CREATE TABLE nyc_jobs (
job_id INTEGER,
agency TEXT,
business_title TEXT,
job_category TEXT,
salary_range_from INTEGER,
salary_range_to INTEGER,
salary_frequency VARCHAR(50),
work_location TEXT,
division TEXT,
job_description TEXT,
created_at DATETIME,
updated_at DATETIME
)
Как мы описали, наша база данных использует Создать таблицу nyc_jobs Чтобы создать новую таблицу SQL, со всеми столбцами присваиваются соответствующие типы данных.
Создать dataframe из таблицы SQL
Загрузка данных из базы данных в DataFrame PandaS удивительно легко. Чтобы загрузить целую таблицу, используйте read_sql_table () Метод:
table_df = pd.read_sql_table(
table_name,
con=engine
)
Первые два параметра, которые мы проходим, такие же, как в прошлый раз: сначала наше имя таблицы, а затем наш двигатель SQLALCHEMY. Вышеуказанный фрагмент, пожалуй, самый быстрый и самый простой способ перевести таблицу SQL в PandaS DataFrame, по существу, не требуется никакой необходимости конфигурации! Интересно, что Пандас по-прежнему замечает DTYPE каждого столбца, которую мы потянули Несмотря на то, что вытащили из базы данных , как мы можем видеть с Печать (tal_df.info ()) :
RangeIndex: 3123 entries, 0 to 3122 Data columns (total 12 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 job_id 3123 non-null int64 1 agency 3123 non-null object 2 business_title 3123 non-null object 3 job_category 3121 non-null object 4 salary_range_from 3123 non-null int64 5 salary_range_to 3123 non-null int64 6 salary_frequency 3123 non-null object 7 work_location 3123 non-null object 8 division 3123 non-null object 9 job_description 3123 non-null object 10 created_at 3123 non-null object 11 updated_at 3123 non-null object dtypes: int64(3), object(9) memory usage: 292.9+ KB None
read_sql_table () Метод может принять гораздо больше аргументов, чем два, которые мы прошли. Вот пример, где мы читаем таблицу SQL и заставьте некоторые явные вещи, которые произойдут:
...
table_df = pd.read_sql_table(
"nyc_jobs",
con=engine,
schema='public',
index_col='job_id',
coerce_float=True,
columns=[
'job_id',
'business_title',
'job_category',
'posting_date',
'posting_updated'
],
parse_dates=[
'created_at',
'updated_at'
],
chunksize=500
)
Некоторые аргументы должны выглядеть знакомы, когда мы побежали to_sql () ранее. Схема и Chunksize имеют те же значения, что и ранее. У нас также есть несколько новых аргументов:
index_col: Мы можем выбрать любой столбец нашей таблицы SQL, чтобы стать индексом в наших pandaframe dataframe, независимо от того, является ли столбец индексом в SQL. Мы можем пропустить имя одного столбца в виде строки или список строк, представляющих имена нескольких столбцов.Coerce_float.: При установке Правда Pandas будут смотреть на столбцы, содержащие цифры и попытаются преобразовать эти столбцы в количественные номера плавающих точек. Этот атрибут установлен на Истинный по умолчанию.Колонны: Прохождение списка имен столбцов к этому атрибуту создаст файл dataframe из Только Столбцы, которые мы предоставляем (аналогично SQL SELECT на столбцах X).parse_dates: При перемещении данных в панды нам нужно явное состояние, какие столбцы должны быть рассмотрены столбцы DateTime.
Создание данных данных из результатов запроса
Возможно, будут времена, когда вы просто ищете подмножество данных в таблице, в отличие от всей таблицы. В этом сценарии мы можем использовать read_sql () , что создает dataframe из результатов запроса SQL, который вы запускаете на таблице:
...
sql_df = pd.read_sql(
"SELECT * FROM nyc_jobs",
con=engine,
parse_dates=[
'created_at',
'updated_at'
]
)
На этот раз вокруг нашего первого параметра является SQL-запрос вместо имени таблицы. Мы можем изменить этот запрос, чтобы выбрать только определенные столбцы, строки, которые соответствуют критериям, или что-то еще, что вы можете сделать с SQL.
Это все люди! Если вы заинтересованы, источник находится на GitHub здесь:
https://github.com/hackersandslackers/pandas-sqlalchemy-tutorial
Оригинал: “https://dev.to/hackersandslackers/connecting-pandas-to-a-database-with-sqlalchemy-1mnf”