sqlalchemy multiple databases with same table names not working

Question

I have two databases that I'm working with in with Python using SQLAlchemy, the databases share table names and therefore I'm getting an error message when running the code.

The error message is :

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.

The simplified code is below:

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)

How do I deal with databases having the same table structure? I'm guessing it has something to do with the MetaData instance, but the SQLAlchemy documentation gets a little confusing when talking about the difference in the class declarative and classical usage..

Answer 1

Since in reality the tables had different structures, the solution was to simply create a separate declarative base. If the tables indeed had the same structure, I would have only needed one class for both tables.

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

You can use one db instance with two Model to bypass this problem.

And this can also be used to implement a master/slave use case in Flask-SQLAlchemy.

Just like this:

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'