我需要用一个查询插入多行(行数不是常量)，所以我需要像这样执行查询:
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)
但我想要更简单的方法。
当前回答
所有这些技术在Postgres术语中都被称为“扩展插入”，截至2016年11月24日，它仍然比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;)
其他回答
Psycopg 2.7新增execute_values方法:
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中，python的方法是:
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)
解释:如果要插入的数据像in一样以元组列表的形式给出
data = [(1,'x'), (2,'y')]
那么它已经是精确要求的格式
插入子句的值语法需要一个记录列表，如 插入t (a, b)值(1，'x')，(2， 'y') Psycopg使Python元组适应Postgresql记录。
惟一必要的工作是提供一个由psycopg填写的记录列表模板
# We use the data list to be sure of the template length
records_list_template = ','.join(['%s'] * len(data))
并将其放在插入查询中
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')
我构建了一个程序，可以向位于另一个城市的服务器插入多行代码。
我发现使用这种方法比任何执行方法都快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)
几年来，我一直在使用ant32的答案。然而，我发现它在python 3中抛出了一个错误，因为mogrify返回一个字节字符串。
显式转换为bytse字符串是使代码与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)
另一种有效的方法是将rows作为1参数传递给insert， 也就是数组的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
最后，在SQLalchemy1.2版本中，这个新实现被添加到使用psycopg2.extras.execute_batch()而不是executemany来初始化引擎时使用use_batch_mode=True，例如:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@host/dbname",
use_batch_mode=True)
http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109
然后有人将不得不使用SQLalchmey不会费心尝试sqla和psycopg2和直接SQL的不同组合在一起。
