I have a parent child relationship which I construct from a data feed. At the time of constructing the object graph I don't have access to the primary keys of the entities, so I build up the object graph by using the relationship attributes. My understanding was that I could perform a session.merge to get the new state of the whole object graph into the database, but when I try do this I get an exception.
Sample code that reproduces the problem I encounter: from sqlalchemy import Column, Integer, ForeignKey, create_engine, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import scoped_session, sessionmaker, relationship Base = declarative_base() PARENT_PK = 5 CHILD_1_PK = 6 CHILD_2_PK = 7 class Parent(Base): __tablename__ = 'parent' pk = Column(Integer, primary_key=True) data = Column(String) children = relationship( 'Child', back_populates='parent' ) class Child(Base): __tablename__ = 'child' pk = Column(Integer, primary_key=True) parent_pk = Column(Integer, ForeignKey('parent.pk'), nullable=False) data = Column(String) parent = relationship( 'Parent', back_populates='children' ) engine = create_engine('sqlite:///temp.db') session_factory = sessionmaker(bind=engine, autoflush=False) Session = scoped_session(session_factory) session = Session() Base.metadata.drop_all(engine) Base.metadata.create_all(engine) # Put some data in the database from some previous feed update parent = Parent(pk=PARENT_PK, data='First') child_1 = Child(pk=CHILD_1_PK, data='First child') child_2 = Child(pk=CHILD_2_PK) parent.children = [child_1, child_2] session.add(parent) session.commit() # New data in the new feed update new_parent = Parent(pk=PARENT_PK, data='Second') new_child_1 = Child(pk=CHILD_1_PK, data='Second child') new_parent.children = [new_child_1] session.merge(new_parent) session.commit() Exception: sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: child.parent_pk [SQL: UPDATE child SET parent_pk=? WHERE child.pk = ?] [parameters: (None, 7)] (Background on this error at: http://sqlalche.me/e/gkpj) Manually setting new_child_1.parent_pk before the merge doesn't do anything as the relationship takes precedence. To avoid the exception I need to do something like: new_child_1.parent_pk = PARENT_PK del new_parent.children del new_child_1.parent Is there an easier way to use session.merge for a graph - or a more standard method? Or do I always have to do some 'post processing' to strip out the relationships before using it? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.