Flask SQLAlchemy relationship associations and backref

Flask, Python, SQLAlchemy / By

Flask-SQLAlchemy relationship associations are useful to easily retrieve related records from a related Model.

In a Python Flask application I wanted to use a many-to-many relationship between Photos and Tags (eg many categories assigned to one or more photos).

To do this I had the following things set up in my application:

  • 3 database tables
    • Photos
    • Tags
    • Photo_Tags
  • 3 model.py sections
    • Photos
    • Tags
    • photo_tags
  • 1 views.py section �photo�
  • 1 template �photo.html�

The models looked like this:


class Photos(db.Model):
�.id = db.Column(db.Integer, primary_key=True)
�.filename = db.Column(db.String(100))
�.tags = db.relationship(�Tags�,  secondary=photo_tags, backref=�photos�)

class Tags(db.Model):
�. id = db.Column(db.Integer, primary_key=True)
�. tagname = db.Column(db.String(100))

photo_tags = db.Table(�photo_tags�,
�. db.Column(�tag_id�, db.Integer, db.ForeignKey(�tags.id�)),
�. db.Column(�photo_id�, db.Integer, db.ForeignKey(�photos.id�))
)

The bolded text above shows how the �tags� relationship between the Photos and the Tags models that is added to the Photos model. SQLAlchemy wants us to add this �tags� association to one of the models that are in it (only one more on that below).

The �tags� relationship is physically recorded in the database table �photo_tags� and it doesn�t get a �class Model� like the Photos and Tags have. Instead, it is setup as shown above to indicate that it is a table with foreign keys to the Photos and Tags tables.

Key things to note about the �tags� reference in the Photos model:

  • �Tags� model is related model
  • �photo_tags� is referenced as the �secondary� or relationship table.
  • �photos� is referenced as �backref�. The significance of this is that you don�t have to add a �tags� to the Tags model. The �backref� will allow you to reversibly reference Photos and Tags eg you can use the �tags� to get
    • Tags related to a specific Photo
    • Photos related to a specific Tag

An example of a view that can use the relationship established above follows. This view retrieves all tags for a specific photo:


@app.route(�/photo/�)
def photo(requested_photo_id=None):
�.requested_photo= Photos.query.get(requested_photo_id)
�.return render_template( �photo.html�, requested_photo = requested_photo
)

Here is what happens in this view:

  • The requested_photo_id is retrieved from the url.
  • The SQLAlchemy query Photos.query.get(requested_photo_id) uses that requested_photo_id to retrieve the photo from the Photos model/MySQL database table, and its related tags.
  • The retrieved requested_photo object is passed to template �photo.html� along with the related tag info.
  • Finally it is in the photo.html template where everything comes together. The template below is simple HTML page that presents the requested_photo_id and its related tags.







Photo Details



Photo ID: {{ requested_photo.id }} 


Photo file name: {{ requested_photo.filename }}




{% for tag in requested_photo.tags %}

tag.id: {{ tag.id}}, tag.tagname: {{ phototag.tagname }} 

{% endfor %}

The requested_photo id and filename come from the Photos Model/MySQL table.

The photo�s related tags come from the requested_photo �tags� association by referencing it as �requested_photo.tags� (this is the awesome part) and then looping through the tags to retrieve each tag so they can be printed on webpage individually.

Related Posts