sqlite3.IntegrityError: NOT NULL constraint failed: user_product.user_id

Viewed 780

I'm creating a web app with Flask and Flask-SQLAlchemy. I'm trying to model a many to many relationship between Users and Products with an association table called UserProduct which I've mapped to a class.

This is the error that I'm getting when I try to post data to my flask app:

sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider
using a session.no_autoflush block if this flush is occurring prematurely) 
(sqlite3.IntegrityError) NOT NULL constraint failed: 
user_product.user_id [SQL: INSERT INTO user_product (product_id, price_cutoff) 
VALUES (?, ?)] [parameters: (2, 15.0)]

models.py:

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


class UserProduct(db.Model):
    user_id = db.Column(db.ForeignKey('user.id'), primary_key=True)
    product_id = db.Column(db.ForeignKey('product.id'), primary_key=True)
    price_cutoff = db.Column(db.Integer, nullable=False)
    user = db.relationship('User', backref='products')
    product = db.relationship('Product', backref='users')


class User(db.Model, UserMixin):    
    id = db.Column(db.Integer, primary_key=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)

    def __repr__(self):
        return self.username


class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    price = db.Column(db.Float, nullable=False)
    url = db.Column(db.String(500), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, server_default='unavailable.png')

routes.py:

@app.route('/home', methods=['GET' 'POST'])
def index():
    form = ProductInfoForm()
    if form.validate_on_submit():
        product = Product.query.filter_by(url=form.url.data).first()
        if not product: 
            product = Product(name='TestName', price=10.2, url=form.url.data)
            db.session.add(product)
        a = UserProduct(price_cutoff=float(form.price_cutoff.data))
        a.product = product
        current_user.products.append(a)
        db.session.add(a)
        db.session.commit()
    return render_template('index.html', form=form)

However, everything goes as expected and my UserProduct table does get populated when I test with some dummy data in the command line:

>>> url = 'https://www.example.com/'
>>> product = Product.query.filter_by(url=url).first()
>>> if not product:
...     product = Product(name='TestName', price=10.2, url=url)
...     db.session.add(product)
... 
>>> a = UserProduct(price_cutoff=15)
>>> a.product = product
>>> u = User(username='testuser', email='testemail', password='testpassword')  
>>> u.products.append(a)
>>> db.session.add(a)
>>> db.session.commit()

I can't figure out what I'm doing wrong. The only difference that I see between my routes.py code and my command line code is that instead of using current_user (which I've imported from the Flask-login module), I manually created a dummy User object. However, I've double checked in the flask debugger and I'm pretty sure current_user exists and is working as expected.

Any help is greatly appreciated!

1 Answers

The type of price_cutoff you put in was Float, and it expects Int

a = UserProduct(price_cutoff=float(form.price_cutoff.data))
Related