Flask webserver - using a database

By 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.

See also

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

Join the PythonInformer Newsletter

Sign up using this form to receive an email when new content is added:

Popular tags

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

Introduction
Navigation
Links