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
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.
If you need to use the RETURNING
clause to obtain the ids back, then the following is the most efficient way of inserting multiple values:
res = await conn.fetch('''
INSERT INTO films (code, title, did, date_prod, kind)
(SELECT
r.code, r.title, r.did, r.date_prod, r.kind
FROM
unnest($1::films[]) as r
)
RETURNING id
''', [
(None, 'B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
(None, 'HG120', 'The Dinner Game', 140, None, 'Comedy')
])
Note that the records you pass as input must correspond to the shape of the table: PostgreSQL does not support arbitrary records as input, so you must use a known record type. Simply pass the columns you are not inserting as None
and don't include them in the SELECT
return list. This method also doesn't allow you to rely on DEFAULT
, you must specify each inserted value explicitly.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With