sqlite3 connections always create a reference cycle

Bug report

Bug description:

This is perhaps an annoyance rather than a bug, but it's nice to avoid creating reference cycles if possible. Practically, this means that the ResourceWarning from an sqlite connection object going out of scope fires from a random irrelevant line - where the cyclic GC happens to run - rather than predictably where the object goes out of scope.

The cycle is between the connection object and a functools _lru_cache_wrapper object, as can be seen like this:

import gc
import sqlite3

conn = sqlite3.connect(":memory:")
referrers = gc.get_referrers(conn)
for obj1 in gc.get_referents(conn):
    for obj2 in referrers:
        if obj1 is obj2:
            print(obj1)

If I'm following the relevant C code correctly, it's similar to doing this in Python:

# In Connection.__init__
self.statement_cache = lru_cache(maxsize)(self)

# In Cursor get_statement_from_cache
self.connection.statement_cache(sql)

I believe the reference cycle could be avoided by doing something like this:

# A function (/staticmethod etc.) not bound to the Connection instance
def compile_stmt(wr_conn, sql):
    conn = wr_conn()
    assert conn is not None
    return conn(sql)

# In Connection.__init__
self.statement_cache = lru_cache(maxsize)(compile_stmt)

# In Cursor get_statement_from_cache
wr_conn = weakref.ref(self.connection)
self.connection.statement_cache(wr_conn, sql)

Or functools.partial could be used to wrap the function so that the weakref is only created once. The significant bit is the connection's cache having only weakrefs back to the connection object. I'm not great at writing C code, but I think I can see that everything required for this is available as C functions.

Thanks!

CPython versions tested on:

3.14

Operating systems tested on:

Linux

Linked PRs

• gh-144383

Bắt đầu với các điểm vào sqlite3 Connection.init và Cursor get_statement_from_cache được mô tả trong báo cáo, sau đó xem xét PR gh-144383 được liên kết. Công việc hoàn tất khi bộ nhớ đệm kết nối không còn tạo ra chu kỳ tham chiếu và hành vi của ResourceWarning có thể dự đoán được khi kết nối ra khỏi phạm vi.