Best way to insert multiple rows with asyncpg

Question

I want to insert multiple rows and get IDs back with asyncpg, i found two ways: 1: generate sql like this

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy')
RETURNING id;

2: use prepared statement in for loop

values =(('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
        ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'))
stmnt = connection.prepare("INSERT INTO films (code, title, did, date_prod, kind) VALUES $1, $2, $3, $4, $5  RETURNING id")
for val in values:
    stmnt.fetchval(*val)

which way i must prefer in case 100x times with 700 000 rows, or there is some way to combine this approaches? i totally green, so throw some tomattoes in me

Answer 1

asyncpg provides the executemany method to insert many rows.

statement = """INSERT INTO films (code,
                           title, 
                           did, 
                           date_prod, 
                           kind) VALUES($1, $2, $3, $4, $5);"""
await connection.executemany(statement, values)

If you need to use RETURNING as you later mentioned to return the inserted ids, this answer is the way to go.