Skip to content

executemany not discarding result aka fetchmany #137

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
samuelcolvin opened this issue May 11, 2017 · 9 comments
Closed

executemany not discarding result aka fetchmany #137

samuelcolvin opened this issue May 11, 2017 · 9 comments
Labels

Comments

@samuelcolvin
Copy link
Contributor

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?

@elprans
Copy link
Member

elprans commented May 11, 2017

Try this:

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

@samuelcolvin
Copy link
Contributor Author

samuelcolvin commented May 11, 2017

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.

@elprans
Copy link
Member

elprans commented May 11, 2017

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

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

@jaredvacanti
Copy link

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.

@feluxe
Copy link

feluxe commented Nov 12, 2018

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.

@jiamo
Copy link

jiamo commented Nov 8, 2019

Any new progress?

@shurshilov
Copy link

Any news???

@jabajke
Copy link

jabajke commented Jan 10, 2025

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

@elprans
Copy link
Member

elprans commented Jan 10, 2025

Any news???

This was implemented in #1175 (as fetchmany).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

7 participants