A simple demo of a Flask web app that queries a sqlite database

database.html

<p> This is the database page </p>

{% for state, capital in results_dict.items() %}
    <p> {{state}}: {{capital}} </p>
{% endfor %}

database.schema

BEGIN;

CREATE TABLE main_table (
    "id" INTEGER NOT NULL PRIMARY KEY,
    "state" TEXT NOT NULL UNIQUE,
    "capital" TEXT
    );

INSERT INTO main_table (state, capital) VALUES ("Massachusetts", "Boston");
INSERT INTO main_table (state, capital) VALUES ("Indiana", "Indianapolis");
INSERT INTO main_table (state, capital) VALUES ("Maryland", "Annapolis");
INSERT INTO main_table (state, capital) VALUES ("New York", "Albany");
INSERT INTO main_table (state, capital) VALUES ("Texas", "Austin");

COMMIT;

index.html

<p> This is the homepage </p>

web_app.py

#! /usr/bin/env python

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)


@app.route('/database/')
def database():
    """Returns webpage containing query results from database

    Returns
    -------
    template : obj
        The ``database.html`` webpage.
    """

    # Connect to database
    connection = sqlite3.connect('data.db')
    cursor = connection.cursor()

    # Execute query
    sql_command = 'SELECT * FROM main_table;'
    cursor.execute(sql_command)

    # Parse results
    results_dict = {}
    results = cursor.fetchall()
    for result in results:
        results_dict[result[1]] = result[2]

    return render_template('database.html', results_dict=results_dict)


@app.route('/')
def main():
    """Generates the website homepage.

    Returns
    -------
    template : obj
        The ``index.html`` template.
    """

    return render_template('index.html')

if __name__ == '__main__':

    app.run()