Вставка значений в базу данных из DataFrame

Question

Каким обрзом вставить значения в базу данных, если есть ограничения в виде наличия уникальных ключей? При этом не хочется делать на каждую вставку коммит, так как это очень долго. Например, sqlalchemy выполняет такую вставку раза в 2 быстрее, чем с коммитами. Предполагается, что используется DataFrame из которого читаются данные. Таблица

        CREATE TABLE processed_roads (
            road VARCHAR ( 64 ),
            lat_end REAL,
            lon_end REAL,
            segment_index INT,
            unique (segment_index,)
        );

Пример со вставкой алхимии:

   road_local_df.head(200).to_sql(name='roads', con=self.__conn, if_exists='replace', index=False)

road_local_df В точности совпадает с таблицей.

В postgresql делаю так:

        data = road_local_df
        columns = ','.join(data.columns.tolist())
        for _, row in enumerate(data.iterrows()):
            values = ','.join(fuel_opt.transform_types(row[1].values.tolist()))
            try:
                self._execute_write_query(cursor=cursor, columns=columns, values=values)
                self.__conn.commit()
            except errors.UniqueViolation:
                self.__conn.rollback()
            except Exception as e:
                self.__conn.rollback()
                loguru.logger.error(e)
                raise e

Answer 1

Ответ дали в другом месте. Это можно решить с помощью On conflict do nothing. Пример из ссылки:

INSERT INTO customers (NAME, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name)
DO NOTHING;

Для нашего примера можно записать так:

INSERT INTO processed_roads (road, lat_end, lon_end, segment_index)
VALUES('123', 1, 2, '333') 
ON CONFLICT (segment_index)
DO NOTHING;

Answer 2

Я бы еще посоветовал дополнить этот ответ советом о том как это правильно делать, используя Pandas:

заливаем данные из Pandas DataFrame во временную таблицу, перезаписывая ее, если такая уже существует:

road_local_df.to_sql(name='tmp_customer', con=self.__conn, 
                     if_exists='replace', index=False)

запускаем INSERT INTO customer SELECT * FROM tmp_customer ON CONFLICT (name) DO NOTHING, используя курсор:

cur = engine.raw_connection().cursor()
qry = """
INSERT INTO customer (name, email)
SELECT * from tmp_customer
ON CONFLICT (name)
DO NOTHING
"""
cur.execute(qry)

БЛОГ НА HUSL

Вставка значений в базу данных из DataFrame

Ответы (2 шт):