SQLAlchemy - How to insert a Joined Table Inherited class instance when the parent is already persisted

Question

I declared two clases using SQLAlchemy Joined Table Inheritance. Additionally I am using Flask-SQLAlchemy and Flask.

class Parent(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    type = db.Column(db.String(50))

    __mapper_args__ = {
        'polymorphic_identity': 'parent',
        'polymorphic_on': type
    }


class Child(Parent):

    id = db.Column(db.Integer, db.ForeignKey('parent.id'), primary_key=True)
    brand_id = db.Column(db.Integer, nullable=False)

    __mapper_args__ = {
        'polymorphic_identity': 'child',
    }

The relationship between parent and child tables are one to one, with optional occurrence in the child side.

When I create a new instance of Child, add it to the session and commit it to the database, everything works as expected. SQLAlchemy sends an INSERT statement to the parent table and then another INSERT to the child table, all by itself, avoiding any Foreign Key constraint violation.

In some point of the application, I want to add a new row in the child table referencing to a row that already exist in the parent table.

When I create a new Child instance and assign it manually an existing id to the FK column and sent it to the database. I get an IntegrityError (duplicate unique constraint violation) because SQLAlchemy tries to create a new parent entry in the database with the id passed to the child instance.

So, the real question is:

How can I tell to SQLAlchemy, that the parent entry already exist in the database and I just want to create the child entry?

Answer 1

You can get around this by using SQLALchemy Core to do your insertion directly to the child, instead of adding it using the ORM.

# where obj is some instance of your parent and session some sqlalchemy session
obj.type = 'child'
session.execute(
  sa.insert(Child.__table__).values(id=obj.id, brand_id=...)
)

Because this is outside the orm, you may need to now expunge the original parent object from the session so that the SQLAlchemy ORM session is up to date with what you just did.