我需要用一个查询插入多行(行数不是常量)，所以我需要像这样执行查询:
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)
但我想要更简单的方法。
当前回答
Execute_batch在这个问题发布后已经添加到psycopg2。
它比execute_values快。
其他回答
从@ant32
def myInsertManyTuples(connection, table, tuple_of_tuples):
cursor = connection.cursor()
try:
insert_len = len(tuple_of_tuples[0])
insert_template = "("
for i in range(insert_len):
insert_template += "%s,"
insert_template = insert_template[:-1] + ")"
args_str = ",".join(
cursor.mogrify(insert_template, x).decode("utf-8")
for x in tuple_of_tuples
)
cursor.execute("INSERT INTO " + table + " VALUES " + args_str)
connection.commit()
except psycopg2.Error as e:
print(f"psycopg2.Error in myInsertMany = {e}")
connection.rollback()
与psycopg2 2.7更新:
经典的executemany()比@ant32的实现(称为“折叠”)慢大约60倍，详见https://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib.com
这个实现在2.7版被添加到psycopg2中，称为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)])
之前的回答:
要插入多行，与execute()一起使用多行VALUES语法比使用psycopg2 executemany()快10倍左右。实际上，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)
或者只使用bytes(带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)
光标。由@ joseph提供的复制解决方案。Sheedy (https://stackoverflow.com/users/958118/joseph-sheedy)以上(https://stackoverflow.com/a/30721460/11100064)确实是闪电般快。
然而，他给出的例子不适用于具有任意数量字段的记录，我花了一些时间才弄清楚如何正确使用它。
IteratorFile需要用制表符分隔的字段实例化，就像这样(r是一个字典列表，其中每个字典都是一条记录):
f = IteratorFile("{0}\t{1}\t{2}\t{3}\t{4}".format(r["id"],
r["type"],
r["item"],
r["month"],
r["revenue"]) for r in records)
为了泛化任意数量的字段，我们将首先创建一个具有正确数量的制表符和字段占位符的行字符串:"{}\t{}\t{}....\t{}"，然后使用.format()为记录中的r填充字段值:*list(r.values())):
line = "\t".join(["{}"] * len(records[0]))
f = IteratorFile(line.format(*list(r.values())) for r in records)
在主旨这里完成功能。
我构建了一个程序，可以向位于另一个城市的服务器插入多行代码。
我发现使用这种方法比任何执行方法都快10倍。在我的例子中，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)
使用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)
