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

cursor.copy_from 是我见过的最快的解决方案到目前为止发现用于批量插入。 这是我制作的要点，其中包含一个名为 IteratorFile 的类，它允许生成字符串的迭代器像读取文件。我们可以使用生成器表达式将每个输入记录转换为字符串。因此，解决方案是

args = [(1,2), (3,4), (5,6)]
f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args))
cursor.copy_from(f, 'table_name', columns=('a', 'b'))

对于这个微不足道的参数大小，它不会产生太大的速度差异，但我在处理数千行以上时看到了很大的加速。与构建巨大的查询字符串相比，它的内存效率也更高。迭代器一次只能在内存中保存一个输入记录，在某些时候，您将在 Python 进程或 Postgres 中通过构建查询字符串耗尽内存。

Answer 2

Psycopg2 教程页面的片段 Postgresql.org（见底部）：

我想向您展示的最后一项是如何使用字典插入多行。如果您有以下情况：

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

您可以使用以下方法轻松插入字典中的所有三行：

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

它不会节省太多代码，但看起来确实更好。

Answer 3

安全漏洞

截至 2022 年 11 月 16 日，@Clodoaldo Neto（针对 Psycopg 2.6）、@Joseph Sheedy、@JJ、@Bart Jonk、@kevo Njoki、@TKoutny 和 @Nihal Sharma 的答案包含 SQL注入漏洞并且不应该 用过的。

迄今为止最快的提案 (copy_from) 也不应该使用，因为很难正确转义数据。当尝试插入 '、"、\n、\、 等字符时，这一点很明显 。

\t 或 \n作者 的psycopg2 还建议不要使用 copy_from：

copy_from() 和 copy_to() 实际上只是古老且不完整的方法

最快的方法

最快的方法是 cursor.copy_expert，它可以直接从 CSV 文件插入数据。

with open("mydata.csv") as f:
    cursor.copy_expert("COPY mytable (my_id, a, b) FROM STDIN WITH csv", f)

copy_expert 也是即时生成 CSV 文件的最快方法。作为参考，请参阅以下 CSVFile 类，该类会注意限制内存使用。

import io, csv

class CSVFile(io.TextIOBase):
    # Create a CSV file from rows. Can only be read once.
    def __init__(self, rows, size=8192):
        self.row_iter = iter(rows)
        self.buf = io.StringIO()
        self.available = 0
        self.size = size

    def read(self, n):
        # Buffer new CSV rows until enough data is available
        buf = self.buf
        writer = csv.writer(buf)
        while self.available < n:
            try:
                row_length = writer.writerow(next(self.row_iter))
                self.available += row_length
                self.size = max(self.size, row_length)
            except StopIteration:
                break

        # Read requested amount of data from buffer
        write_pos = buf.tell()
        read_pos = write_pos - self.available
        buf.seek(read_pos)
        data = buf.read(n)
        self.available -= len(data)

        # Shrink buffer if it grew very large
        if read_pos > 2 * self.size:
            remaining = buf.read()
            buf.seek(0)
            buf.write(remaining)
            buf.truncate()
        else:
            buf.seek(write_pos)

        return data

然后可以像这样使用该类：

rows = [(1, "a", "b"), (2, "c", "d")]
cursor.copy_expert("COPY mytable (my_id, a, b) FROM STDIN WITH csv", CSVFile(rows))

如果您的所有数据都适合内存，您也可以直接生成整个 CSV 数据，而无需 CSVFile 类，但如果您不知道要生成多少数据将来插入，您可能不应该这样做。

f = io.StringIO()
writer = csv.writer(f)
for row in rows:
    writer.writerow(row)
f.seek(0)
cursor.copy_expert("COPY mytable (my_id, a, b) FROM STDIN WITH csv", f)

基准测试结果

• 914 毫秒 - 多次调用 cursor.execute
• 846 毫秒 - cursor.executemany
• 362 毫秒 - psycopg2.extras.execute_batch
• 346 毫秒 - <代码>execute_batch与page_size=1000
• 265 毫秒 - execute_batch，使用准备好的语句
• 161 毫秒 - psycopg2.extras.execute_values
• 127 毫秒 - cursor.execute，使用字符串连接值
• 39 毫秒 - copy_expert 一次性生成整个 CSV 文件
• 32 毫秒 - copy_expert 与 CSVFile

Answer 4

所有这些技术在 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 ；）

Answer 5

10 年后，Psycopg 3 和 PostgreSQL 14 或更高版本的答案是：使用 管道模式。在Psycopg 3的管道模式实现中的execute/executemany语句中使用普通INSERT快速、安全地对抗 SQL 注入。从 Psycopg 3.1 开始，executemany() 在内部使用管道模式2。

启动管道模式和executemany() 的示例。由于 Psycopg 3.1 conn.pipeline() 仅对于execute() 是必需的，但如果只调用一次，则不再对于executemany() 是必需的。

args = [(1,2), (3,4), (5,6)]
with conn.pipeline():
  cur.executemany("INSERT INTO t (a, b) VALUES (%s, %s)", args)

Answer 6

我已经使用上面 ant32 的答案好几年了。但是我发现 python 3 中存在错误，因为 mogrify 返回一个字节字符串。

显式转换为字节字符串是使代码与 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)

Answer 7

executemany 接受元组数组

https://www.postgresqltutorial.com /postgresql-python/插入/

    """ array of tuples """
    vendor_list = [(value1,)]

    """ insert multiple vendors into the vendors table  """
    sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
    conn = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.executemany(sql,vendor_list)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

Answer 8

由 cursor.copyfrom 解决方案://stackoverflow.com/users/958118/joseph-sheedy">@jopseph.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() 填写字段值： *list(r.values())) for r in reports：

        line = "\t".join(["{}"] * len(records[0]))

        f = IteratorFile(line.format(*list(r.values())) for r in records)

要点在这里。

Answer 9

自此问题发布以来， execute_batch 已添加到 psycopg2 中。

它比 execute_values 更快。

Answer 10

另一种不错且有效的方法 - 是将要插入的行作为 1 个参数传递，
这是 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

Answer 11

如果您使用 SQLAlchemy，则无需手动编写字符串，因为 SQLAlchemy 支持为单个值生成多行 VALUES 子句INSERT语句：

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)

Answer 12

psycopg2 2.9.3

data = "(1, 2), (3, 4), (5, 6)"
query = "INSERT INTO t (a, b) VALUES {0}".format(data)
cursor.execute(query)

或

data = [(1, 2), (3, 4), (5, 6)]
data = ",".join(map(str, data))
query = "INSERT INTO t (a, b) VALUES {0}".format(data)
cursor.execute(query)

Answer 13

Psycopg 3 提供了一种使用 PostgresCOPY 的简单方法，而且效率很高。

records = [(1,2), (3,4), (5,6)]
with cursor.copy("COPY example_table (col_a, col_b) FROM STDIN") as copy:
    for record in records:
        copy.write_row(record)

其他信息可在官方文档中找到。

Answer 14

如果你想在一个插入语句中插入多行（假设你没有使用 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

Answer 15

来自@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()

Answer 16

我使用的解决方案可以在 1 毫秒内插入大约 8000 条记录

curtime = datetime.datetime.now()
postData = dict()
postData["title"] = "This is Title Text"
postData["body"] = "This a Body Text it Can be Long Text"
postData['created_at'] = curtime.isoformat()
postData['updated_at'] = curtime.isoformat()
data = []
for x in range(8000):
    data.append(((postData)))
vals = []
for d in postData:
    vals.append(tuple(d.values())) #Here we extract the Values from the Dict
flds = ",".join(map(str, postData[0]))
tableFlds =  ",".join(map(str, vals))
sqlStr = f"INSERT INTO posts ({flds}) VALUES {tableFlds}"
db.execute(sqlStr)
connection.commit()
rowsAffected = db.rowcount
print(f'{rowsAffected} Rows Affected')

Answer 17

最后在 SQLalchemy1.2 版本中，添加了这个新的实现，以便在使用 use_batch_mode=True 初始化引擎时使用 psycopg2.extras.execute_batch() 而不是 executemany，例如：

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在一起..

Answer 18

使用 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)

Answer 19

我构建了一个程序，可以将多行插入到位于另一个城市的服务器中。

我发现使用此方法比 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)

Answer 20

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 中执行此操作的 pythonic 方式：

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 语法需要一个记录列表，如下所示

   插入到 t(a, b) 值 (1, 'x'),(2, 'y')

2. Psycopg 将 Python tuple 改编为 Postgresql 记录。

唯一必要的工作是提供一个由 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< /code> 参数替换

cursor.execute(insert_query, data)

或者只是测试将发送到服务器的内容

print (cursor.mogrify(insert_query, data).decode('utf8'))

输出：

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

Answer 21

使用 psycopg2 2.7 更新：

经典的 executemany() 比 @ant32 的实现（称为“folded”）慢大约 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)])

---

上一个答案：

要插入多行，请使用 multirow使用 execute() 的 VALUES 语法比使用 psycopg2 executemany() 快约 10 倍。事实上，executemany() 只是运行许多单独的 INSERT 语句。

@ant32 的代码在 Python 2 中完美运行。但在 Python 3 中，cursor.mogrify() 返回字节，cursor.execute() 接受字节或字符串，并且','.join() 需要 str 实例。

因此，在 Python 3 中，您可能需要通过添加 .decode('utf-8') 来修改 @ant32 的代码：

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)