Почему psycopg2 INSERT так долго выполняется в цикле и как его ускорить?

Я пытаюсь вставить (source_lat, source_long, destination_lat, destination_long) строки из кадра данных Pandas в таблицу PostgreSQL (gmaps), используя psycopg2 INSERT в цикле for. В таблице есть ограничение целостности, которое предотвращает вставку повторяющихся строк (source_lat, source_long, destination_lat, destination_long), поэтому я перехватываю любые дубликаты с помощью блока try, кроме блока. Мой код ниже.

Я перебираю каждую строку в кадре данных (около 100000 строк) и вызываю cursor.execute(INSERT) для каждой строки, проверяя, вызывает ли это ошибку целостности, если нет, я вставляю эту строку в таблицу gmaps.

Однако этот фрагмент кода выполняется целую вечность — как я могу его ускорить? Я не уверен, где накладные расходы лежат? Спасибо!

Ele — это кортеж, который содержит (source_lat, source_long, destination_lat, destination_long)

for ele in coordinates:
#Inserts new row to table
      try:
         cursor.execute('INSERT INTO gmaps (source_latitude, source_longitude, destination_latitude, destination_longitude) VALUES (%s, %s, %s, %s)', (ele[0], ele[1], ele[2], ele[3])))
      except psycopg2.IntegrityError:
         conn.rollback()
      else:
         conn.commit()

python psycopg2 postgresql pandas
person wanderingstu    schedule 02.08.2019    source источник
comment
Это не код, а база данных отказывается от действия. Вы можете попробовать, если это быстро, когда вы напрямую вставляете значение в базу данных.   -  person Yuan    schedule 02.08.2019
comment
Выполнение 100000 отдельных операторов INSERT будет медленным. Нет пути вокруг этого.   -  person John Gordon    schedule 02.08.2019

Ответы (1)


arrow_upward
2
arrow_downward

Существует несколько вариантов ускорения вставки объемных данных.

1.) commit() после завершения цикла:

for ele in coordinates:
    cursor.execute('INSERT INTO gmaps (source_latitude, source_longitude, destination_latitude, destination_longitude) VALUES (%s, %s, %s, %s)', (ele[0], ele[1], ele[2], ele[3])))
conn.commit()

2.) Используйте помощники быстрого выполнения от psycopg2, например execute_batch() or execute_values().

3.) Концентрация строк с использованием mogrify():

dataText = ','.join(cur.mogrify('(%s,%s,%s,%s)', row) for ele in coordinates)
cur.execute('INSERT INTO gmaps VALUES ' + dataText)
cur.commit()

Подробное сравнение INSERT скоростей выполнения можно найти в этом тесте.

person Maurice Meyer    schedule 03.08.2019