使用psycopg2插入多行 [英] Inserting multiple rows using psycopg2

问题描述

根据> psycopg2：通过一个查询插入多行，使用psycopg2的执行而不是执行。其他人可以确认吗？

According to psycopg2: insert multiple rows with one query, it is much more efficient to use psycopg2's execute instead of executemany . Can others confirm?

上述StackOverflow问题建议使用迁移以创建以下类型的语句：

The above StackOverflow question suggests using mogrify for creating statements of the sort:

INSERT INTO table VALUES (value1, value2), (value3, value4)

是否可以使用常规执行函数？我认为是这样的形式

Is it possible to generate such a statement using the regular execute function? I thought something of the form

cursor.execute("""INSERT INTO table VALUES (%s, %s), (%s, %s)""", ((value1,value2),(value3,value4)))

会工作。

更新：

例如，我尝试传递执行sql语句：

For instance, I tried I passing into execute the sql statement:

insert into history (timestamp) values (%s),(%s); 

具有以下元组：

(('2014-04-27 14:07:30.000000',), ('2014-04-27 14:07:35.000000',))

但我得到的只是错误：

没有要提取的结果

no results to fetch

推荐答案

要使用execute方法，将要插入的数据放入一个列表。一个列表将由psycopg2调整为数组。然后取消嵌套该数组并根据需要强制转换值

To use the execute method place the data to be inserted in a list. A list will be adapted by psycopg2 to an array. Then you unnest the array and cast the values as necessary

import psycopg2

insert = """
    insert into history ("timestamp")
    select value
    from unnest(%s) s(value timestamp)
    returning *
;"""

data = [('2014-04-27 14:07:30.000000',), ('2014-04-27 14:07:35.000000',)]
conn = psycopg2.connect("host=localhost4 port=5432 dbname=cpn")
cursor = conn.cursor()
cursor.execute(insert, (data,))
print cursor.fetchall()
conn.commit()
conn.close()

不确定性能与executemany的差异将是巨大的。但我认为以上方法更加整洁。顾名思义， returning 子句将返回插入的元组。

Not sure if the performance difference from executemany will be significant. But I think the above is neater. The returning clause will, as the name suggests, return the inserted tuples.

BTW 时间戳是保留字，不应用作列名。

BTW timestamp is a reserved word and should not be used as a column name.

这篇关于使用psycopg2插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！