Also, note that if identical SQL statements are created in multiple cursors belonging to the same connection, they will currently _not_ be added to the connection weak ref list. See the if (self->statement->in_use) in the middle of _pysqlite_query_execute(). Thus, the current code actually fails to register _all_ statements in the "all statements" weak ref list. IMO, this is yet another argument to abandon that list.

Side note: This branch is not exercised in the unit test suite. Adding the following test will cover this branch:

diff --git a/Lib/sqlite3/test/dbapi.py b/Lib/sqlite3/test/dbapi.py
index c17f911fa1..8e53c657a6 100644
--- a/Lib/sqlite3/test/dbapi.py
+++ b/Lib/sqlite3/test/dbapi.py
@@ -526,6 +526,10 @@ def test_last_row_id_insert_o_r(self):
         ]
         self.assertEqual(results, expected)
 
+    def test_same_query_in_multiple_cursors(self):
+        cursors = [self.cx.execute("select 1") for _ in range(3)]
+        for cu in cursors:
+            self.assertEqual(cu.fetchall(), [(1,)])
 
 class ThreadTests(unittest.TestCase):
     def setUp(self):


See bpo-43553 for sqlite3 coverage improvements.