CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Matt Davis
Matt Davis

Reputation: 482

db.Model vs db.Table in Flask-SQLAlchemy

The Flask-SQLAlchemy docs say that many-to-many lookup tables should not subclass db.Model but instead be written as db.Tables. From the docs:

If you want to use many-to-many relationships you will need to define a helper table that is used for the relationship. For this helper table it is strongly recommended to not use a model but an actual table

Why? What are the downsides to making everything a model? I think it looks cleaner to have a unified way of declaring tables in the database. Also, it's possible that sometime later a developer will want to access those mapping records directly, rather than through a relationship, which would require a model.

Upvotes: 26

Views: 16011

Answers (1)

Grey Li
Grey Li

Reputation: 12822

The db.Table is more simple.

When you define a many-to-many relationship through db.Table, SQLAlchemy would take over and do most of the job for you.

So, assuming that we have a relationship with posts and tags with the following Table and Model definitions:

Table:

tagging = db.Table('tagging',
    db.Column('post_id', db.Integer, db.ForeignKey('post.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'))
)

Model:

class Tagging(db.Model):
    tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'),
                              primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'),
                               primary_key=True)

Insert and delete

As the description in docs:

A behavior which is unique to the secondary argument to relationship() is that the Table which is specified here is automatically subject to INSERT and DELETE statements, as objects are added or removed from the collection. There is no need to delete from this table manually. The act of removing a record from the collection will have the effect of the row being deleted on flush.

With db.Table, you can do something like this:

>>> post.tags.append(tag_foo)
>>> db.session.commit()

You needn't add it into session, then you can delete a relationship with remove():

>>> post.tags.remove(tag_foo)
>>> db.session.commit()

However, if you use db.Model, you have to do something like this (Tagging is the Model class):

>>> tagging = Tagging(post=post_foo, tag=tag_bar)
>>> db.session.add(tagging)
>>> db.session.commit()

then delete it like this:

>>> tagging = post.tags.filter_by(post_id=post.id).first()
>>> db.session.delete(tagging)
>>> db.session.commit()

Query

With db.Table:

>>> post.tags.all()
>>> [<Tag 'foo'>, ...]

Then db.Model:

>>> post.tags.all()  # You only get Tagging item.
>>> [<Tagging 'post_foo -- tag_bar'>, ...]
>>> for tagging in post.tags:
>>>     print tagging.tag  # <Tag 'foo'>

In a word, if you don't need to store extra data about the relationship, just use db.Table, it will save your time.

Upvotes: 31

Related Questions