Identical Databases in Flask-SQLAlchemy

Question

I already asked a similar question, but I thought that maybe I could rephrase it or show what I did next to shed light on what is happening here.

I currently have two identical databases, and I tried to solve the problem (on another issue that I saw) as follows:

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 is that now the last binding is used everywhere, so if I do it somewhere else:

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

He then receives the results from the second database. If I were to switch the locations of the SubTable classes, the results were the same (Edit for clearity: by which I mean that the results are based on what was determined last, if they were to switch, instead, the query is' bind2 'instead of' bind1 ', as it does now). I really don't know what to do, so if you can help in any way, it will be awesome.

Thanks.

EDIT: If this is not possible (or you just know better or even differently), do it, please let me know. If I could do something like two different db objects, that would be nice, I just don’t know how to do this or what the consequences might be.

EDIT 2: After you tackled this watch and watch, I finally came to the conclusion on how to do it.

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 the bindings can only be defined once and not changed, and no two table names can be the same, even if the bindings are different. So you need to make 2 instances of MetaData, otherwise SQLAlchemy will get angry. So the problem is the limitation in SQLAlchemy.

Answer 1

I don’t know what __bind_key__ , but there are many approaches to using one session with several connections. The session itself can be directly connected: for this, SubTable1 and SubTable2 must be displayed individually, and not part of the inheritance hierarchy, because Session finds a binding based on the base class. 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 in one direction. Another, let’s actually just use two different MetaData objects, simple 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 two MetaData objects, we can “bind” them directly if we want to go along this 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()