Flask: Read from a Database

It is essential to get your database connection working without errors before you try to do more with the database and Flask.

In this chapter we focus on reading data from a SQLite database, using Flask-SQLAlchemy.

The database

The SQLite database used here is named sockmarket.db. It has only one table, named socks. It has seven fields: id, name, style, color, quantity, price, and updated. You will see the table name and the field names later, in the Python code. The image below is a screenshot from the DB Browser for SQLite, showing the top rows of the socks table.

Socks table in DB Browser screenshot

First read

Without templates or anything fancy, let’s attempt to read some data from the database and return it in the browser.

../python_code_examples/flask/databases/read_db_basic.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
""" read from a SQLite database and return data """

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# the name of the database; add path if necessary
db_name = 'sockmarket.db'

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_name

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

# this variable, db, will be used for all SQLAlchemy commands
db = SQLAlchemy(app)

# each table in the database needs a class to be created for it
# db.Model is required - don't change it
# identify all columns by name and data type
class Sock(db.Model):
    __tablename__ = 'socks'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    style = db.Column(db.String)
    color = db.Column(db.String)
    quantity = db.Column(db.Integer)
    price = db.Column(db.Float)
    updated = db.Column(db.String)

#routes

Everything up to line 18 comes from the script explained in the previous chapter.

The model

Lines 21–29 provide a model so that Python can translate the socks table. It’s a Python class that inherits from the Model class from SQLAlchemy. (Remember, db refers to SQLAlchemy.) We could name the new class anything, but Sock makes sense because this table’s data is all about socks.

Note

Python style dictates that a class starts with an uppercase letter and uses camelCase. Using uppercase (as in Sock here) helps us recognize when a class is being used in Python.

If your database has more than one table, you will need to create an additional class like this for each additional table.

Note the following in the highlighted class above:

  • Identify the primary_key field as shown (line 23).

  • Write the field names exactly as they appear in the table.

  • In __tablename__ = 'socks', note that the name of the table is case-sensitive. Match it to your actual table’s name.

  • String, Integer or Float must match the data type in your fields.

  • Include every field in the table.

See all possible data types here.

The query in the route

We will provide only one route to start with, and it includes a try/except just like the example in the previous chapter.

../python_code_examples/flask/databases/read_db_basic.py
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#routes

@app.route('/')
def index():
    try:
        socks = Sock.query.filter_by(style='mini').order_by(Sock.name).all()
        sock_text = '<ul>'
        for sock in socks:
            sock_text += '<li>' + sock.name + ', ' + sock.color + '</li>'
        sock_text += '</ul>'
        return sock_text
    except Exception as e:
        # e holds description of the error
        error_text = "<p>The error:<br>" + str(e) + "</p>"
        hed = '<h1>Something is broken.</h1>'
        return hed + error_text

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

The only code that “talks” to the database is in line 36.

  1. socks is a new variable. We assign to it the data we are pulling from the database.

  2. Sock.query refers to the class we built, Sock, starting on line 21. We are querying the table specified in that class.

  3. .filter_by() limits what we’re asking for. It’s the WHERE clause in regular SQL.

  4. style='mini' — in this table, style is a field name. 'mini' is a value in that field (column). So we will get only socks with the style “mini” — not “knee-high,” “ankle,” or “other.”

  5. order_by() selects a field (column) to determine the order of the results listing. This is optional. Any field could be used.

  6. Sock.name refers to the property name in the Sock class.

  7. .all() is tacked onto the end of every query, unless you expect or want only one record to be returned — in which case, use .first() instead.

The default for order_by() is ascending. To sort by a column in descending order:

from sqlalchemy import desc
Sock.query.filter_by(style='mini').order_by( desc(Sock.price) ).all()

Lines 37–40 create a string using the data in socks and adding HTML tags around the data — the <ul> and <li> tags should be familiar to you.

After the for-loop completes, and the final closing tag </ul> is concatenated to the string, sock_text, and the final string will be:

<ul><li>D'Shawn, blue</li><li>Divine, white stripes on navy</li><li>Faye, dark purple</li>
<li>Isabel, white</li><li>Jenny, blue</li><li>Jo-Anne, brown</li><li>Krissie, blue</li>
<li>Lizzy, red</li><li>Nancie, purple</li><li>Tanya, red</li><li>Terrie, blue stripe</li></ul>

Assuming that everything worked, that string is what will be returned (line 41).

The rest of the code (lines 42–49) is from the database intro chapter.

When the script runs, this is the result in the browser:

Results in browser screenshot

We have successfully queried data from this database and used it in a Flask route.

Now we will put it into a Flask template.

Add a template

Using a Flask template, we can make the list of socks display more beautifully:

Socks list in templated page screenshot

Here is the central section of the HTML template that will display the socks. Instead of using an HTML list with <ul> and <li> tags, we’re using the standard HTML table tags with Bootstrap styles applied.

../python_code_examples/flask/databases/flask_db_read/templates/list.html
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
      <h1 class="pt-5 pb-2">List of Socks</h1>

      <p class="lead alert alert-warning">This is a list of all the socks of the
			<strong>{{ style }}</strong> style in our inventory.</p>

	  <p class="ml-4"><a href="{{ url_for('index') }}">Return to the index.</a></p>

		<table class="table">
			<tr>
				<th>Sock Name</th>
				<th>Color</th>
				<th>Price</th>
				<th>Quantity</th>
				<th>Updated</th>
			</tr>

			<!-- here is a for-loop to give us all the socks -->
			{% for sock in socks %}
			<tr>
				<td>{{ sock.name }}</td>
				<td>{{ sock.color }}</td>
				<td>${{ sock.price }}</td>
				<td>{{ sock.quantity }}</td>
				<td>{{ sock.updated }}</td>
			</tr>
			{% endfor %}
		</table>

Refer to the Flask: Web Forms chapter for details about adding Bootstrap to Flask. Refer to the Flask Templates chapter for details about working with templates.

The entire app, including its templates and the database file, is here.

The previous route

Our first working route with no template was this:

@app.route('/')
def index():
    try:
        socks = Sock.query.filter_by(style='mini').order_by(Sock.name).all()
        sock_text = '<ul>'
        for sock in socks:
            sock_text += '<li>' + sock.name + ', ' + sock.color + '</li>'
        sock_text += '</ul>'
        return sock_text
    # lines omitted here

Note that the database query is the same below, in the new route that uses a Flask template.

The new route

If you refer to the Jinja code in the template above, you will see that it requires a value for style{{ style }} and the value socks — used in the for-loop. (Those lines are highlighted.)

In the render_template() function returned by this route (below), you can see that these values are passed to the template.

@app.route('/inventory/<style>')
def inventory(style):
    socks = Sock.query.filter_by(style=style).order_by(Sock.name).all()
    return render_template('list.html', socks=socks, style=style)

The one difference in the database query is that instead of getting only records for which the style is 'mini', here the value for style comes from a route variable. This technique is covered in the templates chapter.

A different database query

If you’re familiar with SQL, you know there are many different kinds of queries we can write against a database. All of them can be constructed one way or another with Flask-SQLAlchemy.

  • Search on the Query API page in the SQLAlchemy documentation when you need to work out the syntax for an unusual query.

As an example, let’s see how to get this result for the index page of the socks app:

Socks index template screenshot

We might know exactly which styles are available in this database — but what if we don’t know? What if we expect new styles to be added without warning? Then we cannot hard-code the style values into the page you see above. Someday there might be a fifth style. We need to extract the styles from the database itself.

../python_code_examples/flask/databases/flask_db_read/read_db.py
35
36
37
38
39
40
41
42
#routes

@app.route('/')
def index():
    # get a list of unique values in the style column
    styles = Sock.query.with_entities(Sock.style).distinct()
    return render_template('index.html', styles=styles)

Above is the route for the index (or starting page) of the app. It calls the template named index.html, which you can see in part below. But focus your attention on the database query in line 40.

  • The with_entities() method restricts the columns returned to only the one(s) you want. In this case, we want only the style column.

  • The distinct() method returns only unique values in the specified column.

Therefore, the contents of the new variable styles in line 40 above are one instance of each unique value in the style column. By passing styles to the template index.html, we can use those values to create a list of links.

../python_code_examples/flask/databases/flask_db_read/templates/index.html
21
22
23
24
25
26
27
28
29
30
31
      <h1 class="pt-5 pb-2">Welcome to the Sock Market!</h1>

      <p class="lead">This is the index page for an example Flask app using a SQL database containing an inventory of many colorful socks.</p>

      <p>Select the style of sock you would like to see:</p>

      <ul>
          {% for s in styles %}
          <li><a href="{{ url_for( 'inventory', style=s.style ) }}">{{ s.style }}</a></li>
          {% endfor %}
      </ul>

Above is the HTML in the index.html template. The list styles came from the route, and we can loop over the list with a Jinja directive.

The URL value for href= must be encoded for Flask (line 29). This was covered in the templates chapter.

In line 29, both the href= value and the text that is linked use the same value from styles. As we loop over styles, the current record is s — so the value in the style column is accessed with s.style. Because the list styles contains only unique values from the style column, each different style appears only once.

The URL that executes the other route function in this app is:

/inventory/<style>

You’ve seen this kind of Flask route before:

/president/<num>

The result of clicking knee-high on the index page is that the following HTTP request is sent to the server:

localhost:5000/inventory/knee-high

The HTTP response comes from this route in the app script, with the value of style now being knee-high:

@app.route('/inventory/<style>')
def inventory(style):
    socks = Sock.query.filter_by(style=style).order_by(Sock.name).all()
    return render_template('list.html', socks=socks, style=style)

In the browser, the list.html template will be loaded, displaying a table showing all sock records that have the style knee-high.

Summary

Reading from the database depends on being able to connect to the database in the first place. That is covered in Flask and Databases.

After that is accomplished, you need to write a database query (in a Flask route function) that gets what you need from the database. You might need only one record (part or whole), several records, or just values from one column in the database.

Before writing any queries, though, you need to create a model for each table in your database. The name of this model class will be used in your database queries.

If your database has more than one table, you might need to write SQL joins to get what you need. Joins are not covered here. Explicit linking of tables with a foreign key also is not covered here.

Values obtained from a database query are then passed to a Flask template. Using the Jinja template syntax in the template file’s code determines where the values appear in the HTML. That is covered in Flask Templates and also in this chapter.

.