Menu
I've had a ton of trouble properly implementing many to many relationships in flask-sqlalchemy. It started with duplicate tags and now, many days later is a mapping problem. Fair warning, my code was much prettier before these issues crept up. I also added a addproduct.py file to speed up testing. So here it is.
models.py
1 from app import app, db
2
3 product_tags = db.Table('association',
4 db.Column('product_id', db.Integer, db.ForeignKey('product.id')),
5 db.Column('tag_name', db.Integer, db.ForeignKey('tag.name'))
6 )
7
8 class Product(db.Model):
9 id = db.Column(db.Integer, primary_key=True)
10 title = db.Column(db.String(128))
11 description = db.Column(db.Text)
12 image = db.Column(db.String(64))
13 link = db.Column(db.String(256))
14 price = db.Column(db.Float())
15 timestamp = db.Column(db.DateTime)
16 expiration = db.Column(db.String(6))
17 tags = db.relationship('Tag', secondary=product_tags,
18 backref=db.backref('product', lazy='dynamic'))
19
20 def __init__(self, title, description, image, link, price, timestamp, expiration, tags):
21 self.title = title
22 self.description = description
23 self.image = image
24 self.link = link
25 self.price = price
26 self.timestamp = timestamp
27 self.expiration = expiration
28 self.tags = tags
29 print self.title
30
31 def __repr__(self):
32 return '<Title %r, Description %r, Image %r, Link %r, Price %r, Timestamp %r, Expires %r, Tags %r>' % (self.title, self.description, self.image, sel
33
34 class Tag(db.Model):
35 name = db.Column(db.String(32), primary_key=True)
36
37 def __init__(self, name):
38 self.name = name
39
40 def __repr__(self):
41 return '<Tag %r>' % self.name
addproduct.py
1 from app import db
2 from app.models import Product, Tag, product_tags
3 from datetime import datetime
4
5 imagefolder = 'static/img/'
6
7 title = 'product'
8 description = 'description'
9 image = 'image.jpg'
10 link = 'http://link.com'
11 price = 2000.00
12 expiration = ''
13 tags = ['tag1','tag2']
14
15 newtags = []
16
17 def create_product(title, description, image, link, price, expiration, tags):
18 image = imagefolder + image
19 tag_assoc = []
20 for tag in tags:
21 tagcheck = Tag.query.filter_by(name=tag).first()
22 if tagcheck == None:
23 tag_assoc.append(Tag(tag))
24 else:
25 newtags.append(tag)
26
27 product = Product(title, description, image, link, price, datetime.utcnow(), expiration, tag_assoc)
28 create_assoc(newtags)
29 return product
30
31 def create_assoc(newtags):
32 title_search = Product.query.filter_by(title=title).first()
33 for tag in newtags:
34 assoc = product_tags.insert().values(product_id=title_search.id, tag_name=tag)
35 db.session.add(assoc)
36 db.session.commit()
37
38 if __name__ == '__main__':
39 product = create_product(title, description, image, link, price, expiration, tags)
40 db.session.add(product)
41 db.session.commit()
42 create_assoc(newtags)
The error message I get is:
11:11 ~/shop $ python addproduct.py
product
Traceback (most recent call last):
File "addproduct.py", line 39, in <module>
product = create_product(title, description, image, link, price, expiration, tags)
File "addproduct.py", line 28, in create_product
create_assoc(newtags)
File "addproduct.py", line 35, in create_assoc
db.session.add(assoc)
File "/home/username/.local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 114, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/home/username/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1358, in add
raise exc.UnmappedInstanceError(instance)
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.sql.expression.Insert' is not mapped
This is my first webapp not directly from a tutorial, and I am totally lost. Please help!
560
The first mistake I can see if in your models-- your association correctly has two foreign keys, but the foreign key for the tag is an Integer but your primary key within your Tag class is a String-- they should match. Apart from that the models look good.
You also should be able to simplify your create_product function:
def create_product(title, description, image, link, price, expiration, tags):
image = imagefolder + image
tag_list = []
for tag in tags:
tagcheck = Tag.query.filter_by(name=tag).first()
if tagcheck is None:
tag_list.append(Tag(tag))
else:
tag_list.append(tagcheck)
product = Product(title, description, image, link, price, datetime.utcnow(), expiration, tag_list)
return product
This can easily be moved inside your __init___ constructor. In your example you deal directly with the association table, but you don't have to at all-- just trust the ORM to do the correct thing, that's the beauty of SQLAlchemy.
Here's an example of what your product model could look like:
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(128))
description = db.Column(db.Text)
image = db.Column(db.String(64))
link = db.Column(db.String(256))
price = db.Column(db.Float())
timestamp = db.Column(db.DateTime)
expiration = db.Column(db.String(6))
tags = db.relationship('Tag', secondary=product_tags,
backref=db.backref('products', lazy='dynamic'))
def __init__(self, title, description, image, link, price, timestamp, expiration, tags):
self.title = title
self.description = description
self.image = image
self.link = link
self.price = price
self.timestamp = timestamp
self.expiration = expiration
for tag in tags:
tagcheck = Tag.query.filter_by(name=tag).first()
if tagcheck is None:
self.tags.append(Tag(tag))
else:
self.tags.append(tagcheck)
def __repr__(self):
return '<{}, {}>'.format(self.title, ":".join([x.name for x in self.tags]))
And to test it out, first lets add a couple of tags to the system:
ta = Tag('cat')
tb = Tag('dog')
db.session.add_all([ta, tb])
db.session.commit()
>>> Tag.query.all()
[<Tag u'cat'>, <Tag u'dog'>]
Now lets add a product that uses those tags, plus a new tag.
p = Product(
'title',
'description',
'image',
'link',
0.0,
datetime.now(),
'expiry',
['dog','cat','horse']
)
db.session.add(p)
db.session.commit()
When we create that product, the constructor takes each of those three string tags, and says "Hey, does a tag of this name already exist?" if so, it uses it, and if not, it creates a new tag by that name. SQLAlchemy is clever enough to know to add the new Tag into the session, and to commit it when the product is committed.
>>> Tag.query.all()
[<Tag u'cat'>, <Tag u'dog'>, <Tag u'horse'>]
Now lets find all products with the dog tag (assuming more products have been added).
>>> tag = Tag.query.get('dog')
>>> products = tag.products
>>> [x.title for x in products]
['title','other','examples']
Again, at no point am I touching the association table at all, there's no need. SQLAlchemy is saving us effort.
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
