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!