SQLAlchemy Declarative + relationships across multiple different databases

Question

It took me a while, but I figured out how to use SQLAlchemy to model a relationship across two different kinds 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's 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 have to do this because the TERM table is in an Oracle database on which I only have read access, and I'm writing an app that records surveys, taken by students, about that term.

The above works, but it's very fragile when the number of tables climbs up, as the Session needs to specify exactly which mapped classes correspond to which engine. I would really like to be able to use a different Base to define which tables belong to which engine, instead of binding each table individually. 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 results in the following error when the query runs:

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.

even though I did add the relationship() to Survey after Term was defined.

Does anyone have any suggestions?

In theory you could use a [metaclass](https://docs.python.org/3/reference/datamodel.html#metaclasses) to dynamically populate a dict of which class maps to which engine. Or dynamically check for all subclasses of `MySQLBase` / `OracleBase` when binding the session via `MySQLBase.__subclasses_()`. (haven't tested it) — Romuald Brunet, Nov 22 '19 at 09:23

score 9 · Answer 1 · answered Sep 27 '15 at 16:50

9

Possibly very late with this reply, but you could have defined the metadata separate from the declarative base and then passed it onto both. ie:

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)

answered Sep 27 '15 at 16:50

Victor Uriarte

479
4
9

1

Hmm, I'm not sure that was possible 4 years ago, when I asked this question. Quite useful for future googlers, though! – coredumperror Sep 27 '15 at 21:52
Future googler here: Is there a way this technique could be used to replace the Base inheritance on classes in a third party module? I'm trying to write some code that would benefit from relationships to another DB's objects, but at the moment the best way I think of to do this is to duplicate that code's model classes with the only change being inheriting from a different base. – mpounsett Nov 08 '20 at 16:54
@mpounsett I think what you are asking is not only possible, but its what I was using it for. Its been a few years since I've worked on this project, so I can't say if the technique still works. – Victor Uriarte Nov 09 '20 at 19:24

score 4 · Accepted Answer · answered Sep 15 '11 at 20:37

4

You can't. AFAIK there's no single query against two different databases. Also, your Models have to share the same Metadata instance to be used in the same query.

Perhaps you can link the Oracle db to the MySQL db on the DB layer via ODBC, then you'd only talk to MySQL. I have never done this, and I don't know how it works.

You can also query both databases independently and filter and select data on the application layer, whichever is less work.

answered Sep 15 '11 at 20:37

knitti

6,817
31
42

I figured it might be like that. I didn't know that your models need to share their Metadata to be in the same query, though. That's good to know. – coredumperror Sep 15 '11 at 21:56

SQLAlchemy Declarative + relationships across multiple different databases

2 Answers2

Linked