Jonnie Grieve Digital Media: Blog

Blog: Integrating SQLAlchemy with Flask – #4 (More Posts)

In a new series of blogs related to SQLAlchemy, I’m moving away from the command line environment and moving on to exploring how Python, Flask, and SQLAlchemy can work together to make a website that uses data dynamically.

Recap

When I left it in my last blog, there was an app that had a local development server with 4 interconnected routes that formed a small website.  This website contained a form which when submitted, added that data to a SQLite database.  By the end of this blog, I’ll have set up the ability to read that entire list of data, and to be able to click through to another route that displays filtered data based on the corresponding primary key.

You can find the previous editions to this series here.

• https://blog.jonniegrieve.co.uk/blog_posts/integrating-sqlalchemy-with-flask-1/
• https://blog.jonniegrieve.co.uk/blog_posts/integrating-sqlalchemy-with-flask-2/
• https://blog.jonniegrieve.co.uk/blog_posts/integrating-sqlalchemy-with-flask-3/

Let’s get cracking!

When I first made this simple design, I wanted it to be a somewhat rudimentary example of a site that talks to a dataset with just a couple of columns but no more, as a demonstrable example of using the Flask and SQLAlchemy technologies. This far along we have a simple site that does this. But there are still a couple more CRUD operations to demonstrate. UPDATE and DELETE and there are a few more things to talk about with READ operation.

Thankfully SQLAlchemy provides a simple way to do all of this in a Flask environment.

Let’s finish off READ so we do a little more with the data.

Currently, the app displays all the data and its columns.

Flask: Integrating SQLAlchemy Data into Flask

We need to be able to click through to a route that displays the information again but only the data applicable to that record. In other words, we need a filter query that limits results to a single record.

How would we go about getting a page of information for a particular name? We’d want to return the original data and some additional details that tell us more info. To do this in a URL we can pass in a reference to the primary key and the template that it’ll be returned on. To do that, we need another route. A route that for this app, I’m calling “/single” and a new HTML template.

First, inside the index.html template, I’m going to add an anchor element inside the table cell (TD) element for the name column. Remember this is inside a for loop, so it will be the same for all link elements.

{% for people in roster %}

<tr>
<td> <a href="{{ url_for('single')}}">{{ people.name }}</a> </td>
<td>{{ people.age }}</td>
<td>{{ people.joined }}</td>
</tr>

{% endfor %}

So the “person” column now links to a template (single.html) and will display the same template regardless of which link is clicked.  This means the same information is always displayed, which isn’t very useful of course. We have to find a way to display unique information for each link, just as we’ve been doing with Flasks content blocks.

Add the following to a file called single.html to the templates directory you previously created.

{% extends "main.html" %}

{% block title_block %} title index {% endblock %}

{% block nav_block %}
<nav>
<ul>
<li><a href="/" class="selected" >One</a></li>
<li><a href="/two">Two</a></li>
<li><a href="/three">Three</a></li>
</ul>
</nav>
{% endblock %}

{% block main_block %}

<h2>Roster ID Number: Number</h2>

<div class="name_data_single">

<h3>Name information goes here </h3>
<p> Description data goes here </p>

</div>

{% endblock %}

Now in the index template, we need to make sure the link is ready to receive a parameter.

<a href="{{ url_for('single', id=people.id )}}"> {{ people.name }} </a>

In databases, each entity has an ID (or unique identifier) for a record of data which is assigned as the primary key for that entity. So we can use that in our URLs for each item in the database. We’re passing in a new parameter called ID that represents the value of the primary key and is rendered in single.html.

We need to get that parameter from somewhere. So to make it possible, we need to add a new route, (I called it single) which needs to pass data in a particular way.

@app.route('/single/<id>')
def single(id):
    return render_template('single.html');

Here is where the route URL is built and inside the angle brackets “<>” <id> is the placeholder that looks for the ID integer.  As it stands now, the links should be correctly linking to all the instances of sigle.html according to ID.

List of data records with a clickable field

Next, it’s time to see if we can find the relevant unique record with a Query on the Roster model.

Inside the single() function, assign the following query to a variable.

roster = Roster.query.get(id)

So what this query is doing is calling the Roster model which is the schema so SQLAlchemy knows the entity to query the data from.  Then we call get() on SQLAlchemy’s query object which is SQLAlchemy’s way of saying find the unique identifier, the primary key of the selected link.  Then, on the last line, the query is assigned to a parameter that passes it to the single.html template. The final code for the route looks like the following:

@app.route('/single/<id>')
def single(id):
    roster = Roster.query.get
    return render_template('single.html', roster=roster);

So with a few simple methods and SQLAlchemy’s one-line database queries, we have a template that renders pages multiple times but with different text according to the data passed in via the route URL.  Now that the template has access to the roster variable we can call the field names on it and display it by putting, inside the “mustache” brackets.  Like this:

{{ model.column_name }}

    <h2>Roster ID Number: {{ roster.id }}</h2>
    <div class="name_data_single">
        <h3>{{ roster.name }}</h3>
        <p>{{ roster.description }}</p>
    </div>

And voila. Now the single page has access to the data so it can be displayed.  So now the application is reading the data in 2 ways. It’s displaying all the data in a single route and it is displaying one record of data in another route, depending on the value of the ID parameter that is given to it.

That does it for the READ part of this site.  It all works, but there’s no way to change or delete the information via the website yet. To refresh the database I have to physically delete the database file and recompile the file via the command line and add new data.  That’s not fit for purpose.  So the interface needs to get a little further complex to make that fix.  In the next stage, I’ll work on making it possible for the user to make edits and deletions via the website.