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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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) |
how can I select data from a database in mysql and send them to an HTML page to show in tornado?
Very Help full
Thanks a Lot
Thanks a lot!
This is absolutely amazing and I can’t thank you enough. Well done!