I’ll show you how to make a database-backed dashboard in under 3 minutes. First, install the following:

pandas (>=0.14.1) + matplotlib
the database driver of your choice (e.g. MySQLdb, psycopg2—whichever driver corresponds to the database your data lives in)

With the prerequisites packages installed, we can populate a pandas DataFrame from read_sql:

import MySQLdb
from pandas.io.sql import read_sql
import pandas as pd

db_connection = MySQLdb.connect(read_default_file='~/.my.cnf')
query = """\
SELECT 
    date(created_at) as date, 
    count(*) as count
FROM events
GROUP BY 1"""
df = read_sql(query, db_connection)

/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pytz/__init__.py:29: UserWarning: Module argparse was already imported from /usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/argparse.pyc, but /usr/local/lib/python2.7/site-packages is being added to sys.path
  from pkg_resources import resource_stream

df.head() # taking a peak at the data

Sweet, we have some data, whoa!

df.plot()

<matplotlib.axes.AxesSubplot at 0x112921f90>

Whoops, we aren’t making use of a datetime index so

We don’t have datetime ticks on the X-axis
We aren’t seeing the “holes” in our timeseries data

So let’s solve both of those:

df.date = pd.to_datetime(df.date)
df.set_index('date', inplace=True)
df = df.reindex(pd.date_range(min(df.index), max(df.index)), fill_value=0)

df.plot()

<matplotlib.axes.AxesSubplot at 0x11293a210>

Wow. We’re pretty much done. Oh yeah, let’s make this dashboard available—1) via web app, 2) via email.

1. serving a dashboard via web app

We’ll use Flask—this is what there is to it:

#!/usr/bin/env python

from flask import Flask, make_response
from cStringIO import StringIO
from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
import matplotlib.pyplot as plt

import MySQLdb
from pandas.io.sql import read_sql
import pandas as pd

app = Flask(__name__)
db_connection = MySQLdb.connect(read_default_file='/Users/max/.my.cnf.snailbo')

@app.route('/')
def index():
    return """\
<html>
<body>
<img src="/plot.png">
</body>
</html>"""

@app.route('/plot.png')
def plot():
    query = """\
SELECT
    date(created_at) as date,
    count(*) as count
FROM events
GROUP BY 1"""
    df = read_sql(query, db_connection)
    df.date = pd.to_datetime(df.date)
    df.set_index('date', inplace=True)
    df = df.reindex(pd.date_range(min(df.index), max(df.index)), fill_value=0)

    df.plot()
    canvas = FigureCanvas(plt.gcf())
    output = StringIO()
    canvas.print_png(output)
    response = make_response(output.getvalue())
    response.mimetype = 'image/png'
    return response

if __name__ == '__main__':
    app.run(debug=True)

If you run this as a script, navigate to http://localhost:5000/ in your browser, you should see this:

blah

Awesome.

2. send the “dashboard” as an email

We could also embed this chart in an email—perhaps using cron to send it to ourselves every X hours?

Here’s how:

#!/usr/bin/env python
# http://stackoverflow.com/a/920928

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
from cStringIO import StringIO
from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
import matplotlib.pyplot as plt
import MySQLdb
from pandas.io.sql import read_sql
import pandas as pd

sender = 'bob@example.com'
recipient = 'bob@example.com'

# Create the root message and fill in the from, to, and subject headers
msg_root = MIMEMultipart('related')
msg_root['Subject'] = 'test message'
msg_root['From'] = sender
msg_root['To'] = recipient
msg_root.preamble = 'This is a multi-part message in MIME format.'

# Encapsulate the plain and HTML versions of the message body in an
# 'alternative' part, so message agents can decide which they want to display.
msg_alt = MIMEMultipart('alternative')
msg_root.attach(msg_alt)

msg_text = MIMEText('This is the alternative plain text message.')
msg_alt.attach(msg_text)

# We reference the image in the IMG SRC attribute by the ID we give it below
msg_text = MIMEText("""\
<html>
<body>
<h1>awesome sauce</h1>
<img src="cid:image1">
<br>Nifty!
</body>
</html>""", 'html')
msg_alt.attach(msg_text)

# Create the chart
query = """\
SELECT
date(created_at) as date,
count(*) as count
FROM events
GROUP BY 1"""
db_connection = MySQLdb.connect(read_default_file='/Users/max/.my.cnf.snailbo')
df = read_sql(query, db_connection)
df.date = pd.to_datetime(df.date)
df.set_index('date', inplace=True)
df = df.reindex(pd.date_range(min(df.index), max(df.index)), fill_value=0)

df.plot()
canvas = FigureCanvas(plt.gcf())
output = StringIO()
canvas.print_png(output)
msg_image = MIMEImage(output.getvalue())

# Define the image's ID as referenced above
msg_image.add_header('Content-ID', '<image1>')
msg_root.attach(msg_image)

server = smtplib.SMTP('smtp.example.com')
server.ehlo()
server.starttls()
server.login(sender, 'mypassword')
server.sendmail(sender, recipient, msg_root.as_string())
server.quit()

(221, '2.0.0 closing connection y7sm19409562qai.30 - gsmtp')

Lookin’ good:

plot

addendum

There’s a few things we didn’t do that would’ve been extra-nifty:

use SQLAlchemy w/ pandas’ read_sql method—you can make some extremely “configurable” dashboards going in this direction
use Vincent or Bearcart instead of matplotlib (2 libraries created by the great Rob Story of datapad.io)
and a whole multitude of other visualizations besides a basic linechart

Drop me a line if you’ve got suggestions for other posts!

I'm Max Mautner and I'm your man!

Making database-backed dashboards in 3 minutes

1. serving a dashboard via web app

2. send the “dashboard” as an email

addendum

Forms in emails

Great Slides About Mobile Monetization