Persist one object from one database to another using sqlalchemy

Question

I have two databases (both Mysql) that have exactly the same tables, and I want to copy some data from one to another using Sqlalchemy.

I can copy simple objects following the answer given in this question:

Cannot move object from one database to another

The problem is when the object has dependencies from another table, and I want to copy the dependencies as well.

So to make it more clear, this is my model (the same for both databases but using a different bind_key that points to a different database):

db1 = SQLAlchemy()

Class Payment(db.Model):
    __tablename__ = 'payments'
    __bind_key__ = 'db1'

    id = db1.Column(db.Integer, primary_key=True)
    paymethod_id = db1.Column(db.Integer(), db1.ForeignKey(PaymentMethod.id))
    payment_method = db1.relationship(PaymentMethod)

What I would like to do is the following:

from models1 import Payment as Payment1
from models2 import Payment as Payment2

# query from one database
payment1 = db1.session.query(Payment1).first()

# create and add it to the other database
payment2 = Payment2(**payment1.__dict__.copy())
db2.session.add(payment)
db2.session.commit()

But in this case the foreign key fails because I don't have the PaymentMethod stored yet.

Is there a different approach to do that or I would have to do this procedure for every dependency of my object and be sure that I store the children beforehand?

Any help is appreciated :)

Answer 1

I came up with a solution that remaps the object to the right model and stores all its children. You call the method save_obj and pass the object you want to map. It will then retrieve a table with the same name but then from the model you want to remap the object to and it will recursively do the same for all its children. You have to define the right model in the method get_model.

To run this is necessary to disable autoflush to prevent committing before the object is correctly formed and it is also necessary to commit after calling the method. I'm using flask-sqlalchemy.

Hope this can help or give some insight to someone that faces a similar problem :)

def save_obj(obj, checked=[]):
    if obj in checked:
        # if the object was already converted, retrieve the right object
        model = get_model(obj.__mapper__.mapped_table.name)
        return get_obj(obj, model)

    checked.append(obj)
    children = []

    relations = obj.__mapper__.relationships.items()

    # get all the relationships of this model (foreign keys)
    for relation in relations:
        model = get_model(relation[1].table.name)
        if model:
            # remove the cascade option for this object, so the children are not stored automatically in the session
            relation[1]._cascade = CascadeOptions('')
            child = getattr(obj, relation[0])

            if not child:
                continue

            # if the child is a set of children
            if isinstance(child, list):
                new_children = []
                for ch in copy(child):
                    # convert the child
                    new_child = save_obj(ch, checked)
                    new_children.append(new_child)

                children.append((relation[0], new_children))
            else:
                new_child = save_obj(child, checked)
                children.append((relation[0], new_child))

    # get the model of the object passed
    model = get_model(obj.__mapper__.mapped_table.name)
    new_obj = get_obj(obj, model)

    # set all the children in this object
    for child in children:
        if child[1]:
            setattr(new_obj, child[0], child[1])

    checked.append(new_obj)

    session.add(new_obj)
    return new_obj


def get_model(table_name):
    # get the right model for this object
    for table in db.Model._decl_class_registry.values():
        if hasattr(table, '__tablename__') and table.__tablename__ == table_name:
            return table
    return None


def create_new_obj(obj, model):
    params = obj.__dict__.copy()
    params.pop('_sa_instance_state')
    return model(**params)


def get_obj(child, model):
    # check if the object is already stored in the db
    child_in_db = session.query(model).get(child.id)

    if child_in_db:
        return child_in_db

    # check if the object is already in the session
    for s in session.new:
        if type(s) == model and s.id == child.id:
            return s

    return create_new_obj(child, model)