Using multiple databases with single sqlalchemy model

Question

I want to use multiple database engines with a single sqlalchemy database model.

Following situation: I have a photo album software (python) and the different albums are stored in different folders. In each folder is a separate sqlite database with additional information about the photos. I don't want to use a single global database because with this way I can simply move, delete and copy albums on a folder base. Opening a single album is fairly straightforward:

Creating a db session:

maker = sessionmaker(autoflush=True, autocommit=False,
                 extension=ZopeTransactionExtension())
DBSession = scoped_session(maker)

Base class and metadata for db model:

DeclarativeBase = declarative_base()
metadata = DeclarativeBase.metadata

Defining database model (shortened):

pic_tag_table = Table('pic_tag', metadata,
                      Column('pic_id', Integer,
                             ForeignKey('pic.pic_id'),
                             primary_key=True),
                      Column('tag_id', Integer,
                             ForeignKey('tag.tag_id'),
                             primary_key=True))


class Picture(DeclarativeBase):
    __tablename__ = 'pic'

    pic_id = Column (Integer, autoincrement = True, primary_key=True)
    ...


class Tags(DeckarativeBase):
    __tablename__ = 'tag'

    tag_id = Column (Integer, autoincrement = True, primary_key=True)
    ...

    pictures = relation('Picture', secondary=pic_tag_table, backref='tags')

And finally open the connection:

engine = engine_from_config(config, '...')
DBSession.configure(bind=engine)
metadata.bind = engine

This works well for opening one album. Now I want to open multiple albums (and db connections) the same time. Every album has the same database model so my hope is that I can reuse it. My problem is that the model class definition is inheritet from the declarative base which is connected to the metadata and the database engine. I want to connect the classes to different metadata with different enginges. Is this possible?

P.S.: I also want to query the databases via the ORM, e.g. DBSession.query(Picture).all() (or DBSession[0], ... for multiple sessions on different databases - so not one query for all pictures in all databases but one ORM style query for querying one database)

Using multiple databases with single sqlalchemy model

1 Answers