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)

Answer 1

You can achieve this with multiple engines and sessions (you don't need multiple metadata):

engine1 = create_engine("sqlite:///tmp1.db")
engine2 = create_engine("sqlite:///tmp2.db")
Base.metadata.create_all(bind=engine1)
Base.metadata.create_all(bind=engine2)
session1 = Session(bind=engine1)
session2 = Session(bind=engine2)
print(session1.query(Picture).all())  # []
print(session2.query(Picture).all())  # []
session1.add(Picture())
session1.commit()
print(session1.query(Picture).all())  # [Picture]
print(session2.query(Picture).all())  # []
session2.add(Picture())
session2.commit()
print(session1.query(Picture).all())  # [Picture]
print(session2.query(Picture).all())  # [Picture]
session1.close()
session2.close()

For scoped_session, you can create multiple of those as well.

engine1 = create_engine("sqlite:///tmp1.db")
engine2 = create_engine("sqlite:///tmp2.db")
Base.metadata.create_all(bind=engine1)
Base.metadata.create_all(bind=engine2)
Session1 = scoped_session(sessionmaker(bind=engine1))
Session2 = scoped_session(sessionmaker(bind=engine2))
session1 = Session1()
session2 = Session2()
...

If you have a variable number of databases you need to have open, scoped_session might be a little cumbersome. You'll need some way to keep track of them.