Python DB API - commit() v. execute("commit transaction")?

[Chris Angelico]

On Wed, May 31, 2017 at 11:06 AM, Ian Kelly <ian.g.kelly at gmail.com> wrote:
> What if you want to set cursor options before executing the query,
> e.g. Cursor.setoutputsize? It doesn't make sense as a Connection
> method, but it needs to be called before execute. Then you need to
> access the cursor before it has a result set.

If cursors have value as sub-transaction concepts, it must be possible
to create multiple of them, apply different options to them, and then
execute queries on them independently. Is this the case? Actually, it
probably doesn't need to be, as there's not really any options where
you'd care about that (if something emulated cursors by simply picking
the largest buffer size ever selected, you'd probably never see the
difference). So the value of a pre-query cursor is fairly minimal.

In the DB API 3.0, what I would like to see is that the connection
becomes a context manager that gives you a transaction object. With
that transaction, you can perform queries, and you could create
cursors from it if you want them, or just use a single in-built
cursor. But a cursor would relate to a result set, and would have no
meaning outside of it. At the moment, we don't have any object
representation of a transaction, which means that (unless I'm missing
something here) it's fundamentally impossible to have concurrent
transactions on a single connection.

ChrisA