Generate HTML reports from arbitary PostgreSQL queries

mail_sql_report

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Generates email reports from any SQL query. Usage:
#
#     ./mail_sql_report config_file
#
# This script is not SQL injection safe script and should be run only
# with read-only credentials if you want to play sure. This just runs
# the SQL as-is.
#
# Author: joel.lehtonen+pghtml@iki.fi

import datetime
import smtplib
from subprocess import check_output
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import sys
import ConfigParser
from string import Formatter

formatter = Formatter()
configParser = ConfigParser.RawConfigParser()
configParser.read(sys.argv[1])

def conf(key):
    return configParser.get('global',key)

def format_key(key):
    return formatter.vformat(conf(key),sys.argv,user_fields)

def to_list():
    # FIXME: If email recipient name contains comma, even inside
    # quoted section, it breaks this.
    return map(str.strip,conf('to').split(','))

# Neat way to read postgresql parameters from a separate section
credentials = ' '.join(map('='.join,configParser.items("database")))

# Generating available subject fields, usable in subject with {name}
# notation
now = datetime.datetime.today()
user_fields = {'date':now.date().isoformat(),
               'time':now.time().isoformat()}

# Create message container - the correct MIME type is multipart/alternative.
msg = MIMEMultipart('alternative')
msg['Subject'] = format_key('subject')
msg['From'] = conf('from')
msg['To'] = conf('to')

# Generate both formats, latter with postgresql HTML output enabled
sql = format_key('sql')
text = check_output(["psql",credentials,"-c",sql])
html = check_output(["psql","-H",credentials,"-c",sql])

# Record the MIME types of both parts - text/plain and text/html.
part1 = MIMEText(text, 'plain',"UTF-8")
part2 = MIMEText(html, 'html',"UTF-8")

# Attach parts into message container. According to RFC 2046, the last
# part of a multipart message, in this case the HTML message, is best
# and preferred.
msg.attach(part1)
msg.attach(part2)

# Send the message via SMTP server. NB! SMTP wants recipients as list
# and not as comma separated string.
s = smtplib.SMTP(conf("smtp_server"))
s.sendmail(conf("from"), to_list(), msg.as_string())
s.quit()

unhappy_customers.conf

[global]
smtp_server = smtp.example.com
from = Customer performance reporting tool <reporting@example.com>
to = boss@example.com
subject=Unhappy customers in {date}
sql = select name,happiness from customer where happiness < 0 order by happiness
[database]
host=localhost
port=4321
dbname=corporation
user=read_only_user
password=kissa13