sqlalchemy具有相同表名的多个数据库不起作用

Question

我使用SQLAlchemy在Python中使用了两个数据库，这两个数据库共享表名，因此在运行代码时会收到错误消息。

错误消息为：

sqlalchemy.exc.InvalidRequestError: Table 'wo' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

简化后的代码如下：

from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from mysql.connector.connection import MySQLConnection

Base = declarative_base()



def get_characterset_info(self):
    return self.get_charset()

MySQLConnection.get_characterset_info = MySQLConnection.get_charset


mysqlengine = create_engine('mysql+mysqlconnector://......../mp2', echo=True)
MYSQLSession = sessionmaker(bind=mysqlengine)     
mysqlsession= MYSQLSession()                      


MP2engine = create_engine('mssql+pyodbc://......../mp2', echo=True)
MP2Session = sessionmaker(bind=MP2engine)     
mp2session= MP2Session()                      


class MYSQLWo(Base):
    __tablename__= 'wo'

    wonum = Column(String, primary_key=True)
    taskdesc = Column(String)    


    comments = relationship("MYSQLWocom", order_by="MYSQLWocom.wonum", backref='wo')



class MYSQLWocom (Base):
    __tablename__='wocom'

    wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
    comments = Column(String, primary_key=True)





class MP2Wo(Base):
    __tablename__= 'wo'

    wonum = Column(String, primary_key=True)
    taskdesc = Column(String)    


    comments = relationship("MP2Wocom", order_by="MP2Wocom.wonum", backref='wo')


class MP2Wocom (Base):
    __tablename__='woc'

    wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
    location = Column(String)
    sublocation1 = Column(String)
    texts = Column(String, primary_key=True)

如何处理具有相同表结构的数据库？我猜这与MetaData实例有关，但是在讨论类的声明性用法和经典用法的区别时，SQLAlchemy文档会让人感到有点困惑。

Answer 1

由于实际上这些表具有不同的结构，因此解决方案是简单地创建一个单独的声明性基础。如果这两个表确实具有相同的结构，那么我只需要为这两个表使用一个类。

Base = declarative_base()
Base2 = declarative_base() #this is all I needed

class MYSQLWo(Base):
....
class MYSQLWocom(Base):
....
class MP2Wo(Base2): 
....
class MP2Wocom(Base2)

http://groups.google.com/group/sqlalchemy/browse_thread/thread/afe09d6387a4dc69?hl=en

Answer 2

您可以将一个db实例与两个模型一起使用，以绕过此问题。

这也可用于在Flask-SQLAlchemy中实现主/从用例。

就像这样：

app = Flask(__name__)
app.config['SQLALCHEMY_BINDS'] = {'rw': 'rw', 'r': 'r'}
db = SQLAlchemy(app)
db.Model_RW = db.make_declarative_base()

class A(db.Model):
    __tablename__ = 'common'

class B(db.Model_RW):
    __tablename__ = 'common'