Psycopg2: вставить несколько строк с одним запросом

Ответ 1

Я построил программу, которая вставляет несколько строк на сервер, который был расположен в другом городе.

Я узнал, что использование этого метода было примерно в 10 раз быстрее, чем executemany. В моем случае tup есть кортеж, содержащий около 2000 строк. При использовании этого метода потребовалось около 10 секунд:

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str) 

и 2 минуты при использовании этого метода:

cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup)

Ответ 2

Новый execute_values метод в Psycopg 2.7:

data = [(1,'x'), (2,'y')]
insert_query = 'insert into t (a, b) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, data, template=None, page_size=100
)

Питонический способ сделать это в Psycopg 2.6:

data = [(1,'x'), (2,'y')]
records_list_template = ','.join(['%s'] * len(data))
insert_query = 'insert into t (a, b) values {}'.format(records_list_template)
cursor.execute(insert_query, data)

Объяснение: Если данные для вставки указаны как список кортежей, например, в

data = [(1,'x'), (2,'y')]

то он уже находится в точно требуемом формате как

• Синтаксис values в предложении insert ожидает список записей, как в

  insert into t (a, b) values (1, 'x'),(2, 'y')

• Psycopg адаптирует Python tuple к Postgresql record.

Единственная необходимая работа - предоставить шаблон списка записей, который будет заполнен psycopg

# We use the data list to be sure of the template length
records_list_template = ','.join(['%s'] * len(data))

и поместите его в запрос insert

insert_query = 'insert into t (a, b) values {}'.format(records_list_template)

Печать выходов insert_query

insert into t (a, b) values %s,%s

Теперь к обычной замене аргументов Psycopg

cursor.execute(insert_query, data)

Или просто тестирование того, что будет отправлено на сервер

print (cursor.mogrify(insert_query, data).decode('utf8'))

Вывод:

insert into t (a, b) values (1, 'x'),(2, 'y')

Ответ 3

Отрывок из страницы руководства Psycopg2 в Postgresql.org(см. ниже):

Последний элемент, который я хотел бы показать вам, - это как добавить несколько строк с помощью словаря. Если у вас есть следующее:

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

Вы можете легко вставить все три строки в словаре, используя:

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

Он не сохраняет много кода, но он окончательно выглядит лучше.

Ответ 4

cursor.copy_from - это самое быстрое решение, которое я нашел для объемных вставок. Вот смысл, который я создал, содержащий класс с именем IteratorFile, который позволяет читать итератор, уступая строкам, как файл. Мы можем преобразовать каждую входную запись в строку с использованием выражения генератора. Таким образом, решение будет

args = [(1,2), (3,4), (5,6)]
f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args))
cursor.copy_from(f, 'table_name', columns=('a', 'b'))

Для этого тривиального размера args он не будет сильно отличаться от скорости, но я вижу большие ускорения при работе с тысячами + строк. Это также будет иметь большую память, чем создание гигантской строки запроса. Итератор будет хранить только одну входную запись в памяти за раз, где в какой-то момент у вас закончится нехватка памяти в процессе Python или в Postgres, построив строку запроса.

Ответ 5

[Обновить с помощью psycopg2 2.7]

Классический executemany() примерно в 60 раз медленнее, чем реализация @ant32 (называемый "сложенным" ), как описано в этом потоке: https://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib.com

Эта реализация была добавлена ​​в psycopg2 в версии 2.7 и называется execute_values():

from psycopg2.extras import execute_values
execute_values(cur,
    "INSERT INTO test (id, v1, v2) VALUES %s",
    [(1, 2, 3), (4, 5, 6), (7, 8, 9)])

[Предыдущий ответ]

Чтобы вставить несколько строк, использование синтаксиса multirow VALUES с execute() примерно в 10 раз быстрее, чем использование psycopg2 executemany(). Действительно, executemany() просто запускает множество отдельных операторов INSERT.

@ant32-код отлично работает на Python 2. Но в Python 3, cursor.mogrify() возвращает байты, cursor.execute() принимает либо байты, либо строки, а ','.join() ожидает str экземпляр.

Итак, в Python 3 вам может понадобиться изменить код @ant32, добавив .decode('utf-8'):

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x).decode('utf-8') for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

Или используя байты (только с b'' или b""):

args_bytes = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute(b"INSERT INTO table VALUES " + args_bytes) 

Ответ 6

Все эти методы называются "Extended Inserts" в терминологии Postgres, и по состоянию на 24 ноября 2016 года она все еще на тонну быстрее, чем psychopg2 executemany(), и все остальные методы, перечисленные в этом потоке (которые я пробовал до придя к этому ответу).

Вот некоторый код, который не использует cur.mogrify и хорош, и просто для того, чтобы опустить голову:

valueSQL = [ '%s', '%s', '%s', ... ] # as many as you have columns.
sqlrows = []
rowsPerInsert = 3 # more means faster, but with diminishing returns..
for row in getSomeData:
        # row == [1, 'a', 'yolo', ... ]
        sqlrows += row
        if ( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0:
                # sqlrows == [ 1, 'a', 'yolo', 2, 'b', 'swag', 3, 'c', 'selfie' ]
                insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert)
                cur.execute(insertSQL, sqlrows)
                con.commit()
                sqlrows = []
insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*len(sqlrows))
cur.execute(insertSQL, sqlrows)
con.commit()

Но следует отметить, что если вы можете использовать copy_from(), вы должны использовать copy_from;)

Ответ 7

Еще один приятный и эффективный подход - это передать строки для вставки в качестве 1 аргумента, который представляет собой массив объектов json.

например. вы передаете аргумент:

[ {id: 18, score: 1}, { id: 19, score: 5} ]

Это массив, который может содержать любое количество объектов внутри. Тогда ваш SQL выглядит следующим образом:

INSERT INTO links (parent_id, child_id, score) 
SELECT 123, (r->>'id')::int, (r->>'score')::int 
FROM unnest($1::json[]) as r 

Примечание. Ваш postgress должен быть достаточно новым, чтобы поддерживать json

Ответ 8

Если вы используете SQLAlchemy, вам не нужно связываться с ручной обработкой строки, потому что SQLAlchemy поддерживает создание многострочного VALUES для отдельного оператора INSERT:

rows = []
for i, name in enumerate(rawdata):
    row = {
        'id': i,
        'name': name,
        'valid': True,
    }
    rows.append(row)
if len(rows) > 0:  # INSERT fails if no rows
    insert_query = SQLAlchemyModelName.__table__.insert().values(rows)
    session.execute(insert_query)

Ответ 9

Я использую ant32-ответ выше в течение нескольких лет. Однако я обнаружил, что это ошибка в python 3, потому что mogrify возвращает строку байтов.

Преобразование явно для байтов строк - это простое решение для обеспечения совместимости кода python 3.

args_str = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) 
cur.execute(b"INSERT INTO table VALUES " + args_str)

Ответ 10

Это решение основано на решении J.J, но имеет другую структуру IF/Else из-за столкновения с проблемами.

def insert_Entries(EntriesWishList):
conn = None
try:
    # connect to the PostgreSQL database
    con = psycopg2.connect(dbname='myDBName',
    user='postgres', host='localhost',
    password='myPW')
    # create a new cursor
    cur = con.cursor()
    valueSQL = [ '%s','%s', '%s', '%s', '%s', '%s', '%s' ] # as many as you have columns.
    sqlrows = []
    rowsPerInsert = 3 # more means faster, but with diminishing returns..
    units = len(EntriesWishList) 
    print(units)
    for unit in range(0,units):
            sqlrows += EntriesWishList[unit]
            insertSQL =''
            if(( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0):
                insertSQL = 'INSERT INTO DATABASE VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert)
                cur.execute(insertSQL, sqlrows)
                con.commit()
            elif( (units-unit) <= rowsPerInsert):
                rowsPerInsert = 1
                unit = unit-( len(sqlrows)/len(valueSQL) )
            else:
                continue
            sqlrows = []
    cur.close()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()

Ответ 11

Если вы хотите вставить несколько строк в один член statemens (при условии, что вы не используете ORM), самым простым способом для меня будет использование списка словарей. Вот пример:

 t = [{'id':1, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 6},
      {'id':2, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 7},
      {'id':3, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 8}]

conn.execute("insert into campaign_dates
             (id, start_date, end_date, campaignid) 
              values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);",
             t)

Как вы видите, будет выполнен только один запрос:

INFO sqlalchemy.engine.base.Engine insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);
INFO sqlalchemy.engine.base.Engine [{'campaignid': 6, 'id': 1, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 7, 'id': 2, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 8, 'id': 3, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}]
INFO sqlalchemy.engine.base.Engine COMMIT

Ответ 12

Использование aiopg. Снимок ниже работает отлично.

    # items = [10, 11, 12, 13]
    # group = 1
    tup = [(gid, pid) for pid in items]
    args_str = ",".join([str(s) for s in tup])
    # insert into group values (1, 10), (1, 11), (1, 12), (1, 13)
    yield from cur.execute("INSERT INTO group VALUES " + args_str)