SQLAlchemy Declarative + Relationships Between Several Different Databases

Question

It took me a while, but I figured out how to use SQLAlchemy to model the relationship between two different types of databases:

Base = declarative_base() class Survey(Base): __tablename__ = 'SURVEY' survey_id = Column("SURVEY_ID", Integer, primary_key=True) term_id = Column("TERM_ID", Integer, nullable=False) # Because the TERM table is in Oracle, but the SURVEY table is in # MySQL, I can't rely on SQLAlchemy ForeignKey. Thus, # I need to specify the relationship entirely by hand, like so: term = relationship("Term", primaryjoin="Term.term_id==Survey.term_id", foreign_keys=[term_id], backref="surveys" ) class Term(Base): __tablename__ = 'TERM' term_id = Column(Integer, primary_key=True) term_name = Column(String(30)) start_date = Column(Date) end_date = Column(Date) mysql_engine = create_engine(MYSQL) oracle_engine = create_engine(ORACLE) Session = scoped_session(sessionmaker( binds={ Term: oracle_engine, Survey: mysql_engine } )) if __name__ == "__main__": survey = Session.query(Survey).filter_by(survey_id=8).one() print survey.term print survey.term.surveys 

I need to do this because the TERM table is in the Oracle database, on which I only have read access, and I am writing an application that records the polls made by students about this term.

The above works, but it is very fragile when the number of tables goes up, since the session must specify exactly which mapped classes correspond to which engine. I would really like to be able to use another Base to determine which tables belong to that engine, and not to bind each table separately. Like this:

 mysql_engine = create_engine(MYSQL) oracle_engine = create_engine(ORACLE) MySQLBase = declarative_base(bind=mysql_engine) OracleBase = declarative_base(bind=oracle_engine) class Survey(MySQLBase): __tablename__ = 'SURVEY' survey_id = Column("SURVEY_ID", Integer, primary_key=True) term_id = Column("TERM_ID", Integer, nullable=False) class Term(OracleBase): __tablename__ = 'ads_term_v' term_id = Column(Integer, primary_key=True) term_name = Column(String(30)) start_date = Column(Date) end_date = Column(Date) Survey.term = relationship("Term", primaryjoin="Term.term_id==Survey.term_id", foreign_keys=[Survey.term_id], backref="surveys" ) Session = scoped_session(sessionmaker()) if __name__ == "__main__": survey = Session.query(Survey).filter_by(survey_id=8).one() print survey.term print survey.term.surveys 

Unfortunately, this leads to the following error when executing the query:

sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|Survey|SURVEY, expression 'Term.term_id==Survey.term_id' failed to locate a name ("name 'Term' is not defined"). If this is a class name, consider adding this relationship() to the <class '__main__.Survey'> class after both dependent classes have been defined.

although I did add a relation () to the survey after the term has been defined.

Does anyone have any suggestions?

Answer 1

You can not. AFAIK does not have a single query for two different databases. In addition, your models must use the same metadata instance that will be used in the same request.

Perhaps you can link Oracle db to MySQL db at the database level via ODBC, then you will only talk to MySQL. I have never done this, and I do not know how this works.

You can also query both databases yourself and filter and select data at the application level, depending on which works less.

Answer 2

It may be very late with this answer, but you could define the metadata separately from the declarative database, and then transfer it to both. i.e:

 meta = MetaData() mysql_engine = create_engine(MYSQL) oracle_engine = create_engine(ORACLE) MySQLBase = declarative_base(bind=mysql_engine, metadata=meta) OracleBase = declarative_base(bind=oracle_engine, metadata=meta)