On Nov 24, 2011, at 11:29 PM, dangel wrote:
> 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.
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 sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
