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.
