Once you have your data-model built for your application, you need to load it with data so you can fully explore it. You can't start to really build a solid user-interface, be it web, app or desktop until you have data that represents what the real end user will experience.

Mixer is a smart and easy solution to that problem. We'll walk through a simple example to show how you might go about loading your Flask application with a few thousand elements of data.

Lets build a straightforward application that has Users, Posts and Comments and manually set up a User, Post and Comment.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = 'MixerTest'
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///'

db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(50), nullable=False)

class Post(db.Model):
    __tablename___ = 'post'
    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    user = db.relationship('User', backref='posts')

class Comment(db.Model):
    __tablename__ = 'comment'
    id = db.Column(db.Integer(), primary_key=True)
    content = db.Column(db.Text(), nullable=False)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    user = db.relationship('User', backref='comments')
    post_id = db.Column(db.Integer(), db.ForeignKey('post.id'))
    post = db.relationship('Post', backref='comments')

with app.app_context():

    new_user = User(username='alice', email='alice@example.com')
    new_post = Post(title='Check out this gif!', user=new_user)
    new_comment = Comment(content='LOL! YOLO!', post=new_post, user=new_user)

    c = Comment.query.first()
    print(c.content)  # LOL! YOLO!
    print(c.user.username) # alice
    print(c.post.title) # Check out this gif!

We've defined a User who can create many Post objects and many Comment objects, each Comment belongs to a Post and a User. At this stage in your app, you'd want to load several users, dozens of Posts and perhaps hundreds of comments. This is where Mixer can swoop in and help us out.

One note, the nullable=False is important, it's telling our database that we need data for that entry, and we'll complain if we don't get it. If Mixer looks at a column where nullable=True (which is the default behaviour) then it will say "Pfft, I won't bother making data for that column because it's optional".

Lets begin by installing it via pip install mixer and then use it to build:

  • 10 Users
  • 100 Posts (Each linked to a User)
  • 1000 Comments (Each linked to a User and Post)

Sounds tricky, but we can do it in six lines of code. Lets just add the following code below to our program:

from mixer.backend.flask import mixer

users = mixer.cycle(10).blend(User)
posts = mixer.cycle(100).blend(Post, user=mixer.SELECT)
comments = mixer.cycle(1000).blend(Comment, user=mixer.SELECT, post=mixer.SELECT)

That's it! Lets grab the 500th Comment and look into it's details:

c = Comment.query.get(500)

print("Content:", c.content)
# Content: Et debitis alias sint dicta. Asperiores ...

print("Post Title:", c.post.title)
# Post Title: Debitis Et Velit Suscipit Ullam Voluptatibus

print("Username:", c.user.username, "Email:",  c.user.email)
# Username: parkpotato9 Email: bloodcrunching1@microsoft.tm

Notice how Mixer was smart enough to infer our email field needed a fake email, our username needed a believable username and even that our title would need Title Casing.

Because we have the relationships built, we could have asked Mixer to do all three steps by itself by just asking comments = mixer.cycle(1000).blend(Comment) but we would have ended up with 2000 User objects and 1000 Post objects because it wouldn't have known to randomly select an existing entry from the database and so would have created a new User for each Comment and Post.

After starting a new project that involved long lists of products, I inevitable turned to pagination to help manage the user interface and then I was reminded about how much boilerplate code is required on each template to get it working.

The Flask-SQLAlchemy includes a helpful pagination class utility that integrates easily into a standard ORM query— here's a simple example:

def department(name, page=1):
    dept = Department.query.filter_by(slug=name).first_or_404()
    products = Product.query.filter_by(department=dept).paginate(
        page, app.config["PRODUCTS_PER_PAGE"]

    return render_template(
        'department.html', department=dept, products=products

This is an example taken from a straightforward shopping application, it finds out what department the user is looking at, then returns the products within that department as a pagination object.

In your Jinja template, you then access the properties of the pagination object to build your user interface. Normally that looks something like:

{# Display product data #}
{% for product in products.items %}
    <p>{{ product.description }}</p>
{% endfor %}

{# If we have previous posts #}
{% if products.has_prev %}
    <a href="{{ url_for('department', slug=department.slug, page=products.prev_num) }}">
        &laquo; Previous products
{% else %}
    &laquo; Previous products
{% endif %}
{% if products.has_next %}
    <a href="{{ url_for('department', slug=department.slug, page=products.next_num) }}">
        Next products &raquo;</a>
{% else %}
        Next products &raquo;
{% endif %}

The downside of that is it's a lot of boilerplate code to use on each template. Your endpoints and arguments for your url_for is going to change on each view, so it forces the front-end designer to keep copy and editing this type of layout.

A Better Way™

Instead, we can lean on the information we already have to make some intelligent choices on how we wish to generate our pagination. Here is our macro that will handle it generically:

    {% macro paginate(paginator) %}
        {# A generally pluggable pagination macro, just supply it with the pagination object #}
        {# formatted for Bootstrap 3 #}

        {% set view_args = request.view_args %}
        {% do view_args.pop('page') %}

        <div class="text-center">
            <ul class="pagination pagination-lg">

                {% if paginator.has_prev %}
                    <li class="active">
                        <a href="{{ url_for(request.endpoint, page=paginator.prev_num, **view_args) }}">&laquo;</a>
                {% else %}
                      <li class="disabled"><a href="#">«</a></li>
                {% endif %}

                {% if paginator.has_next %}
                    <li><a href="{{ url_for(request.endpoint, page=paginator.next_num, **view_args) }}">&raquo;</a></li>
                {% else %}
                    <li class="disabled"><a href="#">&raquo;</a></li>
                {% endif %}
    {% endmacro %}

Now when you want pagination in your template, just do the following (assuming the above code is saved in a file called '_helpers.html':

    {% import '_helpers.html' as helpers %}

    {% for product in product.items %}
        <p>{{ product.description }}</p>
    {% endfor %}

    {{ helpers.paginate(products) }}

And it's good. The important thing is does is to grab the current endpoint that is being accessed from request.endpoint and then the arguments that were used to generate the url from request.view_args. That view_args dictionary is going to include a page key, which we don't want since we'll be adding one in, so we pop it off the list using the do function.

The do function is part of a built-in, but not enabled Jinja extension, it executes the commands without doing printing out any returned values. You'll need to register it against your Flask application by doing the following:


Now we have the endpoint, and the cleaned view_args which we can use in tandem with the pagination object to generate our pagination url's:

{{ url_for(request.endpoint, page=paginator.next_num, **view_args) }}

Which all means we get all of that boilerplate generated for us automatically without having to worry about remembering endpoints and their associated arguments.

SQLAlchemy is largely magic, you ask it to do things, and it just works. This is especially true when dealing with 'simple' relationships, e.g. one User has many Books.

This article is going to be a quick overview of how to query a variety of common relationships and will move on to the not so common versions.

We'll want to play with our code, so lets create a little application for it— I tend to do mostly do web-development using Flask, and so I tend to also use Flask-SQLAlchemy for my database work. It's just SQLAlchemy with a few utilities added into it— if you're a pure SQLAlchemy user, you'll follow along just fine.

Here's our basic app:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

## SQLAlchemy Models will live here... So replace the below User model with the
## example if you wish to play with it.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)

## Then we'll run in an app context to work with the ORM:

with app.app_context():

    # Replace the below with the query examples to have a play around. e.g:

    u = User(name='Alice')

    alice = User.query.filter_by(name='Alice').first()
    print alice

    # <User: Alice>

One Author has many Books

This is the most common relationship, a one-to-many relationship. It's the building block of all database schemas worthy of the name. It's worth looking at relationships from both sides to fully understand their scope and limitations. Remember that many is a number from zero upwards.

If I draw the relationship out I get::

----------           --------
| AUTHOR |1---------M| BOOK |
----------           --------

If I stand inside the Author side of the relationship and look out towards the Book object, I can see a M-- so I can say "I the author can write none, one or several books." If I then walk over to the Book side of the relationship and look out towards the Author object, I just see a 1 which means I can say "I, a book have a single author. I cannot have several authors". If we jump out of SQLAlchemy and look directly at the database tables, you'll see that in a one-to-many relationship, the many side (our book table) would have a foreign-key reference (in our case author_id within it) which is the structure of the relationship.

This makes sense— if you imagine picking a book up off a shelf in a library, it would have a reference to the author, in the case of a physical book, the reference is actually the authors name, but to find more about the author (to join to another source of data) we would have to go to Wikipedia and use that reference to find out more information.

So in a one-to-many join, the reference that defines the relationship lives on the many side.

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    articles = db.relationship('Book', backref='author')

    def __repr__(self):
        return '<Author:{}>'.format(self.name)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.Text)
    content = db.Column(db.Text)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))

    def __repr__(self):
        return '<Book:{}>'.format(self.title)

And now lets enter some data to play with:

bob = Author(name='Bob')
dune = Book(title='Dune')
moby_dick = Book(title='Moby Dick')

carol = Author(name='Carol')
ring_world = Book(title='Ring World')
fahrenheit = Book(title='Fahrenheit 451')

bob.books = [dune, moby_dick]
carol.books = [fahrenheit]


You note that we only add bob and carol to the session before we commit to the database, this is because SQLAlchemy is smart enough to add the books that we've assigned to them to the session automatically so we don't have to— quite the time-saver!

With that in mind, it's really easy to add another book to carol by doing the following.


SQLAlchemy automatically adds ring_world to the session, and so it's sent to the database when we commit the session.

And finally, the fun step— querying the models and relations.

author = Author.query.filter_by(name='Carol').first()
print author  # <Author:Carol>
print author.books  # [<Book:Ring World>, <Book:Fahrenheit 451>]

dune_book = Book.query.filter_by(title='Dune').first()
print dune_book  # <Book:Dune>
print dune_book.author  # <Author:Bob>

So we can see how to query from either the Book side or the Author side of the relation.

Many to Many (M2M) Relationships

In a Many to Many relationship both sides of the relationship can have zero to many entries, we are going to extend our example to include a M2M relationship by modelling the idea that "many Books have many Categories". To be clear, I mean that a book might be both in Romance and Comedy, or Science Fiction and Fantasy.

At first glance this feels the same as the one-to-many relationship we worked on before (one Author has many Books) but in that case, one book could only have one author. In a M2M relationship we're saying one Book has many categories, but we're also saying that one category has many books.

When we spoke about one-to-many relations, we said that the reference that defines the relationship lives on the many side— but now we have two many sides (which is.. too many) and it simply won't work.

A M2M relationship is actually just two one-to-many relationships working together. We might initially think a M2M relationship looks like this::

--------              ------------
| Book |M------------M| Category |
--------              ------------

The problem with that is that because both sides need to hold multiple references to the other table, there's nowhere to store those references, while retaining the benefits of using a database. So what a M2M relationship actually looks like is::

--------     -----------------     ------------
| Book |1---M| book_category |M---1| Category |
--------     -----------------     ------------

In SQLAlchemy parlance it's referred to as an association table. It's job is to manage the associations between the Book and Category tables. If we delved into the book_category table we would see just two columns: book_id and category_id (remember, the 'many' side of a relation gets the foreign-key references).

Because this is a very common database pattern, SQLAlchemy uses some magic that allows us to talk directly between Book and Category objects, under the surface SQLAlchemy will manage that book_category table for us.

Sometimes you wish to hold some information within that association table, perhaps if you had a M2M relationship between student and exams, you might want to include their grade or date they took the exam in the association table, in that case you want SQLAlchemy to define it as an association object and not a association table. In short, association tables are transparent to you, whereas association objects have data of their own which you access.

For now, we'll build an association table, as we're not storing extra data within it and are only concerned about the Book and Category endpoints. Lets add that table and the Category object to live along side the models we've already created:

categories = db.Table('categories',
    db.Column('category_id', db.Integer, db.ForeignKey('category.id')),
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'))

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    books = db.relationship('Book', secondary=categories,
                            backref=db.backref('categories', lazy='dynamic'))
    def __repr__(self):
        return '<Category:{}>'.format(self.name)

Now lets add some data, assign the books we have already created to those categories and show how the relationship works both ways.

scifi = Category(name='Science Fiction')
classic = Category(name='Classic')

classic.books = [moby_dick, dune, fahrenheit]
scifi.books = [dune, ring_world]

db.session.add_all([classic, scifi])

print dune, dune.categories.all()
# <Book:Dune> [<Category:Classic>, <Category:Science Fiction>]

The reason we added a .all() to the categories is that we defined the categories/book relationship as dynamic. That means it returns a query object, we need to launch that query to get results, .all() is one of the triggers to do that. The big benefit of defining the relationship as dynamic is that we can run further filters and queries on it:

print dune.categories.filter(Category.name.ilike('sci%')).all()
# [<Category:Science Fiction>]

In that example we've further filtered down results to those that have a name starting with 'sci' the i in ilike indicates that it's a case-insensitive search.

That's not the best example, so lets do something a little more useful.

Querying Over Multiple Relations

We now have an author who was written many books each of which can belong to multiple categories. We have one relationship tying the Author to the Book class, and another tying the Book to the Category class.

We've shown how we can use those relationships individually, but lets go one step further and talk across both. How would we find out which authors have penned a science fiction book? We'll add a third author, Alice, who has only written classics to show it's working:

alice = Author(name='Alice')
beowulf = Book(title='Beowulf')
beowulf.author = alice
beowulf.categories = [classic]

print Author.query.join(Author.books).filter(

# [<Author:Carol>, <Author:Bob>]

It's very readable, we ask for the Author objects, and tell it to join to the author's books— then we use the relationship between books and categories to filter to those which contain Science Fiction.

Next lets approach from the opposite side, we want to find out which categories Carol has published within. We're no longer able to use the 'contains' method because we're querying from the many to one side of the relationship between Book and Author.

To put it another way, the contains clause would essentially ask "Out of the list of authors on this book, is one of them Carol?" which would raise an error, because contains is expecting a list and would only get a single value (what we refer to as a scalar) because we've defined 'A Book can only have a single Author' when we built our relationship.

Because we're dealing with a scalar value on Author, we could just use a simple == comparison:

print Category.query.join(Category.books).filter(

# [<Category:Classic>, <Category:Science Fiction>]

Click here for the complete code for you to run and experiment with. If you have any queries that you think would be a good addition to this article, drop me a note.