[MediaWiki-commits] [Gerrit] Add support for executing Python code along with SQL for csv... - change (analytics/limn-mobile-data)
Yuvipanda has uploaded a new change for review. https://gerrit.wikimedia.org/r/57835 Change subject: Add support for executing Python code along with SQL for csv gen .. Add support for executing Python code along with SQL for csv gen Contains a general refactor to make things slightly objecty. Everything is now picked up from the conf file, nothing hard coded Also has a python script that provides a graph of user's mobile upload count and general edit count. It needs to be a python script instead of just SQL because it pulls in data from two different databases on two different hosts. Change-Id: If5d3b2f53dac5071738b4fee2162346d64cae409 --- M generate.py M mobile/config.yaml A mobile/uploaders-experience.py 3 files changed, 159 insertions(+), 38 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-mobile-data refs/changes/35/57835/1 diff --git a/generate.py b/generate.py index 020c0bd..8536272 100644 --- a/generate.py +++ b/generate.py @@ -1,51 +1,88 @@ import os import sys -import glob +import csv +import imp + +import yaml import MySQLdb as mysql from jinja2 import Template -import yaml -import csv -conn = mysql.connect( -host=os.environ.get("STAT_HOST", "s1-analytics-slave.eqiad.wmnet"), -port=int(os.environ.get("STAT_PORT", 3306)), -read_default_file='/a/.my.cnf.research', -db="log" -) -#conn = mysql.connect("s1-analytics-slave.eqiad.wmnet", read_default_file=os.path.expanduser('~/.my.cnf.research'), db="log") -def execute(sql): -cur = conn.cursor() -cur.execute(sql) -return cur +class DataGenerator(): +def __init__(self, folder_path): +self.folder_path = folder_path +self.config = yaml.load(open(os.path.join(self.folder_path, "config.yaml"))) +self.connections = {} -def render(template, env): -t = Template(template) -return t.render(**env) +def conn_for(self, name): +if name not in self.config['databases']: +raise ValueError("No such database %s" % name) + +if name not in self.connections: +db = self.config['databases'][name] +self.connections[name] = mysql.connect( +host=db['host'], +port=db['port'], +read_default_file=db['creds_file'], +db=db['db'], +charset='utf8', +use_unicode=True +) + +return self.connections[name] + +def render(self, template): +t = Template(template) +return t.render(**self.config) + +def execute_sql(self, file_name, db_name): +sql = self.render(open(file_name).read()) +conn = self.conn_for(db_name) +cursor = conn.cursor() +cursor.execute(sql) +rows = cursor.fetchall() + +headers = [field[0] for field in cursor.description] + +return (headers, rows) + +def execute_python(self, name, file_path): +module = imp.load_source(name, file_path) +return module.execute(self) + +def execute(self): + +for key, value in self.config['graphs'].iteritems(): +# Look for the sql first, then python +db_name = value.get('db', self.config['defaults']['db']) +headers = None +rows = None +file_path = None +if os.path.exists(os.path.join(folder, key + '.sql')): +file_path = os.path.join(folder, key + '.sql') +headers, rows = self.execute_sql(file_path, db_name) +elif os.path.exists(os.path.join(folder, key + '.py')): +file_path = os.path.join(folder, key + '.py') +headers, rows = self.execute_python(key, file_path) +else: +raise ValueError("Can not find SQL or Python for %s" % key) + + +print "Generating %s (%s)" % (value['title'], file_path) + +csvOutput = open(os.path.join(self.config['output']['path'], key + '.csv'), 'w') +csvOutputWriter = csv.writer(csvOutput) + +csvOutputWriter.writerow(headers) +csvOutputWriter.writerows(rows) + +csvOutput.close() if __name__ == "__main__": if len(sys.argv) != 2: #FIXME: argparse please print "Usage: generate.py " -sys.exit(-1) +sys.exit(1) folder = sys.argv[1] -config = yaml.load(open(os.path.join(folder, "config.yaml"))) -graphs = dict([ -( os.path.basename(filename).split(".")[0], -render(open(filename).read(), config) -) for filename in glob.glob(os.path.join(folder, "*.sql")) -]) -#url_fmt = 'http://stat1001.wikimedia.org/mobile-dashboard/%s' -for key, sql in graphs.items(): -print "Generating %s" % key -cursor = execute(sql) -rows = cursor.fetchall() - -csvOutput = open(os.path.join('/a/limn-public-
[MediaWiki-commits] [Gerrit] Add support for executing Python code along with SQL for csv... - change (analytics/limn-mobile-data)
Yuvipanda has submitted this change and it was merged. Change subject: Add support for executing Python code along with SQL for csv gen .. Add support for executing Python code along with SQL for csv gen Contains a general refactor to make things slightly objecty. Everything is now picked up from the conf file, nothing hard coded Also has a python script that provides a graph of user's mobile upload count and general edit count. It needs to be a python script instead of just SQL because it pulls in data from two different databases on two different hosts. Change-Id: If5d3b2f53dac5071738b4fee2162346d64cae409 --- M generate.py M mobile/config.yaml A mobile/uploaders-experience.py 3 files changed, 186 insertions(+), 40 deletions(-) Approvals: Ori.livneh: Looks good to me, approved Yuvipanda: Verified diff --git a/generate.py b/generate.py index 020c0bd..7aeb743 100644 --- a/generate.py +++ b/generate.py @@ -1,51 +1,113 @@ +#!/usr/bin/env python +# -*- coding: utf-8 -*- +import csv +import imp +import io import os import sys -import glob -import MySQLdb as mysql -from jinja2 import Template + +import MySQLdb +import jinja2 import yaml -import csv -conn = mysql.connect( -host=os.environ.get("STAT_HOST", "s1-analytics-slave.eqiad.wmnet"), -port=int(os.environ.get("STAT_PORT", 3306)), -read_default_file='/a/.my.cnf.research', -db="log" -) -#conn = mysql.connect("s1-analytics-slave.eqiad.wmnet", read_default_file=os.path.expanduser('~/.my.cnf.research'), db="log") -def execute(sql): -cur = conn.cursor() -cur.execute(sql) -return cur +class DataGenerator(object): +"""Executes queries and generates CSV reports based on YAML configs.""" -def render(template, env): -t = Template(template) -return t.render(**env) +def __init__(self, folder_path): +"""Reads configuration 'config.yaml' in `folder_path`.""" +self.folder_path = folder_path +config_file_path = os.path.join(folder_path, 'config.yaml') +with io.open(config_file_path, encoding='utf-8') as config_file: +self.config = yaml.load(config_file) +self.connections = {} + +def make_connection(self, name): +"""Opens a connection to a database using parameters specified in YAML +file for a given name.""" +try: +db = self.config['databases'][name] +except KeyError: +raise ValueError('No such database: "%s"' % name) + +self.connections[name] = MySQLdb.connect( +host=db['host'], +port=db['port'], +read_default_file=db['creds_file'], +db=db['db'], +charset='utf8', +use_unicode=True +) + +def get_connection(self, name): +"""Gets a database connection, specified by its name in the +configuration files. If no connection exists, makes it.""" +if name not in self.connections: +self.make_connection(name) +return self.connections[name] + +def render(self, template): +"""Constructs a SQL query string by interpolating values into a jinja +template.""" +t = jinja2.Template(template) +return t.render(**self.config) + +def execute_sql(self, file_name, db_name): +"""Reads a query from `file_name`, renders it, and executes it against +a database, specified by configuration key. + +Returns a tuple of (headers, rows). +""" +with io.open(file_name, encoding='utf-8') as f: +sql = self.render(f.read()) +conn = self.get_connection(db_name) +cursor = conn.cursor() +try: +cursor.execute(sql) +rows = cursor.fetchall() +headers = [field[0] for field in cursor.description] +finally: +cursor.close() +return (headers, rows) + +def execute_python(self, name, file_path): +"""Does unspeakable evil. Look away!""" +module = imp.load_source(name, file_path) +return module.execute(self) + +def execute(self): +"""Generates a CSV report by executing Python code and SQL queries.""" +global folder + +for key, value in self.config['graphs'].iteritems(): +# Look for the sql first, then python +db_name = value.get('db', self.config['defaults']['db']) + +if os.path.exists(os.path.join(folder, key + '.sql')): +file_path = os.path.join(folder, key + '.sql') +headers, rows = self.execute_sql(file_path, db_name) +elif os.path.exists(os.path.join(folder, key + '.py')): +file_path = os.path.join(folder, key + '.py') +headers, rows = self.execute_python(key, file_path) +else: +raise ValueError("Can not find SQL or Python for %s" % key) + +print "Generating %s (%s)"