[doc] fix inaccuracies in sqlite3.Cursor.lastrowid docs

[erlend-aasland]

BPO	46261
Nosy	@malemburg, @taleinat, @miss-islington, @erlend-aasland
PRs	bpo-46261: Update sqlite3.Cursor.lastrowid docs #30407 [3.10] bpo-46261: Update sqlite3.Cursor.lastrowid docs (GH-30407) #30487 [3.9] bpo-46261: Update sqlite3.Cursor.lastrowid docs (GH-30407) #30488

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2022-01-08.20:06:51.377>
created_at = <Date 2022-01-04.21:46:55.630>
labels = ['3.11', '3.9', '3.10', 'docs']
title = '[doc] fix inaccuracies in sqlite3.Cursor.lastrowid docs'
updated_at = <Date 2022-01-08.20:07:55.976>
user = 'https://github.com/erlend-aasland'

bugs.python.org fields:

activity = <Date 2022-01-08.20:07:55.976>
actor = 'taleinat'
assignee = 'docs@python'
closed = True
closed_date = <Date 2022-01-08.20:06:51.377>
closer = 'erlendaasland'
components = ['Documentation']
creation = <Date 2022-01-04.21:46:55.630>
creator = 'erlendaasland'
dependencies = []
files = []
hgrepos = []
issue_num = 46261
keywords = ['patch']
message_count = 8.0
messages = ['409713', '409714', '409715', '409716', '410106', '410109', '410110', '410111']
nosy_count = 5.0
nosy_names = ['lemburg', 'taleinat', 'docs@python', 'miss-islington', 'erlendaasland']
pr_nums = ['30407', '30487', '30488']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = None
url = 'https://bugs.python.org/issue46261'
versions = ['Python 3.9', 'Python 3.10', 'Python 3.11']

[erlend-aasland]

The sqlite3 docs say that lastrowid is set to None after executemany(), or after execute()'ing statements that are not INSERTs or REPLACEs. This is not true; in those cases, lastrowid is preserved. lastrowid is only None in a pristine cursor. Suggesting to reword the docs so they harmonise with the implementation.

Note, there is a quirk, or bug with lastrowid: it is set to 0 if the first statement that's execute()d on a cursor is a non-INSERT/REPLACE statement. For example:

    >>> import sqlite3
    >>> cx = sqlite3.connect(":memory:")
    >>> cu = cx.cursor()
    >>> cu.lastrowid
    >>> cu.execute("select 1")
    >>> cu.lastrowid
    0

This behaviour is consistent across current main through 3.7 (though in 2.7, the behaviour is consistent with the docs, so it probably changed a long time ago). I'm not sure it's worth noting this side effect in the docs though.

[erlend-aasland]

Oh, and the docs says that lastrowid "provides the rowid of the last modified row". This is not true; it provides the row id of the last _inserted_ row.

[erlend-aasland]

I also suggest to add a note about tables without rowids, quoting the SQLite docs:

Inserts into WITHOUT ROWID tables are not recorded.

[erlend-aasland]

FYI: https://sqlite.org/c3ref/last_insert_rowid.html

[taleinat]

New changeset b6aa38f by Erlend Egeberg Aasland in branch 'main':
bpo-46261: Update sqlite3.Cursor.lastrowid docs (GH-30407)
b6aa38f