Improve docs/docstrings of sqlite3 commit()/rollback() and close()

[erlend-aasland]

Originally posted by @mwichmann in #93890 (comment):

So (real question, no criticism): does that mean there ought to be more detail in the commit/rollback descriptions, rather than the context manager bit?

The wording of the docs and docstrings for the commit, rollback, and close connection methods can be improved, in order to provide more clarity around transaction handling.

Fixed with:

• gh-93925: Improve doc clarity of sqlite3 commit/rollback, and close #93926
• [3.11] gh-93925: Improve clarity of sqlite3 commit/rollback, and close docs (GH-93926) #94010
• [3.10] gh-93925: Improve clarity of sqlite3 commit/rollback, and close docs (GH-93926) #94011

[erlend-aasland]

PEP-249 says this about commit (the first sentence is of importance here):

Commit any pending transaction to the database.
Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.
Database modules that do not support transactions should implement this method with void functionality.

... and this about rollback (the second sentence is of importance here):

This method is optional since not all databases provide transaction support. [3]
In case a database does provide transactions this method causes the database to roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

Note, the proposed autocommit attribute will fix the auto-commit issues of sqlite3. That proposed feature will also fix the last sentence of the rollback specification; per now, an implicit rollback is not performed on close. No transaction control is performed on close.

[erlend-aasland]

PEP-249 says this about close:

Close the connection now (rather than whenever .del() is called).
The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. Note that closing a connection without committing the changes first will cause an implicit rollback to be performed.

We raise ProgrammingError if any operation is attempted with closed connections:

cpython/Modules/_sqlite/connection.c

Lines 504 to 520 in e37a158

	int pysqlite_check_connection(pysqlite_Connection* con)
	{
	if (!con->initialized) {
	pysqlite_state *state = pysqlite_get_state_by_type(Py_TYPE(con));
	PyErr_SetString(state->ProgrammingError,
	"Base Connection.__init__ not called.");
	return 0;
	}
	if (!con->db) {
	PyErr_SetString(con->state->ProgrammingError,
	"Cannot operate on a closed database.");
	return 0;
	} else {
	return 1;
	}
	}

No implicit rollback is performed at the moment. This will change in the future with the autocommit attribute.