SQLAlchemy声明+跨多个不同数据库的关系

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

我花了一段时间，但我发现如何使用SQLAlchemy在两种不同的数据库之间建模关系:

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.

我必须这么做，因为TERM表位于Oracle数据库中，我只能在这个数据库中读取访问权限，我正在编写一个应用程序，记录关于这个术语的调查，由学生进行调查。

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.

sqlalchemy.exc。InvalidRequestError:初始化mapper mapper |测量|调查时，expression ' term_id==调查。term_id'未能找到一个名称(“name 'Term'未定义”)。如果这是一个类名，请考虑将此关系添加到类。

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

即使我在定义完Term之后添加了relationship()。

Does anyone have any suggestions?

有人有什么建议吗?

2 个解决方案

#1

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.

也许您可以通过ODBC将Oracle db连接到db层上的MySQL db，然后您只需与MySQL通信。我从来没有这样做过，我也不知道这是怎么回事。

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

您还可以独立地查询这两个数据库，并过滤和选择应用程序层上的数据，不管哪个工作量更少。

#2

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)

#1