sqlite3 usage of context manager leads to memory accumulation #123739
Description
Bug report
Bug description:
Running the following script leads to an increasing amount of memory usage of the python process.
# Sqlite3 memory consumption minimal example
## setup
import time
import sqlite3
class Foo:
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
## Running it
def main():
db = sqlite3.connect("dummy.db")
db.execute("CREATE TABLE IF NOT EXISTS dummy(x INTEGER PRIMARY KEY ASC, y, z)")
for i in range(10_000_000):
db.execute("INSERT INTO dummy (y, z) VALUES (?, ?)", (i,i+2))
db.close()
for i in range(1_000):
with sqlite3.connect("dummy.db", check_same_thread=False) as db:
cursor = db.cursor()
cursor.execute("SELECT * FROM dummy WHERE x = ?;", (i,))
rows = [Foo(r[0], r[1], r[2]) for r in cursor.fetchall()]
print(i)
time.sleep(0.1)
main()Running the following script keeps a constant memory usage.
# Sqlite3 memory consumption minimal example
## setup
import time
import sqlite3
class Foo:
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
## Running it
def main():
db = sqlite3.connect("dummy.db")
db.execute("CREATE TABLE IF NOT EXISTS dummy(x INTEGER PRIMARY KEY ASC, y, z)")
for i in range(10_000_000):
db.execute("INSERT INTO dummy (y, z) VALUES (?, ?)", (i,i+2))
db.close()
for i in range(1_000):
db = sqlite3.connect("dummy.db", check_same_thread=False)
cursor = db.cursor()
cursor.execute("SELECT * FROM dummy WHERE x = ?;", (i,))
rows = [Foo(r[0], r[1], r[2]) for r in cursor.fetchall()]
db.close()
print(i)
time.sleep(0.05)
main()This leads me to believe that the context manager, of which its usage is recommended, leads to an unnecessary amount of memory usage. I suspect this is related to closing the database connection. When omitting the db.close call, similar behavior occurs as when using the context manager.
CPython versions tested on:
3.12
Operating systems tested on:
macOS