multiple databases with same table names not working

[dangel]

I have two databases (MySQL and MSSQL) that I'm working with using
SQLAlchemy 0.7, 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:
#*********************************************
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)

#*********************************************

Any suggestions? I've looked through both the documentation and the
group posts and cannot find anything definitive. I'm thinking the
answer has something to do with the metadata, but I really don't know
where to go from here.

[Michael Bayer]

Are the table structures identical in both databases ? if so, then I would not double up the declarative classes in this way - I'd use one class to represent a particular table structure, which can then be used in either session. You're already using two different Session objects, so there is no need to specify different classes just to distinguish these.

OTOH, if the table structures do differ among the two databases, and you do in fact need to define each class twice, you just need to use two separate declarative bases so that they no longer share the same MetaData object.

>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[dangel]

> Are the table structures identical in both databases ?  if so, then I would not double up the declarative classes in this way - I'd use one class to represent a particular table structure, which can then be used in either session.   You're already using two different Session objects, so there is no need to specify different classes just to distinguish these.
>
> OTOH, if the table structures do differ among the two databases, and you do in fact need to define each class twice, you just need to use two separate declarative bases so that they no longer share the same MetaData object.
>

The table structures are slightly different. I created a separate
declarative base as you suggested and all is working correctly now.
Thank you for the quick response, this should get be going until the
next problem!