Identical databases in Flask-SQLAlchemy

Question

I've already asked a similar question, but I thought maybe I could rephrase it, or show what I've done further to shed some light onto what's going on here.

Currently I have 2 identical databases, and I've attempted to solve the problem (as per another question I saw) like this:

class BaseTable(db.Model):
    __tablename__ = 'TableName'
    col = db.Column(db.Integer)

class SubTable1(BaseTable):
    __bind_key__ = 'bind1'

class SubTable2(BaseTable):
    __bind_key__ = 'bind2'

The problem with this is that now the most recent bind is used everywhere, so if I do this somewhere else:

SubTable1.query.filter_by(col=12).all()

Then it gets results from the second database. If I were to switch the locations of the SubTable classes, then the results are the same (Edit for clarity: by which I mean that the results come from whatever bind is defined last, if they were to be switched, it would instead query from 'bind2' instead of 'bind1' as it currently does). I don't really know what to do, so if you can help in any way that would be awesome.

Thanks.

EDIT: If it's impossible (or you simply know a better or even different way) to do this, please let me know. If I could do something like having two different db objects, that would be good as well, I just don't really know how to do that or what kind of implications that would have.

EDIT 2: After toiling with this for hours and hours, I've finally come to a conclusion on how to do this.

In __init__.py:

db1 = SQLAlchemy(app)
db2 = SQLAlchemy(app)

In models.py:

class Table1(db1.Model):
    __tablename__ = 'TableName'
    __bind_key__ = 'bind1'
    col = db1.Column(db1.Integer)

class Table2(db2.Model):
    __tablename__ = 'TableName'
    __bind_key__ = 'bind2'
    col = db2.Column(db2.Integer)

The reason for this nonsense is that binds can only be defined once and not changed, and no two table names can be the same, even if the binds are different. So you have to make 2 MetaData instances or else SQLAlchemy gets mad. So it turns out the problem is a limitation in SQLAlchemy.

Answer 1

I don't know what __bind_key__ is, but there are many approaches to using a single Session with multiple binds. Session itself can be bound directly: to do this, SubTable1 and SubTable2 need to be mapped individually and not part of an inheritance hierarchy, as the Session locates the bind based on the base-most mapped class. In order to share the same MetaData, just map both classes to the same Table object:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class BaseTable(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)

class SubTable1(Base):
    __table__ = BaseTable.__table__

class SubTable2(Base):
    __table__ = BaseTable.__table__

db1 = create_engine("sqlite:///db1.db", echo=True, logging_name='db1')
db2 = create_engine("sqlite:///db2.db", echo=True, logging_name='db2')

Base.metadata.create_all(db1)
Base.metadata.create_all(db2)

s = Session(binds={SubTable1: db1, SubTable2: db2})

s.add_all([
    SubTable1(),
    SubTable2(),
    SubTable1(),
    SubTable2(),
    SubTable1(),
])

s.commit()

print s.query(SubTable1).all()
print s.query(SubTable2).all()

that's one way. Another, let's actually just use two different MetaData objects, easy enough with mixins:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class BaseTable(object):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)

class DB1(Base):
    metadata = MetaData()
    __abstract__ = True

class DB2(Base):
    metadata = MetaData()
    __abstract__ = True

class SubTable1(BaseTable, DB1):
    pass

class SubTable2(BaseTable, DB2):
    pass

db1 = create_engine("sqlite:///db1.db", echo=True, logging_name='db1')
db2 = create_engine("sqlite:///db2.db", echo=True, logging_name='db2')

DB1.metadata.create_all(db1)
DB2.metadata.create_all(db2)

s = Session(binds={SubTable1: db1, SubTable2: db2})

s.add_all([
    SubTable1(),
    SubTable2(),
    SubTable1(),
    SubTable2(),
    SubTable1(),
])

s.commit()

print s.query(SubTable1).all()
print s.query(SubTable2).all()

and yes, since we have the two MetaData objects there, we can "bind" them directly, if we want to go that route:

# ... mapping as before

DB1.metadata.bind = db1
DB2.metadata.bind = db2
DB1.metadata.create_all()
DB2.metadata.create_all()

s = Session()  # don't need binds in this case

# ... usage as before
s = Session()