8

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..

Improve this question
2
  • I removed a random ) in your source code I assume that was a typo if it's not it can be reverted. Commented Nov 25, 2011 at 4:01
  • 1
    I have the answer: Since in reality the tables had slightly different structures, the solution was to simply create a separate declarative base. If the tables indeed had the same structure, I would not have needed to create a separate class. Base = declarative_base() Base2 = declarative_base() Commented Nov 25, 2011 at 5:24

2 Answers 2

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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'
Improve this answer

1 Comment

what are the cases where one may want to use the single db instance pattern instead of two different instances, as shown by @dangel?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.