What happens if you use the connection after you call close()? Are we checking for NULL for these veriables everywhere else? (Cannot check myself as I'm on my phone)

You'll get a sqlite3.ProgrammingError. There's a lot of sanity checks in the code for this. The sqlite3 test suite runs fine, but of course, we do not have 100% code coverage yet. (Digression: see issue 43553 for improving sqlite3 code coverage.)

After close, if you try to use a cursor, fetch from a pending statement, or manipulate the connection, you'll get a sqlite3.ProgrammingError:

>>> cx = sqlite3.connect(":memory:")
>>> cu = cx.cursor()
>>> res = cu.execute("select 1")
>>> cx.close()
>>> res.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Cannot operate on a closed database.
>>> cu.execute("select 1")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Cannot operate on a closed database.
>>> cx.create_function("test", 1, lambda x: x)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Cannot operate on a closed database.
>>> 

I added some more tests wrt. this: 1b23df1
Also, there's some regression tests that exercise operations on closed connections (and closed cursors).

Let me know if you want me to add more tests.

Here's a strange observation:
I did a ref leak test on Windows yesterday, and it segfaulted on test_bpo31770. After some testing, I reverted the change highlighted in this conversation; I removed the three Py_CLEAR, added pysqlite_do_all_statements(self, ACTION_FINALIZE, 1) back, but kept connection_close(). That fixed the ref leak test on Windows, and the test suite still worked; it did not "leak" open test database files. So, for the new PR, I'll keep this modification. We'll see how the CI fares :)

cc. @vstinner

CI seems to be fine with this: erlend-aasland#9