executemany not discarding result aka fetchmany

With the following query

INSERT INTO people (name) VALUES ($1)
RETURNING id

and values to create [('anne',), ('ben',), ('charlie',)].

I believe there's currently no way with asyncpg to execute this single query and get all the ids returned.

I've tried:

executemany but the result is discarded
fetch but that doesn't extend to executing many
fetch while casting the argument to an array ::TEXT[][] but I either get a syntax error or a single value of the stringified list insert.

In short: would it be possible for executemany to return the result?

Try this:

await conn.fetch('''
    INSERT INTO people (name) (SELECT unnest ($1))
    RETURNING id
''', ['anne', 'ben', 'charlie'])

Thanks, that got me to the solution, I actually needed

await db_conn.fetch("""
    INSERT INTO recipients (address)  (SELECT unnest ($1::TEXT[]))
    RETURNING id, address
""", ['anne', 'ben', 'charlie'])

(Note the ::TEXT[])

Can I suggest a new section for docs "example of sql with asyncpg"? I know this is technically an issue with my knowledge of postgres not asyncpg, but such a section would ease the learning curve of using asyncpg a lot and avoid you having to answer these questions.

Can I suggest a new section for docs "example of sql with asyncpg"?

Yes, I'm actually working on it right now.

Is it possible to accomplish this same executemany with RETURNING while passing in a list of dicts/mappings?

For the pyscopg2 alternative, it would be usingpsycopg2.extras.execute_values which requires a template of the columns. This then supports default values.

I'm having the same problem with this query:

INSERT INTO persons (lastname, firstname) VALUES ($1, $2) RETURNING id;

data = [
    ('doe', 'john'),
    ('doe', 'jane'),
    ...
]

I was looking for fetchmany intuitively, without luck, so I landed here :) I think a fetchmany function would be really handy for situations like this.

I ended up looping over the list of persons executing a single fetch call for each.

Any new progress？

Any news???

if its possible, you may use asyncpg with jinjasql

we have workaround for such cases

insert into ...
values
    {% for row in data %}
        ({{ row.column1 }}, {{ row.column2 }})
    {% if not loop.last %}, {% endif %}
    {% endfor %}
returning column1, column2

prbly it would be useful for someone

Any news???

This was implemented in #1175 (as fetchmany).

Navigation Menu

executemany not discarding result aka fetchmany #137

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

executemany not discarding result aka fetchmany #137

Description

Activity

elprans commented on May 11, 2017

samuelcolvin commented on May 11, 2017

elprans commented on May 11, 2017

jaredvacanti commented on Mar 28, 2018

feluxe commented on Nov 12, 2018

jiamo commented on Nov 8, 2019

shurshilov commented on Dec 2, 2024

jabajke commented on Jan 10, 2025

elprans commented on Jan 10, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions