I have some models with an integer primary key that should auto increment
class Enrolment(db.Model):
__tablename__ = 'enrolment'
id = db.Column(db.Integer, primary_key=True)
class_id = db.Column(db.Integer, db.ForeignKey('class.id', ondelete='CASCADE'), nullable=False)
student_id = db.Column(db.Integer, db.ForeignKey('student.id'), nullable=False)
enrolment_date = db.Column(db.Date, default=date.today)
status = db.Column(db.Boolean, default=True)
payments = db.relationship("Payment", backref="enrolment", passive_deletes=True)
attendance = db.relationship("Attendance", backref="enrolment", passive_deletes=True)
__table_args__ = (
db.UniqueConstraint('class_id', 'student_id'),
)
class Attendance(db.Model):
__tablename__ = 'attendance'
id = db.Column(db.Integer, primary_key=True)
enrolment_id = db.Column(db.Integer, db.ForeignKey('enrolment.id', ondelete='CASCADE'), nullable=False)
lesson_number = db.Column(db.Integer, nullable=False)
attended = db.Column(db.Boolean, default=False)
lesson_date = db.Column(db.Date)
# status = db.Column(db.Boolean, default=True)
__table_args__ = (
db.UniqueConstraint('enrolment_id', 'lesson_number'),
)
And I have a route that enrols multiple students into a single class:
- For every student, insert a record into
Enrolment - Also insert payment liabilities and attendance records
@enrol_bp.route('/', methods=['POST'])
@jwt_required
def enrol():
c = Class.query.filter_by(id=request.json['class_id']).first()
if not c:
return jsonify(message = f'Class {c.id} does not exist'), 404
student_ids = request.json['student_ids']
for student_id in student_ids:
student = Student.query.filter_by(id=student_id).first()
if not student:
return jsonify(message = f'Student {student_id} does not exist'), 404
enrolment = Enrolment(
class_id=c.id,
student_id=student.id,
enrolment_date=datetime.strptime(request.json['enrolment_date'], '%Y-%m-%d')
)
db.session.add(enrolment)
lesson_count = 0
for term_index, term in enumerate(c.terms):
payment = Payment(
enrolment=enrolment,
number=term_index+1,
amount=term['lessons'] * term['cost_per_lesson'],
)
db.session.add(payment)
for lesson_index in range(0, term['lessons']):
attendance = Attendance(
enrolment=enrolment,
lesson_number=lesson_count+1
)
lesson_count += 1
db.session.add(attendance)
db.session.commit()
return jsonify(message = f'Students enrolled')
When I POST to the enrol endpoint from my frontend, I'm getting primary key constraint errors - which is unexpected since all the models have an autoincrementing integer PK.
What's also strange is that when I keep clicking enrol on my frontend, each request results in a different error. Below you can see that the first attempt tried to create an Attendance with id = 90 and in the second attempt, id = 91. My DB currently holds 280 Attendance records so I imagine that if I keep clicking, it'll eventually "work".
Initially, I was getting an error creating the Enrolment record but as I kept clicking, the ID incremented until it was valid but then I faced the error on Payment and now Attendance.
# Attempt 1
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "attendance_pkey"
DETAIL: Key (id)=(90) already exists.
[SQL: INSERT INTO attendance (enrolment_id, lesson_number, attended, lesson_date) VALUES (%(enrolment_id)s, %(lesson_number)s, %(attended)s, %(lesson_date)s) RETURNING attendance.id]
[parameters: {'enrolment_id': 49, 'lesson_number': 1, 'attended': False, 'lesson_date': None}]
# Attempt 2
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "attendance_pkey"
DETAIL: Key (id)=(91) already exists.
[SQL: INSERT INTO attendance (enrolment_id, lesson_number, attended, lesson_date) VALUES (%(enrolment_id)s, %(lesson_number)s, %(attended)s, %(lesson_date)s) RETURNING attendance.id]
[parameters: {'enrolment_id': 50, 'lesson_number': 1, 'attended': False, 'lesson_date': None}]
Looking at the SQL insert statement, it should work given that it's not specifying the attendance.id in its insert statement so it should be automatically generated. Is there an issue with having RETURNING attendance.id? Or is it something else completely?
