databases: Support returning the rowcount for a given query?

Currently we’re underspecifying the return type from execute, and returning the lastrowid value.

It looks like we probably ought to be returning a richer interface from that, with at least .lastrowid, and .rowcount attributes being supported. Does that sound like the right interface to you folks?

@tomchristie execute does not return feedback when deleting rows. Returning rows affected would be the standard MySQL way.

https://github.com/encode/databases/blob/88b381ae33bcb417b76b8df9149742cbbcc253b5/databases/backends/mysql.py#L134

Always returns 0 because the row no longer exists.

Is there any way to get feedback from a delete?

Is there a separate issue tracking getting the count of affected rows from statements like DELETE FROM and UPDATE or is this the one?

but to complicate things we don’t really expose a way to call .execute() without a raw connection (we only call wrappers like fetch).

Not quite sure what you meant here.

Sorry, I wrote that in a bit of a rush. I meant we do not directly call asyncpg.execute() anywhere useful, and only use asyncpg wrappers around it like fetch(). This means we don’t really expose a way to get the SQL status code.

So, our API for execute() is currently defined as returning the last cursor id. See https://github.com/encode/databases/blob/master/tests/test_databases.py#L236 for an example.

Are the status messages specific to postgres, or are they supported by mysql/sqlite too?

We could have something like a flag for returning="cursor|status" to switch between using asyncpg's fetchval()(in cases where you want the inserted pk) and using asyncpg'sexecute()` (in cases where you want the raw status message)?

Alternately we could change the interface for execute so that it returns more information (Tho I guess that doesn’t work as we either want to call execute or fetchval right?

I think we should not go down this path. I asked in the #sqlalchemy IRC channel and they said that it’s mostly delegated to the underlying database driver.

Plus the rowcount is available on classic DBAPI cursors that also return data, and it seems that using returning would exclude any results from being fetched.

All in all, having access to the rowcount of the underling cursor would be really great, but it requires each of our backends to support it. Fixing it here seems to be the wrong layer.

Edit: In that regard it seems asyncpg is the only backend driver we support that doesn’t expose a rowcount attribute.

I did a bit of digging. This issue is quite enlightening: https://github.com/MagicStack/asyncpg/issues/359

The tl;dr is that execute() in asyncpg returns the ‘status’ of the last command executed. This includes the rowcount: https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.execute

I’m not sure the best place to fix this. aiopg doesn’t seem to handle it itself. We could parse the status of the last SQL command that’s executed, but to complicate things we don’t really expose a way to call .execute() without a raw connection (we only call wrappers like fetch).

Urgh.