Insert a list to postgres table

Question

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

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?

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

Answer 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)