Изящный прием данных с помощью SQLAlchemy и Pandas

Когда размер данных недостаточно велик для использования сред распределенных вычислений (таких как Apache Spark), эффективным способом является обработка данных на машине с пандами. Но как элегантно вставить данные с объектом dataframe — большая проблема. Как мы знаем, у Python хорошая база данных. SQLAlchemy с хорошей интеграцией ORM и хорошей библиотекой обработки данных Панды. Здесь мы рассмотрим несколько различных реализаций и обсудим плюсы и минусы в этой статье.

Проблема

С кадром данных pandas с тысячами данных и сложным типом данных. Как быстро загрузить данные в целевую базу данных, а код должен быть прост в обслуживании.

Примечание. Здесь мы используем PostgreSQL в качестве целевой базы данных.

Простая идея — используйте Pandas df.to_sql функция

С помощью этой функции вы можете вставлять свои данные с помощью pandas API. df.to_sqlто вы сделали работу!

Преимущества

Самый простой способ реализации.

Недостатки

• Очень медленно!
• Если вам нужно сначала усечь таблицу, это не лучший способ использовать эту функцию.

Еще одна простая идея — вставка данных с помощью CSV-файлов

Еще одна наивная идея решить эту проблему — вывести фрейм данных в виде CSV-файла и использовать copy команда или та же реализация в python для импорта данных в базу данных.
Ниже приведен пример кода:

def bulkload_csv_data_to_database(engine, tablename, columns, data, sep=","):
    logging.info("Start ingesting data into postgres ...")
    logging.info("Table name: {table}".format(table=tablename))
    logging.info("CSV schema: {schema}".format(schema=columns))
    conn = engine.connect().connection
    cursor = conn.cursor()
    cursor.copy_from(data, tablename, columns=columns, sep=sep, null='null')
    conn.commit()
    conn.close()
    logging.info("Finish ingesting")

df.to_csv(csv_path, index=False, header=False)
buldload_csv_data_to_database(engine, tablename, columns, data)

Преимущества

Скорость загрузки быстрая!

Недостатки

Необходимо поддерживать и обрабатывать данные в формате CSV, чтобы целевая библиотека базы данных распознала их. Это сходит с ума, когда ваша схема довольно сложна (подумайте о данных с полями и массивами json…). Вам нужно будет рассмотреть формат между df.to_csv а также cursor.copy_from очень осторожно.

Третья идея — вставка данных с помощью SQLAlchemy ORM

Чтобы избавиться от огромных усилий по поддержке формата CSV, другим решением является использование того же метода в Интернете: создание объекта таблицы с строкой pandas и добавление объекта в сеанс один за другим. Ниже приведен простой пример:

Session = sessionmaker(bind=conn)
session = Session()
for _, row in df.iterrows():
  user = User(name=row["name"])
  session.add(user)
session.commit()
session.close()

Преимущества

• Простота обслуживания
• Наслаждайтесь преимуществами ORM

Недостатки

• Медленно, потому что нужно выполнять пункты по одному.

Четвертая идея — вставка данных с помощью Pandas и SQLAlchemy ORM

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

Вот пример кода:

Session = sessionmaker(bind=dest_db_con)
session = Session()
session.bulk_insert_mappings(MentorInformation, df.to_dict(orient="records"))
session.close()

Преимущества

• Быстро
• Простой
• Простота обслуживания
• Наслаждайтесь преимуществами ORM

Недостатки

Не беспокойтесь с моей точки зрения.

Заключение и обсуждение

Поскольку панды могут обрабатывать данные только на машине, стоит подумать и о том, как решить ту же проблему в распределенных средах. Существует множество сред, таких как Apache Spark, для решения расширенной проблемы.

Но в данных размером с одну машину использование pandas + SQLAlchemy — это достаточно мощный способ решить проблему приема данных!