Using SQLAlchemy for relationships between models
In order to store who created the Product, we need to add a field called
creator_id to Product as a foreign key that references the
ID field of
This field should be stored as an integer (since the User ID is an integer type). The second argument to
db.column will be the constraint we are adding, which is
ForeignKey constraint takes in the name of the table we are referencing and the field as a string, separated by a dot.
class Product(db.Model): ... creator_id = db.Column(db.Integer, db.ForeignKey('user.id'))
Now whenever we have an instance of
Product, we can access the the associated user through the
>>> prod = Product.query.first() >>> prod <Product 1> >>> prod.creator <User 2>
In addition to being able to access the
User object through
creator, we can also pass in
User objects to queries and when creating
>>> user = User.query.first() >>> prod = Product(name='Book', description='a book', creator=user) # Equivalent to Product(name='book', description='a book', creator_id=user.id) >>> db.session.add(prod) >>> db.session.commit() >>> Product.query.filter_by(creator=user).all() [<Product 1>] # Equivalent to Product.query.filter_by(creator_id=user.id).all()
creator attribute, however, is just one side of the relationship. The relationship between
Products can be described as a
one-to-many relationship, where one
User is the creator for many
Right now, we haven't told SQLALchemy that there is an attribute that belongs on the
User for the "one to many" relationship. In order to tell SQLAlchemy that there is a "one to many" relationship between
Product, we have to declare how the relationship works on each model. SQLAlchemy provides a
relationship method that we can use within each model, that provides a way to declare what model the relationship is linked to as the first argument. The parameter
back_populates denotes which attribute the relationship will be accessible through on the other model. For
Product, you can see how the
back_populates argument, references the attribute on the other model.
class User(UserMixin, db.Model): ... products = db.relationship("Product", back_populates='creator') class Product(db.Model): creator_id = db.Column(db.Integer, db.ForeignKey('user.id')) ... creator = db.relationship("User", uselist=False, back_populates="products")
By default, the relationship method, assumes that there may be many records on this relationship, but if we only expect there to be one record (in this case, a
Product will only have one creator), we also need to pass in
uselist=False. If we wanted to create a
many-to-many relationship, you would not need to include the
Now that the relationship is declared, we can access the products created by a user easily.
>>> prod = Product(name="Sample", description="test", creator_id=1) >>> prod.creator <User 1> >>> db.session.add(prod) >>> db.session.commit() >>> User.query.find(1).products [<Product 1>] >>> prod.creator.email "[email protected]"
Maintainability Tip: Instead of declaring the relationship on both models, SQLAlchemy provides a
backrefmethod, which allow you to just declare the relationship on one side. While it's easier, it trades off legibility of the relationship. You would have to look at every other model to figure out which attributes/relationships are available on any particular model. By explicitly declaring the relationship, it's easier for other developers to understand what relationships are available by glancing at the model.
In addition to accessing relationships through attributes, you can set or edit relationships through the relationship attributes.
>>> prod.creator = User.query.find(2) >>> prod.creator <User 2> >>> db.session.add(prod) >>> db.session.commit() >>> prod.creator_id 2
We will also want to setup a relationship between a
User and a
class User(UserMixin, db.Model): ... store = db.relationship("Store", uselist=False, back_populates='user') class Store(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) products = db.relationship("Product", back_populates='store') user = db.relationship("User", uselist=False, back_populates="store") @validates('name') def validate_name(self, key, name): if len(name.strip()) <= 3: raise ValueError('needs to have a name') return name
At this point, your
models.py file should look like this.
from sqlalchemy.orm import validates from flask_login import UserMixin from werkzeug.security import generate_password_hash