Last active
May 2, 2016 03:00
-
-
Save crypticmind/88d951fb83cda6e963bdd7361e12e8a9 to your computer and use it in GitHub Desktop.
Read from MySQL (cmd. line), combine with HTTP JSON data (w/caching), output via template
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [client] | |
| host=localhost | |
| database=test_report | |
| user=test_report | |
| password=test_report |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env python | |
| from reporting import run_query, Cache, http_get_json, http_get_json_simple | |
| import httplib | |
| import json | |
| data = run_query("db.cnf", ''' | |
| SELECT a, b | |
| FROM test_data; | |
| ''') | |
| user_info_cache = Cache("user_info") | |
| conn = httplib.HTTPConnection('jsonplaceholder.typicode.com') | |
| def add_user_name(row): | |
| def fetch_user_name(): | |
| return http_get_json(conn, '/users/%s' % str(row[1]))['name'] | |
| return row + [user_info_cache.get_or_else(row[1], fetch_user_name, 5)] | |
| report = map(add_user_name, data) | |
| def output(row): | |
| print(row) | |
| map(output, report) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import subprocess | |
| import urllib2 | |
| import sqlite3 | |
| import pickle | |
| import httplib | |
| import urllib2 | |
| import json | |
| mysql_bin = '/opt/local/lib/mysql56/bin/mysql' | |
| def run_query(dbconf, query): | |
| """Run a SQL query and return a list of lists of values.""" | |
| proc_output = subprocess.check_output([ | |
| mysql_bin, | |
| '--defaults-extra-file=%s' % dbconf, | |
| '--raw', | |
| '--batch', | |
| '--skip-column-names', | |
| '-e', | |
| query | |
| ]).split("\n")[:-1] | |
| return map(lambda line: line.split("\t"), proc_output) | |
| class Cache: | |
| def __init__(self, name): | |
| self.name = name | |
| self.dbconn = sqlite3.connect(name + ".db") | |
| c = self.dbconn.cursor() | |
| c.execute("CREATE TABLE IF NOT EXISTS cache (name TEXT, value BLOB, created TEXT, ttl INTEGER, PRIMARY KEY(name))") | |
| self.dbconn.commit() | |
| def get(self, key): | |
| c = self.dbconn.cursor() | |
| c.execute("SELECT value FROM cache WHERE name = ? AND datetime(created, ttl || ' seconds') > datetime('now')", (str(key),)) | |
| row = c.fetchone() | |
| if row: | |
| return pickle.loads(row[0]) | |
| else: | |
| return None | |
| def get_or_else(self, key, f, ttl_secs): | |
| value_from_cache = self.get(key) | |
| if value_from_cache: | |
| return value_from_cache | |
| else: | |
| try: | |
| value_from_f = f() | |
| self.put(key, value_from_f, ttl_secs) | |
| return value_from_f | |
| except: | |
| raise | |
| def put(self, key, value, ttl_secs): | |
| c = self.dbconn.cursor() | |
| c.execute("INSERT OR REPLACE INTO cache (name, value, created, ttl) VALUES (?, ?, datetime('now'), ?)", (key, pickle.dumps(value), ttl_secs)) | |
| self.dbconn.commit() | |
| def delete(self, key): | |
| c = self.dbconn.cursor() | |
| c.execute("DELETE FROM cache WHERE name = ?", (str(key),)) | |
| def http_get_json(conn, path): | |
| print("Fetch http://%s:%d%s" % (conn.host, conn.port, path)) | |
| conn.request('GET', path) | |
| json_data = conn.getresponse().read() | |
| return json.loads(json_data) | |
| def http_get_json_simple(url): | |
| print("Fetch %s" % url) | |
| return json.loads(urllib2.urlopen(url).read()) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment