Copying data from one sqlalchemy session to another

Question

I have a sqlalchemy schema containing three tables, (A, B, and C) related via one-to-many Foreign Key relationships (between A->B) and (B->C) with SQLite as a backend. I create separate database files to store data, each of which use the exact same sqlalchemy Models and run identical code to put data into them.

I want to be able to copy data from all these individual databases and put them into a single new database file, while preserving the Foreign Key relationships. I tried the following code to copy data from one file to a new file:

import sqlalchemy
from sqlalchemy.ext import declarative
from sqlalchemy import Column, String, Integer
from sqlalchemy import orm, engine

Base = declarative.declarative_base()
Session = orm.session_maker()

class A(Base):
    __tablename__ = 'A'

    a_id = Column(Ingeter, primary_key=True)
    adata = Column(String)
    b = orm.relationship('B', back_populates='a', cascade='all, delete-orphan', passive_deletes=True)


class B(Base):
    __tablename__ = 'B'

    b_id = Column(Ingeter, primary_key=True)
    a_id = Column(Integer, sqlalchemy.ForeignKey('A.a_id', ondelete='SET NULL') 
    bdata = Column(String)
    a = orm.relationship('A', back_populates='b')
    c = orm.relationship('C', back_populates='b', cascade='all, delete-orphan', passive_deletes=True)

class C(Base):
    __tablename__ = 'C'

    c_id = Column(Ingeter, primary_key=True)
    b_id = Column(Integer, sqlalchemy.ForeignKey('B.b_id', ondelete='SET NULL') 
    cdata = Column(String)
    b = orm.relationship('B', back_populates='c')


file_new = 'file_new.db'
resource_new = 'sqlite:////%s' % file_new.lstrip('/')
engine_new = sqlalchemy.create_engine(resource_new, echo=False)
session_new = Session(bind=engine_new)

file_old = 'file_old.db'
resource_old = 'sqlite:////%s' % file_old.lstrip('/')
engine_old = sqlalchemy.create_engine(resource_old, echo=False)
session_old = Session(bind=engine_old)

for arow in session_old.query(A):
    session_new.add(arow)  # I am assuming that this will somehow know to copy all the child rows from the tables B and C due to the Foreign Key.

When run, I get the error, "Object '' is already attached to session '2' (this is '1')". Any pointers on how to do this using sqlalchemy and sessions? I also want to preserve the Foreign Key relationships within each database.

The use case is where data is first generated locally in non-networked machines and aggregated into a central db on the cloud. While the data will get generated in SQLite, the merge might happen in MySQL or Postgres, although here everything is happening in SQLite for simplicity.

Answer 1

First, the reason you get that error is because the instance arow is still tracked by session_old, so session_new will refuse to deal with it. You can detach it from session_old:

session_old.expunge(arow)

Which will allow you do add arow to session_new without issue, but you'll notice that nothing gets inserted into file_new. This is because SQLAlchemy knows that arow is persistent (meaning there's a row in the db corresponding to this object), and when you detach it and add it to session_new, SQLAlchemy still thinks it's persistent, so it does not get inserted again.

This is where Session.merge comes in. One caveat is that it won't merge unloaded relationships, so you'll need to eager load all the relationships you want to merge:

query = session_old.query(A).options(orm.subqueryload(A.b),
                                     orm.subqueryload(A.b, B.c))
for arow in query:
    session_new.merge(arow)