Generate HTML reports from arbitary PostgreSQL queries
mail_sql_report
Only organizations can restrict machine users to read-only access. Personal repositories always grant collaborators read/write access. Machine user keys, like deploy keys, are usually not protected by a passphrase. Run the ssh-keygen procedure on your server and attach the public key to the machine user account.
| #!/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 |
| importdatetime |
| importsmtplib |
| fromsubprocessimportcheck_output |
| fromemail.mime.multipartimportMIMEMultipart |
| fromemail.mime.textimportMIMEText |
| importsys |
| importConfigParser |
| fromstringimportFormatter |
| formatter=Formatter() |
| configParser=ConfigParser.RawConfigParser() |
| configParser.read(sys.argv[1]) |
| defconf(key): |
| returnconfigParser.get('global',key) |
| defformat_key(key): |
| returnformatter.vformat(conf(key),sys.argv,user_fields) |
| defto_list(): |
| # FIXME: If email recipient name contains comma, even inside |
| # quoted section, it breaks this. |
| returnmap(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() |
Add Ssh Key To Github
unhappy_customers.conf
Generate Read Only Github Key Windows 10
| [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 |
Sign up for freeto join this conversation on GitHub. Already have an account? Sign in to comment