Using Flask to answer SQL queries

Four Part series on creating a D3.js graph powered by Flask and SQL
  1. Running a Flask app on AWS EC2
  2. Using Flask to answer SQL queries
  3. Getting csv data from requests to a SQL backed Flask app
  4. A D3.js plot powered by a SQL database

In an part 1 I describe how to set up a Flask service on an AWS EC2 instance. In this post I'll set up the server to respond to queries against a SQL database.

Source code for a basic Flask app

Creating a database

1. The data

We'll use sqlite3 to provide an interface from python to SQL. For this example we'll create a simple database of national parks, the data is here, originally from wikipedia.

A look at the data:

$ head nationalparks.csv
Name,Location,Year Established,Area
Acadia National Park,Maine,1919,48876.58
National Park of American Samoa,American Samoa,1988,8256.67
Arches National Park,Utah,1971,76678.98
Badlands National Park,South Dakota,1978,242755.94

2. Creating the database

This script populates a database with the data from the file:

import csv
import sqlite3

conn = sqlite3.connect('natlpark.db')
cur = conn.cursor()
cur.execute("""DROP TABLE IF EXISTS natlpark""")
cur.execute("""CREATE TABLE natlpark
            (name text, state text, year integer, area float)""")

with open('nationalparks.csv', 'r') as f:
    reader = csv.reader(f.readlines()[1:])  # exclude header line
    cur.executemany("""INSERT INTO natlpark VALUES (?,?,?,?)""",
                    (row for row in reader))
conn.commit()
conn.close()

3. Accessing the database from Flask

Add the following lines to flaskapp.py (see part 1). This code handles managing connections to the database and provides a convenient query method.

import csv
import sqlite3

from flask import Flask, request, g

DATABASE = '/var/www/html/flaskapp/natlpark.db'

app.config.from_object(__name__)

def connect_to_database():
    return sqlite3.connect(app.config['DATABASE'])

def get_db():
    db = getattr(g, 'db', None)
    if db is None:
        db = g.db = connect_to_database()
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, 'db', None)
    if db is not None:
        db.close()

def execute_query(query, args=()):
    cur = get_db().execute(query, args)
    rows = cur.fetchall()
    cur.close()
    return rows

4. Add a request handler to show the database

Add the following to flaskapp.py and restart the server (sudo apachectl restart). Pointing a browser at (your public DNS)/viewdb should show the entire database.

@app.route("/viewdb")
def viewdb():
    rows = execute_query("""SELECT * FROM natlpark""")
    return '<br>'.join(str(row) for row in rows)

5. Add a query url request handler

To allow for queries on state, add the following to flaskapp.py and restart the server (sudo apachectl restart). Pointing a browser at (your public DNS)/state/{state-name} will return a list of all national parks in that state.

@app.route("/state/<state>")
def sortby(state):
    rows = execute_query("""SELECT * FROM natlpark WHERE state = ?""",
                         [state.title()])
    return '<br>'.join(str(row) for row in rows)

6. Note on cross site requests

Later in this series we'll want to query our app from a D3.js graph served from another site. To instruct our Flask server to respond to these requests add the following line to the /etc/apache2/sites-enabled/000-default.conf, right under <Directory flaskapp>:

Header set Access-Control-Allow-Origin "*"

Your apache config should now have a block that looks like this:

<Directory flaskapp>
    Header set Access-Control-Allow-Origin "*"
    WSGIProcessGroup flaskapp
    WSGIApplicationGroup %{GLOBAL}
    Order deny,allow
    Allow from all
</Directory>
Four Part series on creating a D3.js graph powered by Flask and SQL
  1. Running a Flask app on AWS EC2
  2. Using Flask to answer SQL queries
  3. Getting csv data from requests to a SQL backed Flask app
  4. A D3.js plot powered by a SQL database

Similar Posts



Comments

Links

Social