Массовая вставка строк в PostgreSQL через Python и psycopg2

Быстрый ответ

Для вставки нескольких строк с использованием библиотеки psycopg2 воспользуйтесь методом executemany(). Подготовьте список кортежей, где каждый кортеж содержит значение каждой строки. Cочетая executemany() с SQL-запросом INSERT и вашим списком, можно быстро выполнить операцию.

Пример применения этого метода:

data = [(1, 'Алиса'), (2, 'Боб'), (3, 'Чарли')]
query = "INSERT INTO таблица (колонка1, колонка2) VALUES (%s, %s)"
cursor.executemany(query, data)
connection.commit()

Если вам нужна скорость: mogrify()

По сравнению с executemany(), cursor.mogrify() выглядит быстрее, поскольку этот метод подготавливает запросы для объединения их в одну SQL-команду.

Рассмотрим, как это работает на практике:

data = [(1, 'Алиса'), (2, 'Боб'), (3, 'Чарли')]
args_str = ','.join(cursor.mogrify("(%s,%s)", x).decode('utf-8') for x in data)
cursor.execute("INSERT INTO таблица (колонка1, колонка2) VALUES " + args_str)
connection.commit()

mogrify() примерно в десять раз быстрее executemany() при работе с большим объемом данных.

Шаг вперед: execute_values

Метод execute_values из модуля psycopg2.extras — это оптимальное решение для массовой вставки. Он сочетает в себе удобство executemany() и эффективность mogrify().

Пример использования:

from psycopg2.extras import execute_values

data = [(1, 'Алиса'), (2, 'Боб'), (3, 'Чарли')]
execute_values(cursor, "INSERT INTO таблица (колонка1, колонка2) VALUES %s", data)
connection.commit()

Метод execute_values может превосходить executemany() по скорости до шестидесяти раз.

Быстрые и беспощадные: copy_from()

Метод cursor.copy_from() — идеальное решение для эффективной массовой вставки данных. Его преимущество состоит в том, что данные передаются непосредственно в базу данных.

from io import StringIO

data = '1\tАлиса\n2\tБоб\n3\tЧарли\n'
buffer = StringIO(data)
cursor.copy_from(buffer, 'таблица', columns=('колонка1', 'колонка2'))
connection.commit()

Данный подход обеспечивает высокую производительность даже при работе с большими объемами данных.

Сочетание гибкости и эффективности

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

Визуализация

Допустим, что мы загружаем товары в поезд 🚂, где каждый вагон представляет собой строку в базе данных, а товары — это вставляемые данные.

Поезд с базой данных 🚂: |📦|    |    |    |    | <- Пустые вагоны (строки)

При использовании psycopg2 мы загружаем все товары в вагоны одновременно:

cur.executemany("INSERT INTO груз (предмет) VALUES (%s)", [('Уголь',), ('Нефть',), ('Сталь',)])

Сразу же после одного запроса поезд полностью загружен:

Поезд с базой данных 🚂: |📦Уголь|📦Нефть|📦Сталь| <- Всё загружено одним запросом!

Эффективность и грация этого процесса напоминают хорошо отлаженную работу железнодорожной системы. 🛤️

Навигация по нюансам

Python 3 и байтовые строки

Используйте байтовые строки с cursor.execute() для оптимизации производительности, не забывая о необходимости корректного декодирования.

Формат записи PostgreSQL

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

Экономия памяти

При работе с большими объемами данных cursor.copy_from() станет вашим надежным помощником, минимизируя требования к памяти благодаря его эффективности.

Расширение своих знаний

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

Полезные материалы

1. psycopg2.extras – Расширенные возможности Psycopg 2 — подробное описание execute_values в официальной документации psycopg2.
2. python – psycopg2: вставка нескольких строк одним запросом – Stack Overflow — обсуждение вставки нескольких строк с использованием psycopg2 на форуме Stack Overflow.
3. PostgreSQL: Документация: 16: INSERT — документация по команде INSERT в PostgreSQL.
4. itertools — Функции создания итераторов для эффективного циклования — Документация Python 3.12.2 — набор инструментов для улучшения производительности циклов в Python.
5. Когда использовать List Comprehension в Python – Real Python — рекомендации по применению списковых включений в Python.
6. PEP 249 – Спецификация Python Database API версии 2.0 | peps.python.org — стандарт Python Database API.
7. Psychopg2 Tutorial – PostgreSQL wiki — руководство по использованию psycopg2 для работы с PostgreSQL с акцентом на производительность.