migrating a child object from one parent to another

[Jonathan Vanasco]

I am having an issue with migrating an instance child relationship from one object to another.

I have the following two classes:

    class AcmeAccount(Base):

        __tablename__ = "acme_account"

        id = sa.Column(sa.Integer, primary_key=True)

        account_url = sa.Column(sa.Unicode(255), nullable=True, unique=True)

        acme_account_key = sa_orm_relationship(

            "AcmeAccountKey",

            primaryjoin="and_(AcmeAccount.id==AcmeAccountKey.acme_account_id, AcmeAccountKey.is_active.is_(True))",

            uselist=False,

        )

        acme_account_keys_all = sa_orm_relationship(

            "AcmeAccountKey",

            primaryjoin="AcmeAccount.id==AcmeAccountKey.acme_account_id",

            uselist=True,

        )

    class AcmeAccountKey(Base):

        __tablename__ = "acme_account_key"

        id = sa.Column(sa.Integer, primary_key=True)

        acme_account_id = sa.Column(

            sa.Integer, sa.ForeignKey("acme_account.id"), nullable=False

        )

        is_active = sa.Column(sa.Boolean, nullable=False, default=True)

        acme_account = sa_orm_relationship(

            "AcmeAccount",

            primaryjoin="AcmeAccountKey.acme_account_id==AcmeAccount.id",

            uselist=False,

        )

Occasionally a "Duplicate" AcmeAccount is detected when syncing against the upsteam ACME server.

I need to merge the two accounts into the earlier account...

    accountOriginal = dbSession.query(AcmeAccount)...

    accountDuplicate = dbSession.query(AcmeAccount)...

    with dbSession.no_autoflush:

        # update unique url; no problem       

        accountOriginal.account_url = accountDuplicate.account_url

        accountDuplicate.account_url = None

        dbSession.flush()

    

        # unset active key on original; no problem    

        if accountOriginal.acme_account_key:

            accountOriginal.acme_account_key.is_active = None

            dbSession.flush()

        

        # migrate duplicate key to original; many problems

        accountkeyMigrate = accountDuplicate.acme_account_key

        # PROBLEMS HERE, one (of many) attempts was something like

        accountkeyMigrate.acme_account_id == accountOriginal.id

        accountOriginal.acme_account_key == accountkeyMigrate

        

        # eventually

        dbSession.delete(accountDuplicate)

        dbSession.flush()

I have tried addressing the relationship, the columns, both, explicit calls to flush() and a few settings on the relationship itself

I keep running into problems where the ORM will set the `acme_account_id` of one of the AcmeAccountKeys to None, which violates the not-null constraint and raises an exception.

I know that I am overthinking this situation, i've just never had to migrate an object in the ORM like this before.

Can anyone point me in the right direction?

    

[Jonathan Vanasco]

I totally missed the `AcmeAccountKey.is_active.is_(True)` on the relationship. I set it as viewonly and crisis solved.