CODEWITHSUNDEEP
pythonsqlalchemy
scrineym
scrineym

Reputation: 759

sqlalchemy bind table to multiple engines

I have two different mysql databases with the same schema. In short I want to use sqlalchemy to read from one database and insert the data into the other one.

Here is my code so far

   Base = declarative_base()  
   class Test(Base):
       __tablename__ = 'test'
       ID = Column(Integer,primary_key=True)
       Name = Column(String,nullable=True)
       Updated = Column(Integer)

   sourceEngine  = create_engine(sourceDBString)
   destEngine = create_engine(destDBString)
   Base.metadata.bind = sourceEngine

   SourceSession = sessionmaker()
   SourceSession.bind = sourceEngine
   DestSession = sessionmaker(destEngine)

   source_session = SourceSession()
   destSession = DestSession()
   notupdated = source_session.query(Test).filter_by(Updated=0)

   for row in notupdated:
      row.Updated=1
   destSession.add_all(notupdated)
   destSession.commit() 
   source_session.commit()
   destSession.close()
   source_session.close()

The error I'm getting is:

sqlalchemy.exc.InvalidRequestError: Object '<Test at 0x7f7e1a83ce50>' is already attached to session '1' (this is '2')

It seems to be that I can only bind the object Test to one engine at a time, is there any way I can set both engines to use it?

Upvotes: 0

Views: 1301

Answers (1)

dave
dave

Reputation: 905

You have to call make_transient to transfer an object from one session to another. You may also want to clear the obj.id column by setting it to None.

This SO question should help you further. How to use make_transient() to duplicate an SQLAlchemy mapped object?

Upvotes: 1

Related Questions