Skip to content

executemany not discarding result aka fetchmany #137

New issue

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

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

Already on GitHub? # 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).

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

No branches or pull requests

7 participants