Obtaining IDs of intermediary objects before commit in SQLAlchemy ORM

Imagine you have 2 Objects to be stored in database namely Customer and Address

class Customer(Base):
    __tablename__ = "customer"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String)
    billing_address_id = mapped_column(Integer, ForeignKey("address.id"))
    shipping_address_id = mapped_column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address",foreign_keys=[billing_address_id])
    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = "address"
    id = mapped_column(Integer, primary_key=True)
    street = mapped_column(String)
    city = mapped_column(String)
    state = mapped_column(String)
    zip = mapped_column(String)

Now while inserting a records, you insert the Address first and then need to use the address ID for entry in Customer Table.

As the id column of Address table is autogenerated. You wont be able to get it till you commit the session.

But there is an easier way to get it done using flush() and refresh() methods. Here is how to do it

# Create Address Object
address_obj = Address(
        street = "1st Cross",
        city = "Bengaluru",
        state="KA",
        zip = "570186"
    )
# Add it to Db Session
db_session.add(address_obj)

# Perfrom Flush Operation
db_session.flush()

# Refresh the addres_obj. Then it will have new value for address_obj.id
db_session.refresh(address_obj)

# Now use the address_obj.id to Customer entry
customer_obj = Customer(
        name="John Doe",
        billing_address_id = address_obj.id,
        shipping_address_id = address_obj.id
    )

# Add Customer Object to Session
db_session.add(customer_obj)

# Finally commit the session
db_session.commit()