4 уровня создания невероятно быстрых подключений к базе данных Python
Загружать данные в базу данных с помощью Python очень просто. Загрузите свои данные в фрейм данных Pandas и используйте метод dataframe.to_sql(). Но вы когда-нибудь замечали, что вставка занимает много времени при работе с большими таблицами? У нас нет времени сидеть и ждать, пока закончатся наши запросы!
С помощью нескольких настроек вы можете делать вставки НАМНОГО быстрее. Сравните время записи коричневой полосы (метод to_sql() по умолчанию) с зеленой полосой (наша цель). Также обратите внимание, что вертикальная ось представлена в логарифмическом масштабе! Прочитав эту статью, вы сможете молниеносно выполнять операции с базами данных. Готовый? Пойдем!
Цели и шаги
Мы будем работать над методом сверхбыстрой вставки в два этапа. Первая часть посвящена тому, как правильно подключиться к нашей базе данных, а вторая часть исследует 4 способа вставки данных в восходящем порядке.
1. Подключение к нашей базе данных
Чтобы вообще общаться с какой-либо базой данных, вам сначала нужно создать механизм базы данных. Этот движок переводит ваши объекты python (например, кадр данных Pandas) во что-то, что можно вставить в базы данных. Для этого ему необходимо знать, как он может получить доступ к вашей базе данных. Для этого используется строка подключения. Сначала мы создадим строку подключения, а затем используем ее для создания нашего механизма базы данных.
1.1 Создайте строку подключения
Строка подключения содержит информацию о типе базы данных, драйвере odbc и учетных данных базы данных, необходимых для доступа к базе данных.
constring = "mssql+pyodbc://USERNAME:PASSWORD@DATABASESERVER_IP/DATABASENAME?driver=SQL+Server+Native+Client+11.0"
В приведенном выше примере мы создаем строку для подключения к базе данных Microsoft SQL Server. Как видите, мы указываем учетные данные нашей базы данных (имя пользователя, пароль, IP-адрес нашего сервера базы данных и имя нашего сервера базы данных), а также используемый нами драйвер. Формат строки подключения зависит от базы данных. Посетите connectionstrings.com, чтобы узнать, как должна выглядеть ваша строка подключения.
О драйверах. Драйвер должен соответствовать версии используемой вами базы данных. В приведенном выше примере мы используем MS SQL Server 2011, поэтому нам нужен драйвер SQL Server Native Client 11. Этот драйвер должен быть установлен на машине, на которой вы запускаете свои скрипты Python. Проверьте, какие драйверы установлены, выполнив поиск Источники данных ODBC (на вкладке Драйверы). Если необходимый вам драйвер не установлен, вы можете легко загрузить его (например, https://www.microsoft.com/en-us/download/details.aspx?id=36434).
1.2 Создание механизма базы данных с нашей строкой подключения
Как только строка подключения действительна, легко создать механизм базы данных. Я обычно создаю свой, как показано ниже.
import sqlalchemy
dbEngine = sqlalchemy.create_engine(constring, connect_args={'connect_timeout': 10}, echo=False)Установка для эха значения True позволяет вам видеть все выполняемые запросы. Если вы установите эхо на строку «отладка», строки результатов также будут напечатаны.
1.3 Тестирование нашей базы данных
Используйте небольшой скрипт ниже, чтобы проверить ваше соединение. Если все прошло хорошо, он должен напечатать, что двигатель действителен. Если что-то пошло не так, он напечатает ошибку.
try:
with dbEngine.connect() as con:
con.execute("SELECT 1")
print('engine is valid')
except Exception as e:
print(f'Engine invalid: {str(e)}')
2. Четыре уровня методов быстрой вставки
Теперь, когда мы можем подключиться к нашей базе данных, мы можем начать вставлять данные в базу данных. Мы рассмотрим 4 способа вставки данных, закончив самым быстрым. В конце этой статьи вы найдете подробное описание метода to_sql в pandas.
2.1 Ванильный метод to_sql
Вы можете вызвать этот метод в кадре данных и передать его механизму базы данных. Это довольно простой метод, который мы можем настроить, чтобы получить от него каждую каплю скорости. В приведенном ниже примере мы создаем фрейм данных и просто загружаем его.
import pandas as pd
# 1. Create a dataframe
df = pd.DataFrame({'numbers': [1, 2, 3], 'colors': ['red', 'white', 'blue']})
print(df.head())
# dataframe looks like:
numbers colors
0 1 red
1 2 white
2 3 blue
# 2. Upload this dataframe
df.to_sql(con=dbEngine, schema="dbo", name="colortable", if_exists="replace", index=False)
Как видите, нам нужно только указать наше соединение (наш движок базы данных, который мы создали ранее), в какую схему мы хотим поместить нашу новую таблицу и наше новое имя таблицы. Кроме того, мы можем определить, что делать, если указанное schema.tablename уже существует (в нашем случае заменить), и хотим ли мы поместить индекс в таблицу (см. далее полный список параметров).
Приведенный выше пример — самый простой способ вставки данных, но и самый медленный. Проблема в том, что мы пишем весь фрейм данных сразу, создавая оператор вставки для каждой записи. На маленьком столе, таком как наша таблица цветов, это не большая проблема, но на больших столах она определенно есть.
2.2 Фрагментация
Наш код будет работать немного лучше, если мы добавим размер фрагмента. Это будет записывать данные партиями указанного размера фрагмента, экономя много памяти.
df_large.to_sql(con=dbEngine, schema="dbo", name="largetable", if_exists="replace", index=False, chunksize=1000)Тем не менее, этот метод создает оператор вставки для каждой записи в нашей таблице, что все еще очень медленно.
2.3 Мультивставка
Добавление «мультиметода» значительно улучшит скорость вставки. Вместо написания оператора вставки для каждой записи теперь мы можем отправлять несколько строк в одном операторе. База данных может обрабатывать несколько записей за одну операцию вместо операции над записью.
ВАЖНО: этот метод не работает и не нужен для базы данных Microsoft SQL Server. См. способ 4.
df_target.to_sql(con=dbEngine, schema="dbo", name="targettable", if_exists="replace", index=False, chunksize=1000, method='multi')Идеальный размер фрагмента зависит от размеров вашего стола. Таблица с большим количеством столбцов требует меньшего размера блока, чем таблица, в которой всего 3 столбца.
Это быстрый способ записи в базу данных для многих баз данных. Однако для Microsoft Server есть более быстрый вариант.
2.4 SQL Server fast_executemany
SQLAlchemy 1.3 предоставляет нам опцию fast_executemany при создании dbEngine для сервера SQL. Этот метод является самым быстрым способом записи кадра данных в базу данных SQL Server.
dbEngine = sqlalchemy.create_engine(constring, fast_executemany=True, connect_args={'connect_timeout': 10}, echo=False)df_target.to_sql(con=dbEngine, schema="dbo", name="targettable", if_exists="replace", index=False, chunksize=1000)
В приведенном выше коде вы видите, что нам нужно немного настроить наш databaseEngine; мы должны добавить опцию fast_executemany. Вот и все.
Затем, когда мы записываем наш фрейм данных в базу данных, единственное, что мы должны помнить, это то, что мы не указываем наш метод (или устанавливаем метод=None). Это связано с тем, что по умолчанию fast_executeman вставляется одновременно.
Вывод
Благодаря этим простым обновлениям у вас теперь есть инструменты для улучшения соединений Python с базой данных. Я включил сводку с примером кода и шагами ниже. Удачного кодирования!
-Майк
P.S. Нравится, что я делаю? "Подписывайтесь на меня"!
Обзор: параметры to_sql
Ниже обзор и объяснение всех настроек функции to_sql.
- con (движок sqlalchemy)
соединение с базой данных (движок sqlalchemy) - name (str): обязательное
имя таблицы, в которую вы хотите записать - схема (str): по умолчанию: схема базы данных по умолчанию (dbo)
имя схемы, в которую вы хотите выполнить запись. - if_exists (str):по умолчанию: «сбой»
Что делать, если указанная таблица уже существует?
- 'fail': выдает ошибку
- 'append': добавляет данные в указанную таблицу
- 'replace': полностью заменить таблицу (ВНИМАНИЕ: это опасно) - index (bool): По умолчанию: True
Если для параметра Index установлено значение True, создается дополнительный столбец с именем «id», который индексируется. - index_label (строка или последовательность):по умолчанию: нет
Метка столбца для столбцов индекса. По умолчанию Нет. - Размер фрагмента (целое число): по умолчанию: Нет
Записывайте строки пакетами размером [размер фрагмента]. Это хорошая идея, если у вас есть много записей, которые необходимо загрузить. Это экономит память. Если нет: записывает все строки сразу - dtype (dict или scalar): по умолчанию нет
Укажите типы данных
Если указан скаляр: применяет этот тип данных ко всем столбцам в фрейме данных перед записью в базу данных. Чтобы указать тип данных для каждого столбца, предоставьте словарь, в котором имена столбцов фрейма данных являются ключами. Значения являются типами sqlalchemy (например, sqlalchemy.Float и т. д.). - метод (str): по умолчанию нет
Управляет предложением вставки SQL
- Нет: используется стандартное предложение вставки SQL (по одному на строку)
- 'multi': передает несколько значений в одно предложение Insert.
