Sqlalchemy multiple databases with the same table names do not work

Question

I have two databases that I work with Python using SQLAlchemy, the names of shared database tables, and therefore I get an error message when I run the code.

Error message:

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 following is simplified code:

 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 can I work with databases that have the same table structure? I suppose this has something to do with the MetaData instance, but the SQLAlchemy documentation is a bit confusing when it comes to the difference in declarative and classic class usage.

Answer 1

Since the tables actually had different structures, the solution was to simply create a separate declarative database. If the tables really had the same structure, I would need only 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 instance of db with two Models to get around this problem.

And it can also be used to implement an example using master / slave in Flask-SQLAlchemy.

Similar:

 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'