Getting row count from an update

[knyghty]

I'm aware of e.g. #108 but I'm wondering what the best way to get the row count for an update or delete is for now? On Postgres if it makes a difference.

[gvbgduh]

@knyghty as a workaround, for now, you can get down to the raw driver, like

async with database.connection() as connection:
    raw_connection = connection. raw_connection
    # `raw_connection` is `asyncpg.Connection` now
    # `but you'll need to pass the raw sql query (or render the one beforehand)
    status = raw_connection.execute(...)
    # `execute` method doesn't receive the response from the DB,
    # but returns some status, like `COPY 5`or `DELETE 20`

    # or you can try
    records = raw_connection.fetch("UPDATE ... RETURNING id")
    # records is `List[asyncpg.Record]` in this case

[knyghty]

Thanks, using returning seems like a reasonable solution.

I think it would be nice if these approaches were documented - as a user I find it a little surprising to get feedback from create but not from other DML statements.

[gvbgduh]

Yes, extending the documentation would be nice, but could you please elaborate a bit more on

as a user I find it a little surprising to get feedback from create but not from other DML statements.

Also, there's no special need for the raw driver in case of the returning clause, sqlalchemy-core also provides it, like

table.insert().values(**data).returning(literal_column("*"))

[knyghty]

Sure, I just mean that if I do an insert, I get back the primary key of the created row, e.g. I can simply write some_id = await db.execute(statement, values={...}) but this doesn't work for e.g. update.

[gvbgduh]

Hm, it's a bit confusing. I would probably also distinguish the row count and rows ids. So, by default, postgres will return the row count for insert/update/delete operations. asyncpg wraps is some string for the execute method (like DELETE 15), but the execute method does not fetch the data. All of those do support the returning clause that has to be explicitly provided. But it's at the lower level.

As of the high level, there's an open issue related to the support of such statuses for asyncpg.

And some_id = await db.execute(statement, values={...}) should not be able to return any sensible data even with the returning clause. But fetch_all/fetch_one will.

If you have an even more explicit example that would help to understand better.

[gnat]

Already submitted a patch for mysql & sqlite- patiently awaiting feedback / merge: https://github.com/encode/databases/pull/150

Would love to see the same thing for postgres.