Flask webserver - using a database


Martin McBride, 2020-01-22
Tags template html
Categories flask sqlite

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.

If you found this article useful, you might be interested in the book Functional Programming in Python or other books by the same author.

Prev

Popular tags

2d arrays abstract data type alignment and animation arc array arrays behavioural pattern bezier curve built-in function callable object chain circle classes close closure cmyk colour combinations comparison operator comprehension context context manager conversion count creational pattern data types design pattern device space dictionary drawing duck typing efficiency ellipse else encryption enumerate fill filter font font style for loop function function composition function plot functools game development generativepy tutorial generator geometry gif gradient greyscale higher order function hsl html image image processing imagesurface immutable object index inner function input installing iter iterable iterator itertools l system lambda function len line linear gradient linspace list list comprehension logical operator lru_cache magic method mandelbrot mandelbrot set map monad mutability named parameter numeric python numpy object open operator optional parameter or partial application path pattern permutations polygon positional parameter print pure function python standard library radial gradient range recipes rectangle recursion reduce repeat rgb rotation scaling sector segment sequence setup shape singleton slice slicing sound spirograph sprite square str stream string stroke structural pattern subpath symmetric encryption template text text metrics tinkerbell fractal transform translation transparency triangle tuple turtle unpacking user space vectorisation webserver website while loop zip