These three statements are named free_callback_contexts() below. But here they are problematic.

• If this is a brand new connection, then PyType_GenericAlloc has set all these to NULL and they don't need to be NULLed again.
• If someone is calling __init__ for the second time, this might free the Python callable, but SQLite will still have the pointer. Also the old db will leak, and who knows what'll happen to old cursors… Whoa, I don't see how reinitialization could work; should it rather raise an exception?

I had reinitialisation in mind, but as you say, it's hard to imagine how to do that properly anyway. Maybe implicit connection close at the start of __init__ will do the trick. I'll have to think that through. Perhaps we should fix the reinit case before proceeding with this PR.

Maybe implicit connection close at the start of __init__ will do the trick.

I would be surprised. Cursors seem to have hard (and reasonable!) assumptions about their connection's db.
I also don't see the use case – when would a new connection object not be enough?

Hm, true. Let's raise an exception instead.

I did some tests, and reinit actually seems to work pretty well using an implicit close in __init__, even with dangling cursors that fetch and execute after reinit. There's no refleaks on our side, and SQLite seems to be able to clean up as well. The only improvement would be to clear the callbacks from SQLite before the implicit close.

I also don't see the use case

Me neither.

There's no refleaks on our side, and SQLite seems to be able to clean up as well.

I'm surprised. Wouldn't this result in a sqlite3_open_v2 without a corresponding close?

Perhaps I was a little bit vague. I did the tests with an added implicit close in __init__.

Here's the diff I used:

diff --git a/Modules/_sqlite/connection.c b/Modules/_sqlite/connection.c
index 63fd916662..961afe8de0 100644
--- a/Modules/_sqlite/connection.c
+++ b/Modules/_sqlite/connection.c
@@ -56,6 +56,7 @@ static const char * const begin_statements[] = {
 static int pysqlite_connection_set_isolation_level(pysqlite_Connection* self, PyObject* isolation_level, void *Py_UNUSED(ignored));
 static void _pysqlite_drop_unused_cursor_references(pysqlite_Connection* self);
 static void free_callback_context(callback_context *ctx);
+static void connection_close(pysqlite_Connection *self);
 
 static PyObject *
 new_statement_cache(pysqlite_Connection *self, int maxsize)
@@ -114,6 +115,18 @@ pysqlite_connection_init_impl(pysqlite_Connection *self,
         return -1;
     }
 
+    // Gracefully handle reinitialization
+    if (self->initialized) {
+        (void)sqlite3_set_authorizer(self->db, NULL, NULL);
+        sqlite3_progress_handler(self->db, 0, NULL, NULL);
+#ifdef HAVE_TRACE_V2
+        sqlite3_trace_v2(self->db, SQLITE_TRACE_STMT, NULL, NULL);
+#else
+        sqlite3_trace(self->db, NULL, NULL);
+#endif
+        connection_close(self);
+    }
+
     pysqlite_state *state = pysqlite_get_state_by_type(Py_TYPE(self));
     self->state = state;
 

I see. Well, this is more complicated than I initially thought; I guess it should be a separate bpo issue.

I keep finding issues:

• db is not set to NULL if init fails.
• This segfaults for me:

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE foo (bar)')

try:
    conn.__init__('/bad-file/')
except sqlite3.OperationalError:
    pass

conn.execute('INSERT INTO foo (bar) VALUES (1), (2), (3), (4)')

... and even if that's fixed, I'm concerned that there will be other edge cases we can't reasonably think about.

Yes, there's some issues in connection init. I'd gladly clean it up and harden it before continuing with this PR.

It's not a blocker for this PR; just please open a bpo so the issues won't be lost in a closed PR.

I've opened issue 45126 for this.