Lelsoos Lelsoos - 1 month ago 9
Python Question

Flask-SQLAlchemy: Update a One to Many Relationship

I have two Models:

class Project(db.Model):
__tablename__ = 'project'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
timestamp = db.Column(db.DateTime)
info = db.Column(db.Text(255))

post = db.relationship('Post', backref=db.backref('projects'))

class Post(db.Model):
__tablename__ = 'post'

id = db.Column(db.Integer, primary_key=True)
headline = db.Column(db.String(64))
content = db.Column(db.Text(255))
timestamp = db.Column(db.DateTime)

projectId = db.Column(db.Integer, db.ForeignKey('project.id'))


If I, e.g., create an instance of post and append it to a project via
projectInstance.append(post(headline="...", content="..."))


Now I want to update the project to which the post should belong to. Is there an easier way besides querying the desired project, append the post to it and remove it from the old project?

oldProject.remove(post)
newProject.append(post)

Answer

Do it the other way around: use the back reference in Post:

post.projects = newProject

This'll handle the required session management. Here's a minimal example:

In [2]: class A(Base):
   ...:     __tablename__ = 'a'
   ...:     id = Column(Integer, primary_key=True, autoincrement=True)
   ...:     bs = relationship('B', backref='a')
   ...:

In [3]: class B(Base):
   ...:     __tablename__ = 'b'
   ...:     id = Column(Integer, primary_key=True, autoincrement=True)
   ...:     a_id = Column(Integer, ForeignKey('a.id'))
   ...:

In [5]: a1 = A()

In [6]: a2 = A()

In [7]: b = B()

In [8]: a1.bs.append(b)

In [9]: a1.bs
Out[9]: [<__main__.B at 0x7f7322fb52b0>]

In [10]: b.a = a2

In [11]: a1.bs, a2.bs
Out[11]: ([], [<__main__.B at 0x7f7322fb52b0>])

Also just appending the post instance to the newProject.post collection handles removing automatically, as can be seen with our as and bs:

In [9]: a1.bs.append(b)

In [10]: a1.bs, a2.bs
Out[10]: ([<__main__.B at 0x7f26702565f8>], [])

In [11]: a2.bs.append(b)

In [12]: a1.bs, a2.bs
Out[12]: ([], [<__main__.B at 0x7f26702565f8>])