Мне нужно вставить несколько строк с одним запросом (количество строк не является константным), поэтому мне нужно выполнить запрос, подобный этому:
INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);
Единственный способ, которым я знаю,
args = [(1,2), (3,4), (5,6)]
args_str = ','.join(cursor.mogrify("%s", (x, )) for x in args)
cursor.execute("INSERT INTO t (a, b) VALUES "+args_str)
но я хочу более простой способ.
Я построил программу, которая вставляет несколько строк на сервер, который был расположен в другом городе.
Я узнал, что использование этого метода было примерно в 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)
Новый 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')
Отрывок из страницы руководства 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)
Он не сохраняет много кода, но он окончательно выглядит лучше.
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, построив строку запроса.
[Обновить с помощью 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)
Все эти методы называются "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;)
Еще один приятный и эффективный подход - это передать строки для вставки в качестве 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
Если вы используете 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)
Я использую 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)
Это решение основано на решении 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()
Если вы хотите вставить несколько строк в один член 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
Использование 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)