将列表插入postgres表

I want to insert a list of values to a Postgres table through Python:

我想通过Python向Postgres表中插入一个值列表:

The values are in a list say:

这些值在列表中说:

new=
[
    [gold, dresden, 1000, 24],
    [silver, Cologne, 600, 42],
    [gold, Aachen, 3000, 25]
]

And the table has already been created in Postgres with the headers. How do I do the insertion?

该表已经在Postgres中创建了标题。我该如何插入?

db.execute("INSERT INTO B4_O (position, city, amount, score) VALUES (%s,%s,%s)", new)
db.commit

But this gives me error:

但这给了我错误:

not all arguments converted during string formatting

并非在字符串格式化期间转换所有参

2 个解决方案

#1

Use psycopg2.extras.execute_values():

new = [
    ['gold', 'dresden', 1000, 24],
    ['silver', 'Cologne', 600, 42],
    ['gold', 'Aachen', 3000, 25]
]

from psycopg2.extras import execute_values

execute_values(db, "INSERT INTO B4_O (position, city, amount, score) VALUES %s", new)

#2

The interface db.execute() you are using is executing a SQL INSERT command. You can refer to the relevant documentation; you need a string of the form

您正在使用的接口db.execute()正在执行SQL INSERT命令。您可以参考相关文档;你需要一个表格的字符串

INSERT INTO B4_O (position, city, amount, score) 
       VALUES ("gold", "dresden", 1000, 24),
              ("silver", "Cologne", 600, 42),
              ("gold", "Aachen", 3000, 25)

A simple way to get the values in python is:

在python中获取值的一种简单方法是:

','.join(['("{}", "{}", {}, {})'.format(*x) for x in new])

Note that some python lib like SQLAlchemy support multiple rows insertion out of the box.

请注意,像SQLAlchemy这样的一些python库支持多行插入开箱即用。

#1