Psycopg2, Postgresql, Python: самый быстрый способ вставки большого объема

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

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

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

  • Использование pyformat стиля привязки для параметрической вставки
  • Используя executemany в списке кортежей и
  • Использование записи результатов в файл и использование COPY.

Кажется, что первый способ является самым эффективным, но я был бы признателен за ваши идеи и фрагменты кода, рассказывающие мне, как это сделать правильно.

Ответы

Ответ 1

Да, я бы проголосовал за COPY, чтобы вы могли записать файл на жесткий диск сервера (а не на диск, на котором запущено приложение), поскольку COPY будет читать только сервер.

Ответ 2

Существует новый руководство psycopg2, содержащий примеры для всех параметров.

Самый эффективный вариант COPY. Тогда исполнители. Затем выполните с помощью pyformat.

Ответ 3

в моем опыте executemany не быстрее, чем запускать многие вставки самостоятельно, самый быстрый способ - форматировать один INSERT со многими значениями самостоятельно, возможно, в будущем executemany улучшится, но на данный момент он довольно медленный

i подкласс a list и перегрузить метод append, поэтому, когда список достигает определенного размера, я отформатирую INSERT для его запуска

Ответ 4

Вы можете использовать новую библиотеку upsert:

$ pip install upsert

(сначала вам нужно pip install decorator)

conn = psycopg2.connect('dbname=mydatabase')
cur = conn.cursor()
upsert = Upsert(cur, 'mytable')
for (selector, setter) in myrecords:
    upsert.row(selector, setter)

Где selector - объект dict, такой как {'name': 'Chris Smith'} и setter - это dict как { 'age': 28, 'state': 'WI' }

Он почти так же быстро, как писать собственный код INSERT [/UPDATE] и запускать его напрямую с помощью psycopg2... и он не взорвется, если строка уже существует.

Ответ 5

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

Ответ 6

После некоторого тестирования unsest часто кажется чрезвычайно быстрым, как я узнал из @Clodoaldo Neto ответить на аналогичный вопрос.

data = [(1, 100), (2, 200), ...]  # list of tuples

cur.execute("""CREATE TABLE table1 AS
               SELECT u.id, u.var1
               FROM unnest(%s) u(id INT, var1 INT)""", (data,))

Однако может быть сложным с чрезвычайно большими данными.

Ответ 7

Любой, кто использует SQLalchemy, может попробовать 1.2 версию, которая добавила поддержку массовой вставки для использования psycopg2.extras.execute_batch() вместо executemany, когда вы инициализируете свой движок с помощью use_batch_mode = True, например:

engine = create_engine(
    "postgresql+psycopg2://scott:[email protected]/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

Тогда кому-то придется использовать SQLalchmey, не потрудись попробовать разные комбинации sqla и psycopg2 и напрямую с SQL.

Ответ 8

Очень близкий вопрос: Массовая вставка с SQLAlchemy ORM

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

В этом случае автомагистраль должна использовать функцию execute_batch() для psycopg2. В документации это лучше всего:

Текущая реализация executemany() (с использованием чрезвычайно благотворительного занижения), не особенно выполняющая. Эти функции могут использоваться для ускорения повторного выполнения инструкции по набору параметров. executemany() количество серверных обращений, производительность может быть на порядок лучше, чем при использовании функции executemany().

В моем собственном тесте execute_batch() примерно в два раза быстрее, чем executemany(), и дает возможность настроить page_size для дальнейшей настройки (если вы хотите выжать последние 2-3% производительности из драйвера).

Такую же функцию можно легко включить, если вы используете SQLAlchemy, установив use_batch_mode=True в качестве параметра при создании экземпляра механизма с помощью create_engine()