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