Marc-Andre Lemburg <m...@egenix.com> added the comment: On 11.01.2022 20:46, Erlend E. Aasland wrote: > > 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.
I've had a look at the API description and find it less than useful, to be honest: https://sqlite.org/c3ref/last_insert_rowid.html You don't know on which cursor the last row was inserted, it's possible that this was or is done by a trigger and the last row is not updated in case the INSERT does not succeed for some reason, leaving it unchanged - without the user getting a notification of this failure, since the .execute() call itself will succeed for e.g. "INSERT INTO table SELECT ...;". It also seems that the function really only works for INSERTs and not for UPDATEs. > 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(). I'd suggest to deprecate the cursor.lastrowid attribute and instead point people to the much more useful "INSERT INTO t (name) VALUES ('two'), ('three') RETURNING ROWID;" https://sqlite.org/lang_insert.html https://sqlite.org/forum/forumpost/058ac49cc3 (good to know that SQLite has adopted this PostgreSQL variant as well) RETURNING is also available for UPDATES: https://sqlite.org/lang_update.html If people really want to use the sqlite3_last_insert_rowid() functionality, they can use the SQL function of the same name: https://www.sqlite.org/lang_corefunc.html#last_insert_rowid which then has known semantics and doesn't conflict with the DB-API specs. But this is your call :-) ---------- _______________________________________ Python tracker <rep...@bugs.python.org> <https://bugs.python.org/issue46249> _______________________________________ _______________________________________________ Python-bugs-list mailing list Unsubscribe: https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
