我需要用一个查询插入多行(行数不是常量)，所以我需要像这样执行查询:
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)
但我想要更简单的方法。
当前回答
与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)
其他回答
最后，在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的不同组合在一起。
几年来，我一直在使用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)
与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)
如果您正在使用SQLAlchemy，则不需要手工制作字符串，因为SQLAlchemy支持为单个INSERT语句生成多行VALUES子句:
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)
如果你想在一个insert语句中插入多行(假设你没有使用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
