CODEWITHSUNDEEP
pythonpostgresqlsqlalchemybulkinsert
Aviad Nissel
Aviad Nissel

Reputation: 357

SQLAlchemy insert returning primary key with multiple values

I'm trying to use SQLAlchemy's insert and execute methods with a list of dictionaries as the values, and to get back the inserted ids. The database is Postgres, which should support RETURNING.

The code looks more or less like this:

table = MyThing.__table__
insert_stmt = table.insert().values({"name": bindparam("name_val")}).returning(table.c.id)
values = [{"name_val": "a"}, {"name_val": "b"}]
result = session.execute(insert_stmt, values).fetchall()

(trimmed down for readability and some obfuscation)

When the value list is bigger than one (as in the example above) I get:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) no results to fetch

but when the list only have one value the new id is returned properly.

I understand that I probably missed something in the way I'm using returning but I can't figure out what.

Upvotes: 5

Views: 5706

Answers (1)

Oluwafemi Sule
Oluwafemi Sule

Reputation: 38952

The same bind parameter is repeated in the list of values you're passing in the second argument to session.execute.

values ought to be a list when inserting multiple values and sqlalchemy by default binds the parameters unless literal_binds option is set to True so that it is safe to do without explicitly binding the parameters

values = [{"name_val": "a"}, {"name_val": "b"}]
insert_stmt = table.insert().values(values).returning(table.c.id)
result = session.execute(insert_stmt).fetchall()

Say you wanted to keep the existing explicit binding, values passed for the insert statement must be a keyword argument. Your existing query will be updated as follow:

bind_params = [{"name_val": "a"}, {"name_val": "b"}]
insert_stmt = table.insert().values(name_val=bindparam("name_val")).returning(table.c.id)
result = session.execute(insert_stmt, bind_params).fetchall()

Upvotes: 5

Related Questions