Python informer

Improve your Python coding skills

Flask webserver - using a database

This article is part of a series on Flask.

In this section we will take our website a step further and add some simple database functionality.

The database: SQLite

When you think about web sites and databases, MySQL is probably the first thing that springs to mind. But we are going to use a simpler alternative, SQLite. This has two advantages:

  • It is built in to Python
  • The entire database is stored in a single file

This means there are no servers, or users, or permissions to worry about. SQLite connects to the database by opening the file.

SQLite isn’t as efficient or scaleable as other databases. However the authors claim it should be easily capable of running a website which gets 100,000 hits per day.

A simple database program

This article isn’t going to go into much depth about using SQLite and SQL in general, that is a topic on its own. But here is a simple program to read the contents of a database, to get you up and running. It isn’t a web server, it is just a simple standalone program which prints output to the console.

There is a database in the file data.db on github. Copy it into your working directory. It contains a small database about fruit.

Here is a simple bit of code to open the database and read the contents using an SQL query:

import sqlite3

con = sqlite3.connect('data.db')
cur = con.cursor()    
cur.execute("SELECT * FROM fruits")
rows = cur.fetchall()
print(rows)

The key points here are:

  • We connect to the database using connect, supplying the name of the file containing the database.
  • We use a cursor curto get the table data.
  • The SQL statement SELECT * FROM fruits gets all the fields of all the rows in the fruits table.
  • The data is returned as a list of tuples, one per row of data.

Using the database to create a web page

We can use the ideas from the example above to create a web page based on the database. We will create a page which lists the names of all the fruits in the database. Here is the Python code - for brevity we have removed the About and Status pages:

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)

@app.route('/')
def index():
    return render_template('index.html', title='Home page',
                           content='My first Flask site')

@app.route('/fruits')
def fruits():
    con = sqlite3.connect('data.db')
    cur = con.cursor()    
    cur.execute("SELECT * FROM fruits")
    rows = cur.fetchall()

    return render_template('fruits.html', title='Fruits list',
                           rows=rows)

if __name__ == '__main__':
    app.run(debug=True, port=80, host='0.0.0.0')

We fetch rows from the database, and pass it to render_template as a parameter. We use a new template, fruits.html to display this.

The template

So how do we use a template to display a list of fruits? We don’t know how many fruits will be in the list when we create the template.

Fortunately the template syntax supports looping:

<html>
  <head>
    <link rel="stylesheet" href='/static/main.css' />
  </head>
  <body>
    <h1>{{title}}</h1>
    {% for row in rows %}
      {{row[0]}}</br>
    {% endfor %}
 </body>
</html>

The for loop executes once for each value in rows (ie for each fruit). On each pass through, row contains the data for one row of the table (ie a tuple containing the name, color and image for the fruit). We display the first element of the tuple, row[0], which is the name.

The next section describes how to create a detail page, which displays the data for one type of fruit. The full code for this section and the next section, adding a detail view can be found on github.