April 2016 | Data makes sense.

This is a usage example for the MySQL to HTML table combined with the BI mailer. We will make use of the two previous scripts to easily create a scheduled email sending from a MySQL database through a Gmail address with minimal effort.

The idea behind it was to be able to create a new email with only the base building blocks: the MySQL query for data source, subject/recipients as envelope/destination.

The advantage of this combination is the necessity of only 3(!) lines of code for a new email, allowing you to leverage email as an easy and flexible distribution channel for BI data.

In the example below we also add a greeting along with a relevant subject line, which raises our grand total to 4 easily readable lines of code to send an email from MySQL to Gmail. The connection string I used for the query is an open MySQL database with DNA info (this should go in your sql_to_html file).

	cnx = mysql.connector.connect(user='anonymous', password='',
					host='ensembldb.ensembl.org',port='3306',
					database='drosophila_melanogaster_core_48_43b',
					charset="utf8", use_unicode = True)

cnx = mysql.connector.connect(user='anonymous', password='',

host='ensembldb.ensembl.org',port='3306',

database='drosophila_melanogaster_core_48_43b',

charset="utf8", use_unicode = True)

The email contains a random sample of DNA shortened to 64 characters length. It takes a MySQL query and sends an HTML table via Gmail.

from bimailer import *
from sql_to_html import *
from datetime import datetime

dnaquery = """
	select id, sequence_64 from 
		(select seq_region_id as id, left(sequence,64) as sequence_64 
		from dna 
		order by rand() 
		limit 10) unsorted
	order by 1 asc;
"""

dnamail = Bimail('Daily random fruit fly DNA ' +datetime.now().strftime('%Y/%m/%d'), ['testmailer5432@gmail.com', 'adrian.brudaru@gmail.com'])
dnamail.htmladd('Good morning, find the relevant table below.')
dnamail.htmladd(sql_html(dnaquery))
dnamail.send()

from bimailer import *

from sql_to_html import *

from datetime import datetime

dnaquery = """

select id, sequence_64 from

(select seq_region_id as id, left(sequence,64) as sequence_64

from dna

order by rand()

limit 10) unsorted

order by 1 asc;

"""

dnamail = Bimail('Daily random fruit fly DNA ' +datetime.now().strftime('%Y/%m/%d'), ['testmailer5432@gmail.com', 'adrian.brudaru@gmail.com'])

dnamail.htmladd('Good morning, find the relevant table below.')

dnamail.htmladd(sql_html(dnaquery))

dnamail.send()

Result:

While this example is Gmail specific, it can be easily changed to use any email service. Keep in mind that even when using Gmail you should be able to reach any other email, and if you choose to avoid Gmail altogether, you can just change the bimailer.py to use any other email server.

For easy email reporting, I like to send plain HTML tables. An easy implementation is to take the query output and HTML-ize it.

The script below does just that – it connects to MySQL, sends a query, and turns the result into an HTML table. This table can further be used to be sent with the bimailer, or in any other way.

The MySQL connector for Python can be found on the MySQL official website here.

import mysql.connector

def query_mysql(query):
	cnx = mysql.connector.connect(user='myusername', password='mypass',
								  host='myip',port='myport',
								  database='dwh',charset="utf8", use_unicode = True)
	cursor = cnx.cursor()
	cursor.execute(query)
	#get header and rows
	header = [i[0] for i in cursor.description]
	rows = [list(i) for i in cursor.fetchall()]
	#append header to rows
	rows.insert(0,header)
	cursor.close()
	cnx.close()
	return rows

#take list of lists as argument	
def nlist_to_html(list2d):
	#bold header
	htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'
	list2d[0] = [u'<b>' + i + u'</b>' for i in list2d[0]] 
	for row in list2d:
		newrow = u'<tr>' 
		newrow += u'<td align="left" style="padding:1px 4px">'+unicode(row[0])+u'</td>'
		row.remove(row[0])
		newrow = newrow + ''.join([u'<td align="right" style="padding:1px 4px">' + unicode(x) + u'</td>' for x in row])  
		newrow += '</tr>' 
		htable+= newrow
	htable += '</table>'
	return htable
	


def sql_html(query):
	return nlist_to_html(query_mysql(query))
	
#usage example
#query = "select date, sum(sales) as sum_of_sales from table order by 1 desc limit 10"
#print sql_html(query)

import mysql.connector

def query_mysql(query):

cnx = mysql.connector.connect(user='myusername', password='mypass',

host='myip',port='myport',

database='dwh',charset="utf8", use_unicode = True)

cursor = cnx.cursor()

cursor.execute(query)

#get header and rows

header = [i[0] for i in cursor.description]

rows = [list(i) for i in cursor.fetchall()]

#append header to rows

rows.insert(0,header)

cursor.close()

cnx.close()

return rows

#take list of lists as argument

def nlist_to_html(list2d):

#bold header

htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'