python - SQLAlchemy update objects tree

Question

I'm trying to build a tree of objects. The class looks like this:

class Node(BaseModel, db.Model):
    '''Node model'''
    __tablename__ = 'network_nodes'
    id = Column(String(10), primary_key=True)
    parent_id = Column(String(10), ForeignKey('network_nodes.id'))
    parent = relationship("Node", foreign_keys=[parent_id], lazy='dynamic')
    left_id = Column(String(10), ForeignKey('network_nodes.id'))
    left = relationship("Node", foreign_keys=[parent_id], lazy='dynamic')
    right_id = Column(String(10), ForeignKey('network_nodes.id'))
    right = relationship("Node", foreign_keys=[parent_id], lazy='dynamic')

Whenever new leaf is added this code is executed:

node.left_id = element_id
left = Node(id=element_id, parent_id=node.id)
db.session.add(left)

And at some point I commit all changes with db.session.commit(). At the commit() point I see dirty and new objects in session. But commit fails with error:

(MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (db.network_nodes, CONSTRAINT network_nodes_ibfk_2 FOREIGN KEY (left_id) REFERENCES network_nodes (id))') [SQL: UPDATE network_nodes SET left_id=%s, right_id=%s WHERE network_nodes.id = %s] [parameters: ('S5837124', 'S5839010', 'S5832131')]

I assume dirty objects are committed before new ones. Therefore left_id refers to not yet existing record.

How can I make new object committed before dirty ones?

标签： pythonsqlalchemymysql-python

Answer 1

How can I make new object committed before dirty ones?

You could add all the objects, flush the new ones, and then commit the whole batch:

    left = Node(id=element_id, parent_id=node.id)
    session.add(left)
    node.left_id = element_id
    
    session.flush(session.new)
    session.commit()