{ One To Many Associations. }

Objectives

By the end of this chapter, you should be able to:

  • Create a one-to-many association using Flask-SQLAlchemy and Flask Migrate

Associations

Let's add an association. We're going to start with a one-to-many as they are much easier to implement. Let's build off of our application and imagine that one student has many excuses. Go ahead and modifty your Student model so that it has one more attribute:

class Student(db.Model):

    __tablename__ = "students"

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.Text)
    last_name = db.Column(db.Text)
    excuses = db.relationship('Excuse', backref='student',
                                lazy='dynamic')

    def __init__(self, first_name, last_name):
        self.first_name = first_name
        self.last_name = last_name

So everything looks normal, but we have this property called excuses! What we are doing here is establishing a relationship between a student and that student's excuses. Every student is going to have a property called 'excuses' which will return a list of all of the excuses for that student. By using a backref, each excuse will have a property called student, which refers to the entire student object who has that specific excuse.

What about lazy? Lazy defines when SQLAlchemy will load the data from the database. The options for lazy are:

'select' (which is the default) means that SQLAlchemy will load the data as necessary in one go using a standard select statement.

'joined' tells SQLAlchemy to load the relationship in the same query as the parent using a JOIN statement.

'subquery' works like 'joined' but instead SQLAlchemy will use a subquery.

'dynamic' is special and useful if you have many items. Instead of loading the items SQLAlchemy will return another query object which you can further refine before loading the items. This is usually what you want if you expect more than a handful of items for this relationship.

If a student is going to have many excuses, we also need an excuse model! Here's what that might look like (you can put this in your app.py, just like with the Student model):

class Excuse(db.Model):

    __tablename__ = "excuses"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    is_believable = db.Column(db.Boolean)
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'))

    def __init__(self, name, is_believable, student_id):
        self.name = name
        self.is_believable = is_believable
        self.student_id = student_id

Since we set up a backref in our Student model, we do not need to do much more heavy lifting here aside from make sure we have a foreign key! (Though if you want, you can define a repr instance method on excuses so that you get more helpful messages when you look at an excuse in the terminal.)

At this point, your app.py should look something like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://localhost/learn-flask-migrate'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Student(db.Model):

    __tablename__ = "students" # table name will default to name of the model

    # Create the three columns for our table
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.Text)
    last_name = db.Column(db.Text)
    excuses = db.relationship('Excuse', backref='student',
                                lazy='dynamic')

    # define what each instance or row in the DB will have (id is taken care of for you)
    def __init__(self, first_name, last_name):
        self.first_name = first_name
        self.last_name = last_name

    # this is not essential, but a valuable method to overwrite as this is what we will see when we print out an instance in a REPL.
    def __repr__(self):
        return f"The student's name is {self.first_name} {self.last_name}"

class Excuse(db.Model):

    __tablename__ = "excuses"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    is_believable = db.Column(db.Boolean)
    # remember - the name of our table is "students"
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'))

    def __init__(self, name, is_believable, student_id):
        self.name = name
        self.is_believable = is_believable
        self.student_id = student_id

if __name__ == '__main__':
    app.run(debug=True,port=3000)

Since we just added another model, we'll also need to create and run a new migration. Remember, we only need to init once: this time, we just need to run the following commands:

python manage.py db migrate
python manage.py db upgrade

You should now have a second table in your database, as well as an association between the two tables! You should also see both of your migrations inside of migrations/versions.

Now let's open up an ipython REPL and use DML to add some students and excuses!

from app import db, Student, Excuse

elie = Student('Elie', 'Schoppik')
matt = Student('Matt', 'Lane')
michael = Student('Michael', 'Hueter')

db.session.add_all([elie, matt, michael])

db.session.commit()

len(Student.query.all()) # 3

elie = Student.query.get(1)

excuse1 = Excuse('My homework ate my dog', False, 1)

db.session.add(excuse1)
db.session.commit()

elie.excuses.all() # list of excuses
elie.excuses.first().is_believable # False

Excuse.query.get(1).student # The student's name is Elie Schoppik

excuse2 = Excuse('I overslept', True, 1)

db.session.add(excuse2)
db.session.commit()

len(elie.excuses.all()) # 2

If you want to double-check that your data is stored in the database, we can also hop into the database using psql learn-flask-migrate and run some SQL commands! You should see something like this:

SELECT * FROM students;

/*
 id | first_name | last_name
----+------------+-----------
  1 | Elie       | Schoppik
  2 | Matt       | Lane
  3 | Michael    | Hueter
(3 rows)
*/

SELECT * FROM excuses;

/*
 id |          name          | is_believable | student_id
----+------------------------+---------------+------------
  1 | My dog ate my homework | f             |          1
  2 | I overslept            | t             |          1
(2 rows)
*/

Screencast

If you'd like to see an example of building an application with a 1:M association and migrations, feel free to watch the screencast below.

Exercise

Complete the Second Flask-SQLAlchemy exercise.

When you're ready, move on to Responding with JSON

Continue

Creative Commons License