Database connection is not released when dataset.Database.close() function is called. Was found when trying to create, use, and delete sqlite databases when unit testing some of my own code.
Example to Reproduce Issue
import os
import dataset
# Connect to a database and perform an action that will invoke dataset.Database.executable() function
db = dataset.connect('sqlite:///test.db')
table = db['user']
table.insert(dict(name='John Doe', age=46, country='China'))
db.close()
# Attempting to delete database file will fail with the file still being locked.
os.remove('test.db')
Research
Dug into the issue a little. Calling dispose on a sqlalchemy engine will not close connections that have been checked out and not returned to the connection pool. The connections opened in the dataset.Database.executable() function and stored in self.local.conn are never returned to the connection pool, and thusly are not closed by self.engine.dispose(). Here is the relevant text from sqlalchemy docs: https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal
A Possible Fix
I got something working by moving away from using threading.local() for storing the connectoins in dataset.Database. Instead, I added a self.connections = {} dictionary, where the key is the current thread identifier and the value being the sqlalchemy connection. Threw the lock around checking/modifying that dictionary, and in the dataset.Database.close() function I iterate over the dictionary, close all the connections, and finally clear the dictionary (again inside the lock). It seems to work. If I get a chance this week, I'll throw together a PR for it.
Issue
Database connection is not released when dataset.Database.close() function is called. Was found when trying to create, use, and delete sqlite databases when unit testing some of my own code.
Example to Reproduce Issue
Research
Dug into the issue a little. Calling dispose on a sqlalchemy engine will not close connections that have been checked out and not returned to the connection pool. The connections opened in the
dataset.Database.executable()function and stored inself.local.connare never returned to the connection pool, and thusly are not closed byself.engine.dispose(). Here is the relevant text from sqlalchemy docs: https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposalA Possible Fix
I got something working by moving away from using threading.local() for storing the connectoins in dataset.Database. Instead, I added a
self.connections = {}dictionary, where the key is the current thread identifier and the value being the sqlalchemy connection. Threw the lock around checking/modifying that dictionary, and in thedataset.Database.close()function I iterate over the dictionary, close all the connections, and finally clear the dictionary (again inside the lock). It seems to work. If I get a chance this week, I'll throw together a PR for it.Thank you!
Thank you for this wonderful library!