Following is a simplified demo code of the problem I've met. I try using sqlalchemy merge() to upsert data, and teacher's id is known and student's id is unknown. After fisrt successful merge, the second merge sets existing students' teacher id null. Would someone tell me the reason and how to fix it? Thanks in advance.
class Teacher(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
def __init__(self, id):
self.id = id
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
tid = db.Column(db.Integer, db.ForeignKey("teacher.id"), nullable=True)
teacher = db.relationship(
"Teacher",
uselist=False,
backref=db.backref("students"),
)
name = db.Column(db.Text)
def __init__(self, teacher, name):
self.teacher = teacher
self.name = name
def __repr__(self):
return f'id: {self.id}, name: {self.name}, teacher id: {self.tid}'
def show_students():
for s in Student.query.all():
print(s)
def init_db():
teacher = Teacher(1)
Student(teacher, 's1')
Student(teacher, 's2')
db.session.merge(teacher)
db.session.commit()
show_students()
def add_student():
teacher = Teacher(1)
Student(teacher, 's3')
db.session.merge(teacher)
db.session.commit()
show_students()
run init_db() to add two students
id: 1, name: s1, teacher id: 1
id: 2, name: s2, teacher id: 1
run add_student() to add another student, but existing students' teacher id becomes None
id: 1, name: s1, teacher id: None
id: 2, name: s2, teacher id: None
id: 3, name: s3, teacher id: 1