Am I misunderstanding threading or using it inefficiently for a persistant base class used by utilities?

I have a base class for a utility that is Persistant that is used to manage scoped_sessions (from sqlalchemy) to a database. The sessions are stored in a dict that is supposed to be stored in local threading, the key being used based on the utility using the base class.
I'm afraid I am misunderstanding how threading is used and am just making a new scoped session each time the utility is called.

My base class:

from persistent import Persistent
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Table, MetaData, create_engine
import threading
from zope.interface import implements
from zope.sqlalchemy import datamanager as tx
from zope.interface import Interface

class IDatabaseConnectionManager(Interface):

    def dsn(self):
        """ Property for DSN used to connect to database - Needs to be defined
            by class inheriting DatabaseConnectionManager
        """

    def session(self):
        """ Property that can be retrieved to get Session object
        """   

    def sessionkey(self):
        """ Property for session key of database utility - Needs to be defined
             by class inheriting DatabaseConnectionManager
        """

class DatabaseConnectionManager(Persistant):
    implements(IDatabaseConnectionManager)

    def __init__(self):
        self._engine = None
        self._metadata = None
        self._threadlocal = threading.local()
        self._session = scoped_session(sessionmaker(
           extension=tx.ZopeTransactionExtension(),
           twophase=False
           ))

    @property        
    def sessionkey(self):
        raise Exception('property should be defined in class inheriting DatabaseConnectionManager')

    @property
    def dsn(self):
        raise Exception('property should be defined in class inheriting DatabaseConnectionManager')

    @property
    def session(self):
        """Create and store session in a dict stored in local threading"""
        if 'sessions' not in self._threadlocal.__dict__.keys():
            setattr(self._threadlocal,'sessions',{})
        if self.sessionkey not in self._threadlocal.sessions.keys():
            self._session.configure(bind=self.engine)
        self._threadlocal.sessions[self.sessionkey] = self._session
        return self._threadlocal.sessions[self.sessionkey]

    @property
    def engine(self):
        if self._engine is None:
            self._engine = create_engine(self.dsn,convert_unicode=True,encoding='utf-8')
            self.metadata.bind = self._engine
            self.setupMappers()
        return self._engine

    @property
    def metadata(self):
        if self._metadata is None:
            self._metadata = MetaData()
        return self._metadata        

One of my utilties using the DatabaseConnectionManager

class MyDBUtility(DatabaseConnectionManager):
    
    @property
    def dsn(self):
        """ value To be retrieved from registry """
        return "mysql://plone_dbuser:mypw@mydomainip/databasename?charset=utf8

    @property
    def sessionkey(self):
        return 'mydbutility'

I apologize for my long code, but I wanted to show how I was creating sessions.

Is my use of a dict containing sessions wrong?

I was thinking if I had multiple database utilities, this would've been useful. I don't have a second database to test, but I was curious if local threading could help me.

class AnotherDatabaseUtility(DatabaseConnectionManager):
    
    @property
    def dsn(self):
        """ value To be retrieved from registry """
        return "mysql://plone_dbuser:mypw@mydomainip/anotherdatabasename?charset=utf8

    @property
    def sessionkey(self):
        return 'anotherdbutility'

I'm no where sure what you are trying to do, but normally a utility doesn't need to be persistent unless you want to store something. And even then I normally just take the site root and store something there, or put stuff in the registry.

I'm guessing you'll be better of using https://github.com/collective/collective.saconnect or https://pypi.org/project/z3c.saconfig/

-Roel

Plone Foundation Code of Conduct