CODEWITHSUNDEEP
sqlalchemy
Guillaume Chérel
Guillaume Chérel

Reputation: 1518

Inserting a child object with a parent when the parent already exists with SQLAlchemy

I would like to insert an child object (see class definitions below) which has a relationship with a parent object which may or may not already exists in the database, and then get the generated primary key for the child. I tried using both Session.add and Session.merge but I'm running into problems with both.

  1. Using Session.add does not work when the parent object already exists in the table. For example, the following fails:
# Create a parent with id 1
parent = Parent(id = 1)
with Session(engine) as session:
    session.add(parent)
    session.commit()

...

# Later, add a child whose parent is the one with id 1. 
# I know the parent id and don't need to fetch it from
# the database, thus I'm directly creating the parent object. 
parent = Parent(id = 1)
child = Child(parent = parent)
with Session(engine) as session:
    session.add(child)
    session.commit()
    print("child.id = " + str(child.id))

It produces:

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "parent_pkey"
DETAIL:  Key (id)=(1) already exists.

SQLAlchemy is trying to add the parent again but complains that the primary key "1" already exists.

  1. Using Session.merge works but I cannot get the generated id for the new child:
# The Parent with id = 1 now exists in the parent table

# Add the child with the parent using merge
parent = Parent(id = 1)
child = Child(parent = parent)
with Session(engine) as session:
    session.merge(child)
    session.commit()
    print("child.id = " + str(child.id))

This shows child.id = None.

I'm probably not approaching the right way and I'd very much appreciate some directions.

Here are the example class definitions:

    class Parent(Base):
        __tablename__ = 'parent'

        id = Column(Integer, primary_key = True)

        children = relationship("Child", back_populates = "parent")

    class Child(Base):
        __tablename__ = 'children'

        id = Column(Integer, primary_key = True)
        parent_id = Column(Integer, ForeignKey("parent.id"), nullable = False)

        parent = relationship("Parent", back_populates = "children")

Upvotes: 2

Views: 1780

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123664

Instead of arbitrarily creating parent = Parent(id = 1) you should check if it already exists:

# retrieve parent from database …
parent1 = session.get(Parent, 1)
if not parent1:
    # … and create if not found
    session.add(parent1 := Parent(id=1, name="Homer"))

Upvotes: 1

Related Questions