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

В этой статье будет сравниваться не один, а ЧЕТЫРЕ способа записи данных из электронной таблицы в таблицу SQL с помощью Python, чтобы определить наиболее эффективный метод.

Подключиться к SQL

Если вы еще не подключились к SQL в Python, вам нужно это сделать. Если не знаете как, у меня есть отдельная статья о том, как это сделать!

После настройки ваш код должен выглядеть примерно так:

# PACKAGES
import pyodbc
from getpass import getpass
# INPUTS
username = input("Username: ")
password = getpass("Password: ")
database = input("Database Name: ")
schema = input("Schema: ")
table = input("Table: ")
dsn = input("DSN Name: ")
# CONNECT TO DATABASE
conn = pyodbc.connect("DSN="+dsn+";UID="+username+";PWD="+password)
print(conn)

Примечание. Я работаю на Mac, и у меня было множество проблем с конфигурацией, которые в конечном итоге привели меня к использованию комбинации DSN и UN/PW для подключения. Если вы работаете в Windows, возможно, вы используете другой драйвер или настройки учетных данных, поэтому не беспокойтесь, если это выглядит немного иначе, но по-прежнему работает.

Надеюсь, вы получите вывод, который выглядит примерно так:

<pyodbc.Connection object at 0x7f847b10ef10>

Читать таблицу

Если у вас есть книга Excel:

# PACKAGES
import pandas as pd
# READ EXCEL FILE
df = pd.read_excel("my_data.xlsx", dtype=str)

Если у вас есть CSV:

# PACKAGES
import pandas as pd
# READ CSV FILE
df = pd.read_csv("my_data.csv", dtype=str)

Запись в базу данных SQL

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

Метод 1: МАССОВАЯ ВСТАВКА (РАЗРЕШЕНИЯ МОГУТ ОГРАНИЧИВАТЬ)

Существуют проблемы с безопасностью, связанные с предоставлением разработчикам возможности использовать запрос BULK INSERT, поэтому, если вы используете базу данных организации, вы, вероятно, не сможете использовать этот метод. Однако, если вы можете, это код, который вы хотели бы попробовать:

cursor = conn.cursor()
cursor.execute("BULK INSERT "+schema+"."+table+" FROM 'my_data.csv'")
conn.commit()

Я не могу попробовать это, поэтому дайте мне знать в комментариях, сможете ли вы это сделать и как это работает по сравнению с другими методами.

Метод 2: Цикл + Одиночное выполнение + ВСТАВКА В (МЕДЛЕННО)

Это самый простой и наименее оптимальный подход, поэтому я бы его не рекомендовал, но исключить этот метод из списка с моей стороны было бы упущением.

chunk = df.values.tolist()
tuple_of_tuples = tuple(tuple(x) for x in chunk)
cols = ("?,"*df.shape[1])[:-1]
cursor = conn.cursor()
for t in tuple_of_tuples:
   cursor.execute(f"INSERT INTO "+schema+"."+table+" VALUES ({cols})", t)
conn.commit()

Эмпирическое правило: прежде чем смириться с циклом по строкам фрейма данных для чтения/записи, проверьте документацию и форумы stackoverflow для более эффективных решений.

Способ 3: выполнить Many + INSERT INTO (ЗАВИСИТ ОТ ДРАЙВЕРА)

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

Я попробовал следующий код, и он не удался, выдав ошибку segfault. После некоторых исследований я обнаружил, что это произошло потому, что fast_executemany = True использует функцию ODBC, называемую «массивами параметров», и эта функция не поддерживается FreeTDS и некоторыми другими драйверами ODBC.

Однако один из моих товарищей по команде, использующих Windows, смог успешно запустить это.

chunk = df.values.tolist()
tuple_of_tuples = tuple(tuple(x) for x in chunk)
cols = ("?,"*df.shape[1])[:-1]
cursor = conn.cursor()
cursor.fast_executemany=True
cursor.executemany(f"INSERT INTO "+schema+"."+table+" VALUES ({cols})", tuple_of_tuples)
conn.commit()

Метод 4: Алхимия SQL «to_sql» (ПОБЕДИТЕЛЬ?)

Этот метод требует немного больше настройки, но на самом деле операция записи занимает всего одну строку кода. Слишком хорошо, чтобы быть правдой? Спойлер: это не так!

Сначала вам нужно создать объект механизма с помощью SQL Alchemy, заменив свои учетные данные и данные базы данных. То, как это будет выглядеть, будет зависеть от того, как вы подключаетесь к своей базе данных — я рекомендую иметь DSN в ваших файлах конфигурации для простоты и безопасности вашего кода.

После создания движка достаточно одной строки для записи фрейма данных (например, вашей электронной таблицы в виде объекта Python) в таблицу SQL. Ваши параметры должны быть следующими:

  • con = движок, который вы только что создали (con = подключение)
  • name = таблица SQL, в которую вы записываете данные
  • schema = схема SQL, в которой находится таблица
  • if_exists = что делать, если таблица уже существует — у меня она удаляет старые данные и вставляет совершенно новые данные (отсюда 'replace' в моем коде), но вы можете иметь ее 'fail', если вы не хотите, чтобы какие-либо данные записывались, или 'append' если вы хотите добавить данные к существующим данным.
  • index = записывать ли столбец индекса из фрейма данных или нет
  • chunksize = количество строк, которые нужно записать в таблицу за один раз — этот параметр меня поначалу сбил с толку. Поведение по умолчанию заключается в записи всех строк одновременно, однако существует ограничение на количество полей, которые он может записать одновременно (chunksize = (number of rows * number of columns) / limit)).
  • method = это самое важное!!! Если вы оставите это поле пустым, по умолчанию для него будут использоваться одиночные вызовы INSERT, что точно так же, как при использовании метода № 2 (медленного). Использование 'multi' передаст несколько значений в один вызов INSERT и значительно ускорит его!!!
# PACKAGES
import sqlalchemy
# CREATE ENGINE - MAC WITH DSN
engine = sqlalchemy.create_engine("mssql+pyodbc://"+username+":"+password+"@"+dsn, connect_args={"database": database})  
  
# CREATE ENGINE - WINDOWS WITH NO DSN
server_name_or_ip = "XXX.XXX.XXX.XXX"
engine = sqlalchemy.create_engine("mssql+pyodbc://"+server_name_or_ip+"/"+database+"?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server")
df.to_sql(con=engine, name=table, schema=schema, if_exists="replace", index=False, chunksize=100, method="multi")

Этот метод был очень быстрым для загрузки моего набора данных с ~ 40 столбцами и ~ 18 000 строк.

Заключение и заключительные мысли

Первые два метода, объемная вставка и петля одиночных вставок, являются наименее идеальными. Когда дело доходит до наиболее оптимального, это, вероятно, выбор между методами № 3 и № 4 для пользователей Windows и, безусловно, № 4 для пользователей Mac.

Вот код всех четырех методов, объединенных в одну суть — пожалуйста, попробуйте протестировать их на скорость и дайте мне знать, если у вас были похожие результаты!

Надеюсь, эта статья помогла вам решить дилемму с обработкой данных, и рассмотрите возможность подписаться на меня, чтобы получить больше руководств по кодированию и работе с данными!



Мэдисон Гипсон — Medium
Читайте статьи Мэдисон Гипсон на Medium. Специалист по данным и SWE | НАСА | Студент бакалавра компьютерных наук и MBA. Каждый день…madisongipson.medium.com