Flask - (sqlite3.IntegrityError) NOT NULL constraint failed even after migration

Question

I have created a one-to-many database relationship between the following:

models.py

class User(db.Model, UserMixin):
    __tablename__ = "user"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)

    histories = db.relationship('History', backref='admin', lazy=True)

    def save(self):
        db.session.add(self)
        db.session.commit()

    def __repr__(self):
        return f"User('{self.id}', '{self.username}', '{self.email})"


class History(db.Model):
    __tablename__ = "history"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    offender_ip = db.Column(db.String(100), nullable=False)
    offender_mac = db.Column(db.String(100), nullable=False)
    action = db.Column(db.String(100), nullable=False)
    classification = db.Column(db.String(100), nullable=False)
    case_id = db.Column(db.String(100), nullable=False)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def save(self):
        db.session.add(self)
        db.session.commit()

    def __repr__(self):
        return f"Post('{self.id}', {self.date_posted}', '{self.offender_ip}', '{self.offender_mac}', '{self.action}'," \
            f" '{self.classification}', '{self.case_id}')"

I get the error when creating an instance of 'History' here:

@app.route("/block/add/result", methods=['GET', "POST"])
def block_submit_add():
    print(current_user)
    get_user = "user"
    get_password = "password"

    get_mac = request.form["mac"]
    get_offender_ip = request.form["off_ip"]
    get_classification = request.form["classification"]
    get_case_id = request.form["case_id"]
    now = datetime.now()

    if current_user.is_authenticated:
        history = History(date_posted=now, offender_ip=get_offender_ip,
                          offender_mac=get_mac, action="BLOCKED", classification=get_classification, case_id=get_case_id)
        db.session.add(history)
        db.session.commit()

        user = History.query.filter_by(offender_mac=get_mac).first()

        print(user.offender_ip)
        print(user.classification)
        print(user.get_case_id)

I also have a 'load_user' function that uses 'user_loader' from Flask-Login:

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

Here is the full error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: history.user_id
[SQL: INSERT INTO history (date_posted, offender_ip, offender_mac, action, classification, case_id, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)]
[parameters: ('2019-08-01 23:35:01.351985', '10.0.0.0', '00:00:00:00:00:00', 'BLOCKED', 'Test', '123', None)]

Is there a reason why 'History.user_id' is None? I have also tried to do a database migration/upgrade with Alembic and still receive the error.

Answer 1

You never set user_id when you create the new History object:

history = History(date_posted=now, offender_ip=get_offender_ip,
                  offender_mac=get_mac, action="BLOCKED", classification=get_classification, case_id=get_case_id)

It seems like offender_mac is what you use to identify a user by, if that is true I don't get why you also need a user_id.