Unable to move an object from one database to another

Question

I am trying to move an object from one database to another. The mappings are the same, but the tables are different. This is a merge tool in which data from an old database needs to be imported into a new one. However, I think something fundamental is missing here in SQLAlchemy. What is it?

from sqlalchemy import Column, Float, String, Enum from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import orm from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker DeclarativeBase = declarative_base() class Datum (DeclarativeBase): __tablename__ = "xUnitTestData" Key = Column(String, primary_key=True) Value = Column(Float) def __init__ (self, k, v): self.Key = k self.Value = v src_engine = create_engine('sqlite:///:memory:', echo=False) dst_engine = create_engine('sqlite:///:memory:', echo=False) DeclarativeBase.metadata.create_all(src_engine) DeclarativeBase.metadata.create_all(dst_engine) SessionSRC = sessionmaker(bind=src_engine) SessionDST = sessionmaker(bind=dst_engine) item = Datum('eek', 666) session1 = SessionSRC() session1.add(item) session1.commit() session1.close() session2 = SessionDST() session2.add(item) session2.commit() print item in session2 # >>> True print session2.query(Datum).all() # >>> [] session2.close() 

Answer 1

I do not know what is happening under the hood, but in the ORM template, the object corresponds to a specific row in a specific table. If you try to add the same object to two different tables in two different databases, this does not seem like good practice, even if the table definition is exactly the same.

What would I do to solve this problem, just create a new object that is a copy of the original object and add it to the database:

 session1 = SessionSRC() session1.add(item) session1.commit() new_item = Datum(item.Key, item.Value) session2 = SessionDST() session2.add(new_item) session2.commit() print new_item in session2 # >>> True print session2.query(Datum).all() # >>> [<__main__.Datum object at 0x.......>] session2.close() session1.close() 

Note that session1 does not close immediately to read the original attributes of the object when creating a new object.