Dynamically attaching a SQLite database with SQLAlchemy

Question

I've been able to successfully get SQLAlchemy and SQLite to work together for the purpose of vertical partitioning (partitioning certain tables into different databases).

The problem I have is that there are parts of the application that do not require the partitioned tables and if I load them via an "attach" command in those sessions that don't actually need it, then the attached database is effectively locked thereby eliminating the entire benefit of the partitioning.

I've tried with the following code where if the SQLALchemy object inherits from a certain base class, then I would attach the database for the session but this does not seem to work as I expect:

class LazyAttachDatabaseSession(orm.Session):
    def __init__(self, theEngine, dbpath, **kw):
        super().__init__(**kw)
        self._engine = theEngine
        self._dbpath = dbpath
        self._logger = logging.getLogger(__name__)

    def get_bind(self, mapper=None, clause=None):
        if mapper is not None and issubclass(mapper.class_, _Realtime):
            self._engine.execute(f"ATTACH DATABASE '{self._dbpath}' as 'realtime'")
        return self._engine

I configure the Session object as follows:

    _Session = orm.sessionmaker(theEngine=engine,
                                class_=LazyAttachDatabaseSession,
                                dbpath=realtimeDBPath)

But this doesn't work for a variety of reasons. For example, I receive the "database is already attached" error

Sohail Sohail · Accepted Answer · 2019-08-13T15:01:30

The "problem" if there can be such a thing is that SQLite connections are not pooled, and so the effect of the attach command is ephemeral/transient.

Assuming that you just want it to be set once per process/connection/thread, modify the code as follows:

class LazyAttachDatabaseSession(orm.Session):
    isAttached = False #NOTE: New class-level variable

    def __init__(self, theEngine, dbpath, **kw):
        super().__init__(**kw)
        self._engine = theEngine
        self._dbpath = dbpath
        self._logger = logging.getLogger(__name__)

    def get_bind(self, mapper=None, clause=None):
        if not LazyAttachDatabaseSession.isAttached and mapper is not None and issubclass(mapper.class_, _Realtime):
            self._logger.info("Attaching %s database as 'realtime'",self._dbpath)
            self._engine.execute(f"ATTACH DATABASE '{self._dbpath}' as 'realtime'")
            LazyAttachDatabaseSession.isAttached = True
        return self._engine

And configure the engine as follows:

    engine: sa.engine.Engine = create_engine(url,
                                             echo=debug,
                                             poolclass=sa.pool.SingletonThreadPool,
                                             connect_args={'timeout': 30})

Note the "poolclass" parameter. This should only attach the "realtime" db if it is actually needed.

Dynamically attaching a SQLite database with SQLAlchemy

1 Answers