Flask SQLAlchemy session.add违反主键约束

Question

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

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

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

另一个奇怪的是，当我在前端不断单击enrol时，每个请求都会导致不同的错误。下面您可以看到，第一次尝试使用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 insert语句，如果它没有在insert语句中指定attention.id，那么它应该可以工作，因此应该自动生成它。拥有RETURNING attendance.id是否存在问题？还是完全是另外一回事

Answer 1

不要紧，这是一个Postgres问题，原因是在没有先清除原始数据库的情况下尝试还原数据库，然后决定不费事完成还原并继续处理数据库，没有意识到还原使数据库处于半完成状态

这导致主键序列与实际表不同步（即主键序列通过转储更新，但表数据本身不同步）

有关如何修复此问题的更多信息： postgresql duplicate key violates unique constraint