SQLAlchemy: Why does appending to this many-to-one relationship fail?
<p>I have two classes, a <code>Parent</code> and <code>Child</code>. A parent can have multiple children. In my program, I append children to parents as needed. This consistently succeeds for the first child but fails for the second. Here are my relationships</p>

<pre><code>class Parent(db.Model):

    __tablename__ = 'parent'
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship(
        'Child',
        backref=db.backref('parent', order_by=id)
    )

class Child(db.Model):

    __tablename__ = 'child'
    id = db.Column(db.Integer, primary_key=True)
    parent_fk = db.Column(
        db.Integer,
        db.ForeignKey('parent.id')
    )
</code></pre>

<p>And then the code to add one child at a time:</p>

<pre><code>print(parent.children)
parent.children.append(Child())
print(parent.children)
database.update_object(parent)  # Uses a scoped session to merge and commit.
</code></pre>

<p>Here is my update code:</p>

<pre><code>@contextmanager
def session_scope():
    try:
        yield db.session
        db.session.commit()
    except Exception as e:
        print('Rolling back database')
        print(e)
        db.session.rollback()

def update_object(obj):
    with session_scope() as session:
        session.merge(obj)
</code></pre>

<p>When I log the results from the program, I get this:</p>

<p>Adding first child:</p>

<pre><code>[]
[&lt;Child None&gt;]
</code></pre>

<p>Adding second child:</p>

<pre><code>[&lt;Child 219L&gt;]
[&lt;Child 219L&gt;, &lt;Child None&gt;]
Rolling back database
(OperationalError) (1048, "Column 'parent_fk' cannot be null") 'UPDATE child SET parent_fk=%s WHERE child.id = %s' (None, 218L)
</code></pre>

<p>What am I missing?</p>

<p>FWIW, a consistent pattern is that the second <code>Child</code> instance has an <code>id</code> that is 1 lower than the first.</p>