psycopg2：使用一条查询插入多行

Question

我需要一次性插入多行数据，但行数不固定，所以我想执行像下面这样的查询：

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)

但我希望能有更简单的方法。

Answer 1

关于 psycopg2 2.7 的更新：

传统的 executemany() 方法比 @ant32 提出的“折叠”实现慢大约 60 倍，具体可以参考这个讨论： 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)])

---

之前的回答：

如果要插入多行数据，使用多行的 VALUES 语法配合 execute() 方法，比使用 psycopg2 的 executemany() 快大约 10 倍。实际上，executemany() 只是执行了很多单独的 INSERT 语句。

@ant32 的代码在 Python 2 中运行得很好。但在 Python 3 中，cursor.mogrify() 返回的是字节，cursor.execute() 可以接受字节或字符串，而 ','.join() 需要的是字符串实例。

所以在 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) 

Answer 2

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)

解释：如果要插入的数据是以元组列表的形式给出的，比如

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

那么它已经是完全符合要求的格式，因为

1. insert 语句中的 values 语法需要的是一系列记录，像这样：

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

2. 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')

Answer 3

我写了一个程序，可以把很多行数据插入到一个位于另一个城市的服务器上。

我发现用这种方法插入数据的速度大约是 executemany 的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)