python - Flask SQLAlchemy - session.add 违反主键约束

问题描述

我有一些带有整数主键的模型应该自动递增

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'),
    )

我有一条路线可以将多个学生注册到一个班级：

为每个学生插入一条记录Enrolment
同时插入付款负债和出勤记录

@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')

当我从前端 POST 到enrol端点时，我收到主键约束错误 - 这是意外的，因为所有模型都有一个自动递增的整数 PK。

同样奇怪的是，当我在前端继续单击注册时，每个请求都会导致不同的错误。您可以在下面看到第一次尝试使用id = 90和在第二次尝试中创建出勤，id = 91。我的数据库目前拥有 280 条出勤记录，所以我想如果我继续点击，它最终会“工作”。

最初，我在创建注册记录时遇到错误，但当我不断单击时，ID 会递增直到它有效，但后来我在付款和现在出勤时遇到了错误。

# 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}]

查看 SQL 插入语句，它应该可以工作，因为它没有在其插入语句中指定出勤.id，因此它应该是自动生成的。有没有问题RETURNING attendance.id？还是完全是别的东西？

标签： pythonsqlalchemyflask-sqlalchemy

python - Flask SQLAlchemy - session.add 违反主键约束

问题描述

解决方案

推荐阅读