execute_many returns None after successful insert

[euri10]

I have the following table

metadata = sqlalchemy.MetaData()
clients = sqlalchemy.Table(
    "clients",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String, nullable=False, unique=True),
    sqlalchemy.Column("dsn", sqlalchemy.String, nullable=False),
)

should I perform this:

            query2 = clients.insert()
            values =[
                {"name": "default", "dsn":"postgresql://foo:bar@host1/dev"},
                {"name": "local", "dsn":"postgresql://foo:bar@host2/dev"}
            ]
            rq2 = await database.execute(query=query2, values=values[0])

then I got rq2 that is equal to the id of the row inserted

now if I try to do

response = await database.execute_many(query=query, values=values)

rows are indeed correctly inserted, but I don't have the ids of the rows inserted, which sucks because they are foreign keys in another table I plan on using for further operations

question is: is that intended / normal in which case I'm ok to make a loop on the values I want to insert, grab the id inserted and deal with it later or is there a way to enhance execute_many ?

[szelenka]

execute and execute_many will always return None.

If you want to retrieve values, you'll need to use the fetch_all or fetch_one methods with your insert statement. You'll also need to add a RETURNING clause on your SQL statement. For sqlalchemy, I typically use bindparams something similar to :

stmt = sa.insert().values(
    name=sa.bindparam('name'),
    dsn=sa.bindparam('dsn')
).returning(clients.id)
values = [...]
results = await database.fetch_all(query=stmt, values=values)
for result in results:
    print(result['name'])

Your idea on a loop to iterate is correct in this packages current form. I haven't found a way to do a bulk add using named parameters. You can execute it with the execute_many with a list of values, and it'll do it in micro transactions, but you cannot get the results returned from that method.

[frjonsen]

Has there been any work on this issue, or is inserting each object one by one in order to retrieve the respective IDs still the only way?

[vmarkovtsev]

@frjonsen yep, is still the only intended way. However, you've got the raw_connection 😉 Remember to take the _lock if you are working with it.

[frjonsen]

@vmarkovtsev Thank for the head's up about raw_connection, although I'm not sure how using it would solve our problem. Is there a way to insert with a RETURNING clause and getting the results back by using the raw_connection?

The solution we went for is this. Since our table looks like the follow:

Foo= Table(
    "foo",
    metadata,
    Column("id", Text, primary_key=True, default=generate_foo_id),
    Column("owner_id", UUIDType, ForeignKey("user.id"), nullable=False),
    Column("creator_id", UUIDType, ForeignKey("user.id"), nullable=False),
    Column("used_at", DateTime(timezone=True)),
    Column("notes", Text),
    Column("created_at", DateTime(timezone=True), default=func.now(), nullable=False),
    Column("updated_at", DateTime(timezone=True), default=func.now(), onupdate=func.now(), nullable=False),
)

where generate_foo_id is a function we use for generating a unique string (because of business logic reasons we can't use an UUID here), we opted for generating all the defaults ourselves before inserting, i.e. we insert using:

now = datetime.utcnow()
for i in range(item_count):
    values.append(
        {
            "id": db_model.generate_foo_id(),
            "owner_id": owner_id,
            "creator_id": creator_id,
            "notes": notes,
            "created_at": now,
            "updated_at": now,
        }
    )

query = t.Foo.insert()

await self.database.execute_many(query, values)
return domain.Foo.parse_obj_list(values)

This isn't exactly ideal, since the table defines using func.now, but we instead use the Python-side datetime.utcnow, we could in theory get some issues here. In this case we just use the timestamps for troubleshooting, but there may be other cases where doing this is not an option. Since there are cases where we generate thousands of these objects at a time, doing one insert per item just wasn't an acceptable solution, so we will probably be using this "workaround" in the meantime, unless there is a better solution using the raw_connection directly.

[vmarkovtsev]

Is there a way to insert with a RETURNING clause and getting the results back by using the raw_connection?

Of course, it gives you direct access to the underlying DB driver. For example, it is asyncpg for Postgres, and it has execute, executemany, fetch, et cetera, et cetera.