[issue46249] [sqlite3] move set lastrowid out of the query loop

[Marc-Andre Lemburg]

Marc-Andre Lemburg <mal at egenix.com> added the comment:

On 04.01.2022 11:02, Erlend E. Aasland wrote:
> 
> Erlend E. Aasland <erlend.aasland at innova.no> added the comment:
> 
> Thank you for your input Marc-André.
> 
> For SQLite, it's pretty simple: we use an API called sqlite3_last_insert_rowid() which takes the database connection as it's argument, not a statement pointer. This function returns "the rowid of the most recent successful INSERT into a rowid table or virtual table on database connection" (quote from SQLite docs). IMO, it would make sense to also use this post executemany().

Sounds like a plan.

If possible, it's usually better to have the .executemany() create a
cursor with an output array providing the row ids, e.g. using "INSERT ...
RETURNING ..." (PostgreSQL). That way you can access all row ids and
can also provide the needed detail in case the INSERTs happen out of
order to map them to the input data.

For cases where you don't need sequence IDs, it's often better to
not rely on auto-increment columns for IDs, but instead use random
pre-generated IDs. Saves roundtrips to the database and works nicely
with cluster databases as well.

----------

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue46249>
_______________________________________