> Is 0 a valid row ID in SQLite ? If not, then I guess this would be
> an alternative to None as suggested by the DB-API.

Yes. Any 64 bit signed integer value is a valid row id in SQLite.

> If it is a valid row ID, I'd suggest to go back to resetting to None,
> since otherwise code might get confused: if an UPDATE does not get
> applied (e.g. a condition is false), code could then still take
> .lastrowid as referring to the UPDATE and not a previous
> operation, since code will now know whether the condition was met
> or not.

True. OTOH, we've had no requests for reverting to pre Python 3.6 behaviour.

If we are to revert to this behaviour, we'll have to start examining the SQL we are given (search for INSERT and REPLACE keywords, determine if they are valid (i.e. not a comment, not part of a column or table name, etc.), which will lead to a noticeable performance hit for every new statement (not for statements reused via the LRU cache though). I'm not sure this is a good idea. However I will give it a good thought.

My first thought now, is that it would be better for the sqlite3 module to align lastrowid with the behaviour of the C API sqlite3_last_insert_rowid() (also available as an SQL function: last_insert_rowid). OTOH, the SQLite API is tied to the _connection_ object, so it may not make sense to align it with lastrowid which is a _cursor_ attribute.

Perhaps the Right Thing To Do™ is to be conservative and just leave it as it is. I still want to apply the optimisation, though. It does not alter the behaviour in any kind of way, and it speeds up executemany().