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).
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
This is the best I have found. Thank you for sharing the code.