def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') Date = request.args.get("Date", '') TargetField = request.args.get("TargetField", '') ShortText = request.args.get("ShortText", '') Text = request.args.get("Text", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '100000') sql = "select '%s',%s,%s,%s from %s where %s order by 2 limit %s offset %s" % ( TargetField, Date, ShortText, Text, table, where, limit, start) header = "TargetField,Date,ShortText,Text" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) == 0: return Response(open(datfile, 'r'))
def render(vis, request, info): dbscope = vis.config.get('db_scope', '') if len(dbscope) > 0 and (not 'where' in dbscope.lower()): dbscope = " WHERE " + dbscope if vis.config['db_system'] == 'mysql': sql = "SELECT table_name,column_name FROM information_schema.columns " + dbscope else: sql = "SELECT table_schema || '.' || table_name,column_name FROM information_schema.columns " + dbscope #load always reload = 1 # int(request.args.get("reload", 0)) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, None) table_list = defaultdict(list) reader = csv.DictReader(open(datfile, 'r'), fieldnames=("table", "field")) for row in reader: table_list[row['table']].append(row['field']) module_list = json.loads( open(os.path.realpath('modules/modules.json'), 'r').read()) data = {'modules': module_list, 'tables': table_list} return Response(json.dumps(data))
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", 0)) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", ' count(*) ') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') # 1000 links max pfield = request.args.get("pfield", []) sfield = request.args.get("sfield", []) source = request.args.get("source", '') target = request.args.get("target", '') orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy if len(table) == 0 or not source or not target or not field: info["message"].append("table, source, target, or field missing.") info["message_class"] = "failure" else: if not sfield or len(sfield) == 0: sfield = ' count(*) ' else: sfield = sfield[0] sql = "select %s, %s, %s from (select * from %s where %s)" % (source, target, sfield, table, where,) + \ " as a where %s is not null and %s is not null group by 1,2 %s limit %s offset %s" % ( source, target, orderBy, limit, start) info["title"] = "Interactions between %s and %s as %s in %s" % ( source, target, field, table) header = "source,target,value" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile pfield = request.args.get("pfield", []) info[ "title"] = "SOURCE: <em>%s</em>, <br />TARGET: <em>%s</em>, on <br />LINK: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( source, target, pfield[0], table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", 0)) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') sfield = request.args.get("sfield", []) pfield = request.args.get("pfield", []) if len(sfield) == 1: sfield.append(' count(*) ') pfield.append(' count(*) ') if len(table) == 0 or len(field) < 1: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select %s as n from %s where %s group by 1 order by n desc limit %s offset %s"\ % (field, table, where, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile json_file = datfile.replace('csv', 'json') if reload > 0 or (not os.path.exists(os.path.realpath(json_file))): # csv to json conversion try: reader = csv.DictReader(open(datfile, 'r'), fieldnames=("name", "size")) out = [obj for obj in reader if len(obj['name']) > 0] with open(json_file, 'w') as jf: json.dump({"name": 'flare', "children": out}, jf) except: info["message"].append("Couldn't find CSV file") info["message_class"] = "failure" info["datfile"] = json_file info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (', '.join(pfield[:2]), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", "") field = request.args.get("field", "") where = request.args.get("where", "1=1") reload = int(request.args.get("reload", 0)) view = request.args.get("view", "") start = request.args.get("start", "0") # start at 0 limit = request.args.get("limit", "5000") # max 5000 data points groupBy = request.args.get("groupBy", "") if groupBy and len(groupBy) > 0: groupBy = " group by %s " % groupBy xField = request.args.get("xField", "") pfield = request.args.get("pfield", []) sfield = request.args.get("sfield", []) if len(table) == 0 or len(xField) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" elif len(sfield) < 2: info["message"].append("Need at least two fields.") info["message_class"] = "failure" else: sql = "select %s, %s from %s where %s %s order by 1 limit %s offset %s" % ( xField, ",".join(sfield[:2]), table, where, groupBy, limit, start, ) header = "Date,A,B" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["title"] = "Diff of <br />FIELD: <em>%s</em> on <br />FIELD: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( pfield[1], pfield[0], table, ) info["title"] = Markup(info["title"]) info["message"] = Markup("".join("<p>%s</p>" % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') xField = request.args.get("xField", '') sfield = request.args.get("sfield", '') pfield = request.args.get("pfield", []) if len(table) == 0 or not xField: info["message"].append("Table or xfield missing.") info["message_class"] = "failure" elif len(sfield) != 2: info["message"].append( "Need two fields : a field to group by, and another aggregate field." ) info["message_class"] = "failure" else: sql = "select t, %s, n from ( \ select *,row_number() over (partition by 1,2 order by 3 desc) as rank from \ (select %s as t, %s, %s as n from %s where %s group by 1,2) \ as a) as a where rank >= %s and rank <=%s + %s" \ % (sfield[0], xField, sfield[0], sfield[1], table, where, start, start, limit) # sql = "select %s, %s from %s where %s group by 1,2 order by 1 limit %s offset %s"\ # % (xField, field, table, where, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") datfilen = datfile + '_2mat.csv' info["datfile"] = datfilen (startYear, endYear) = _array2mat(datfile, datfilen) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) return vis.render_template('explore_mashed_series.html', **info)
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", 0)) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", ' count(*) ') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') # 1000 links max pfield = request.args.get("pfield", []) sfield = request.args.get("sfield", []) source = request.args.get("source", '') target = request.args.get("target", '') orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy if len(table) == 0 or not source or not target or not field: info["message"].append("table, source, target, or field missing.") info["message_class"] = "failure" else: if not sfield or len(sfield) == 0: sfield = ' count(*) ' else: sfield = sfield[0] sql = "select %s, %s, %s from (select * from %s where %s)" % (source, target, sfield, table, where,) + \ " as a where %s is not null and %s is not null group by 1,2 %s limit %s offset %s" % ( source, target, orderBy, limit, start) info["title"] = "Interactions between %s and %s as %s in %s" % (source, target, field, table) header = "source,target,value" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile pfield = request.args.get("pfield", []) info[ "title"] = "SOURCE: <em>%s</em>, <br />TARGET: <em>%s</em>, on <br />LINK: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( source, target, pfield[0], table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy sfield = request.args.get("sfield", []) pfield = request.args.get("pfield", []) if len(table) == 0 or not field or len(sfield) < 2: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: if len(sfield) > 5: info["message"].append("Too many fields. Only first 5 are used.") sfield = sfield[:5] pfield = pfield[:5] info["fieldValue"] = pfield[0] info["title"] = "FIELD_1: <em>%s</em> against <br />OTHER FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (pfield[0], ', '.join(pfield[1:]), table) sql = "select %s from %s where %s %s %s limit %s offset %s" \ % (','.join(sfield), table, where, groupBy, orderBy, limit, start) header = "labels,%s" % (','.join(pfield[1:])) (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy sfield = request.args.get("sfield", []) pfield = request.args.get("pfield", []) if len(table) == 0 or not field or len(sfield) < 2: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: if len(sfield) > 5: info["message"].append("Too many fields. Only first 5 are used.") sfield = sfield[:5] pfield = pfield[:5] info["fieldValue"] = pfield[0] info["title"] = "FIELD_1: <em>%s</em> against <br />OTHER FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (pfield[0], ', '.join(pfield[1:]), table) sql = "select %s from %s where %s %s %s limit %s offset %s" \ % (','.join(sfield), table, where, groupBy, orderBy, limit, start) header = "labels,%s" % (','.join(pfield[1:])) (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] reload = request.args.get("reload", 0) table = request.args.get("table", '') where = request.args.get("where", '1=1') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '10000') # module dependent user inputs field = request.args.get("field", '') pfield = request.args.get("pfield", []) groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy if len(table) == 0 or not field: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select row_number() over (order by 1) as rnum, * from (select %s from %s where %s %s %s limit %s offset %s) as a" % ( field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % (pfield[d], d) for d in range(len(pfield)) ] info['divs'] = Markup(''.join(divs)) info["fieldY"] = pfield[0] if len(pfield) > 0 else "Y" info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( ', '.join(pfield), table) info["title"] = Markup(info["title"])
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", 0)) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') sfield = request.args.get("sfield", []) pfield = request.args.get("pfield", []) if len(sfield) == 1: sfield.append(' count(*) ') pfield.append(' count(*) ') if len(table) == 0 or len(field) < 1: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select %s as n from %s where %s group by 1 order by n desc limit %s offset %s"\ % (field, table, where, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile json_file = datfile.replace('csv', 'json') if reload > 0 or (not os.path.exists(os.path.realpath(json_file))): # csv to json conversion try: reader = csv.DictReader(open(datfile, 'r'), fieldnames=( "name", "size" )) out = [obj for obj in reader if len(obj['name']) > 0] with open(json_file, 'w') as jf: json.dump({"name": 'flare', "children": out}, jf) except: info["message"].append("Couldn't find CSV file") info["message_class"] = "failure" info["datfile"] = json_file info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (', '.join(pfield[:2]), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') # 10 years max start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') field = request.args.get("field", "") orderBy = request.args.get("orderBy", '') groupBy = request.args.get("groupBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s' % orderBy if groupBy and len(groupBy) > 0: groupBy = ' group by %s' % groupBy # verify essential parameter details - smell test if len(table) == 0 or len(field) == 0: info["message"].append("Table or Field missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start) header = None (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view, True) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( ', '.join(pfield), table) info["title"] = Markup(info["title"]) # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') # 10 years max start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') field = request.args.get("field", "") orderBy = request.args.get("orderBy", '') groupBy = request.args.get("groupBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s' % orderBy if groupBy and len(groupBy) > 0: groupBy = ' group by %s' % groupBy # verify essential parameter details - smell test if len(table) == 0 or len(field) == 0: info["message"].append("Table or Field missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start) header = None (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view, True) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (', '.join(pfield), table) info["title"] = Markup(info["title"]) # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') xField = request.args.get("xField", '') sfield = request.args.get("sfield", '') pfield = request.args.get("pfield", []) if len(table) == 0 or not xField: info["message"].append("Table or xfield missing.") info["message_class"] = "failure" elif len(sfield) != 2: info["message"].append("Need two fields : a field to group by, and another aggregate field.") info["message_class"] = "failure" else: sql = "select t, %s, n from ( \ select *,row_number() over (partition by 1,2 order by 3 desc) as rank from \ (select %s as t, %s, %s as n from %s where %s group by 1,2) \ as a) as a where rank >= %s and rank <=%s + %s" \ % (sfield[0], xField, sfield[0], sfield[1], table, where, start, start, limit) # sql = "select %s, %s from %s where %s group by 1,2 order by 1 limit %s offset %s"\ # % (xField, field, table, where, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") datfilen = datfile + '_2mat.csv' info["datfile"] = datfilen (startYear, endYear) = _array2mat(datfile, datfilen) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>"\ % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) return vis.render_template('explore_mashed_series.html', **info)
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '3650') # 10 years max start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs xField = request.args.get("xField", '') field = request.args.get("field", " count(*) ") # verify essential parameter details - smell test if len(table) == 0 or not xField: info["message"].append("Table or xField missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s, %s from %s where %s group by 1 order by 1 limit %s offset %s" % ( xField, field, table, where, limit, start) header = "Date,Field" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> along <br />xFIELD: <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (', '.join(pfield), xField, table) info["title"] = Markup(info["title"]) # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '3650') # 10 years max start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs xField = request.args.get("xField", '') field = request.args.get("field", " count(*) ") # verify essential parameter details - smell test if len(table) == 0 or not xField: info["message"].append("Table or xField missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s, %s from %s where %s group by 1 order by 1 limit %s offset %s" % ( xField, field, table, where, limit, start) header = "Date,Field" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> along <br />xFIELD: <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (', '.join(pfield), xField, table) info["title"] = Markup(info["title"]) # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] reload = request.args.get("reload", 0) table = request.args.get("table", '') where = request.args.get("where", '1=1') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '10000') # module dependent user inputs field = request.args.get("field", '') pfield = request.args.get("pfield", []) groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy if len(table) == 0 or not field: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select row_number() over (order by 1) as rnum, * from (select %s from %s where %s %s %s limit %s offset %s) as a" % ( field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % ( pfield[d], d) for d in range(len(pfield))] info['divs'] = Markup(''.join(divs)) info["fieldY"] = pfield[0] if len(pfield) > 0 else "Y" info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (', '.join(pfield), table) info["title"] = Markup(info["title"])
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '5000') # max 5000 data points groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy xField = request.args.get("xField", '') pfield = request.args.get("pfield", []) sfield = request.args.get("sfield", []) if len(table) == 0 or len(xField) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" elif len(sfield) < 2: info["message"].append("Need at least two fields.") info["message_class"] = "failure" else: sql = "select %s, %s from %s where %s %s order by 1 limit %s offset %s" \ % (xField, ','.join(sfield[:2]), table, where, groupBy, limit, start) header = "Date,A,B" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["title"] = "Diff of <br />FIELD: <em>%s</em> on <br />FIELD: <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (pfield[1], pfield[0], table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", "") field = request.args.get("field", "") where = request.args.get("where", "1=1") reload = int(request.args.get("reload", 0)) view = request.args.get("view", "") limit = request.args.get("limit", "100") # 100 entries max start = request.args.get("start", "0") # start at 0 groupBy = request.args.get("groupBy", "") if groupBy and len(groupBy) > 0: groupBy = " group by %s " % groupBy orderBy = request.args.get("orderBy", "1") orderBy = " order by %s " % orderBy if len(table) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start, ) (datfile, reload, result) = export_sql(sql, vis.config, reload, header=None, view=view, addHeader=True) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["message"] = Markup("".join("<p>%s</p>" % m for m in info["message"] if len(m) > 0)) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (", ".join(pfield), table) info["title"] = Markup(info["title"])
def render(vis, request, info): info["message"] = [] reload = request.args.get("reload", 0) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 xField = request.args.get("xField", '') limit = request.args.get("limit", '20') if len(table) == 0 or len(field) == 0: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select t, word, n from ( select *,row_number() over (partition by t order by n desc) as rank from ( select word, t,n from ( select word, t, count(*) as n from (select regexp_split_to_table(regexp_replace(lower(coalesce(%s,'')),'[^a-z0-9@]+',' ','g'),' ') as word, %s as t from %s where %s) as a where char_length(word) > 0 group by 1,2 ) as a where n > 5 ) as a ) as a where rank >= %s and rank <= %s + %s" % ( field, xField, table, where, start, start, limit) header = None (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") datfilen = datfile + '_2mat.csv' info["datfile"] = datfilen datfilen = datfile + '_2mat.csv' info["datfile"] = datfilen (startYear, endYear) = _array2mat(datfile, datfilen) print(startYear, endYear) pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) return vis.render_template('explore_series.html', **info)
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '5000') pfield = request.args.get("pfield", []) xField = request.args.get("xField", '') annotate = request.args.get("annotate", '') groupby = request.args.get("groupBy", '') if groupby and len(groupby) > 0: groupby = ' group by %s' % groupby if len(table) == 0 or len(xField) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: annotate = ',%s' % annotate if len(annotate) > 0 else '' sql = "select %s, %s %s from %s where %s %s order by 1 limit %s offset %s" % ( xField, field, annotate, table, where, groupby, limit, start) annotate = ',annotate' if len(annotate) > 0 else '' header = "Date,%s%s" % (','.join(pfield), annotate) (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["title"] = "%s from %s" % (','.join(pfield), table)
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '100000') xField = request.args.get("xField", '') groupby = request.args.get("groupBy", '') if groupby and len(groupby) > 0: groupby = ' group by ' + groupby if len(table) == 0 or len(xField) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: sql = "select %s, %s from %s where %s %s order by 1 limit %s offset %s" % ( xField, field, table, where, groupby, limit, start) field = ','.join([re.compile(r' as ').split(f)[-1].strip() for f in field.split(',')]) header = "Date,%s" % (field) (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '5000') pfield = request.args.get("pfield", []) xField = request.args.get("xField", '') annotate = request.args.get("annotate", '') groupby = request.args.get("groupBy", '') if groupby and len(groupby) > 0: groupby = ' group by %s' % groupby if len(table) == 0 or len(xField) == 0 or len(field) == 0: info["message"].append("Table or field missing.") info["message_class"] = "failure" else: annotate = ',%s' % annotate if len(annotate) > 0 else '' sql = "select %s, %s %s from %s where %s %s order by 1 limit %s offset %s" % ( xField, field, annotate, table, where, groupby, limit, start) annotate = ',annotate' if len(annotate) > 0 else '' header = "Date,%s%s" % (','.join(pfield), annotate) (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["title"] = "%s from %s" % (','.join(pfield), table)
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", '0')) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '100') if len(table) == 0 or not field: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select %s from %s where %s group by 1 order by 1 desc limit %s offset %s" % ( field, table, where, limit, start) header = "letter,frequency" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile pfield = request.args.get("pfield", []) info["fieldY"] = pfield[1] if len(pfield) > 1 else "Y" info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( ', '.join(pfield), table) info["title"] = Markup(info["title"])
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", '0')) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '1000') groupby = field pfield = request.args.get("pfield", []) if len(table) == 0 or not field: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select %s, count(*) as n from %s where %s group by %s order by n desc limit %s offset %s"\ % (field, table, where, groupby, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (', '.join(pfield[:2]), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", 0)) sql = "select table_catalog, table_schema, table_name, column_name, data_type " \ + "FROM information_schema.columns c left join pg_class p on c.table_name = p.relname " \ + "where not (table_schema like '\%pg_\%' or table_schema like '\%gp\%' or table_schema like '\%schema\%') " (datfile, reload, result) = export_sql(sql, vis.config, reload, None, None, addHeader=True) json_file = datfile.replace('csv', 'json') if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") keys = ['table_schema', 'table_name', 'column_name'] keys.reverse() jout = nest_array(csv.DictReader(open(datfile)), keys) with open(json_file, 'w') as jf: json.dump([{'key':'DB', '_values': jout}], jf) else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = json_file info["title"] = "DB CONTENT" info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): dbscope = vis.config.get('db_scope', '') if len(dbscope) > 0 and (not 'where' in dbscope.lower()): dbscope = " WHERE " + dbscope if vis.config['db_system'] == 'mysql': sql = "SELECT table_name,column_name FROM information_schema.columns " + dbscope else: sql = "SELECT table_schema || '.' || table_name,column_name FROM information_schema.columns " + dbscope #load always reload = 1 # int(request.args.get("reload", 0)) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, None) table_list = defaultdict(list) reader = csv.DictReader(open(datfile, 'r'), fieldnames=( "table", "field" )) for row in reader: table_list[row['table']].append(row['field']) module_list = json.loads(open(os.path.realpath('modules/modules.json'), 'r').read()) data = {'modules': module_list, 'tables': table_list} return Response(json.dumps(data))
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') # 10 years max if limit: limit = ' limit %s' % limit start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs latitude = request.args.get("latitude", '') longitude = request.args.get("longitude", '') field = request.args.get("field", '') mapField = request.args.get("mapField", " '1' ") pfield = request.args.get("pfield", []) groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s ' % groupBy orderBy = request.args.get("orderBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy # verify essential parameter details - smell test if len(table) == 0 or len(latitude) == 0 or len(longitude) == 0: info["message"].append("Table or latitude or longitude missing") info["message_class"] = "failure" else: # prepare sql query sql = "select row_number() over (order by 1,2) as rnum, * from (select %s,%s,%s,%s from %s where %s %s %s %s offset %s) as a" % ( latitude, longitude, mapField, field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info[ "title"] = "LATITUDE: <em>%s</em> and <br />LONGITUDE:<em>%s</em> and <br />FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( latitude, longitude, ','.join(pfield), table) info["title"] = Markup(info["title"]) divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % ( pfield[d], d) for d in range(len(pfield))] info['divs'] = Markup(''.join(divs)) info["fieldY"] = pfield[0] if len(pfield) > 0 else "Y" # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '5000') # 10 years max start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') limit = ' limit %s' % limit # module dependent user inputs latitude = request.args.get("latitude", '') longitude = request.args.get("longitude", '') field = request.args.get("field", ' count(*) ') xField = request.args.get("xField", "'1'") # verify essential parameter details - smell test if len(table) == 0 or len(latitude) == 0 or len(longitude) == 0: info["message"].append("Table or latitude or longitude missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s, %s, %s, %s from %s where %s group by 1,2,3 order by 1 %s offset %s" % ( xField, latitude, longitude, field, table, where, limit, start) header = "t,latitude,longitude,count" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages field = ','.join([re.compile(r' as ').split(f)[-1].strip() for f in field.split(',')]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info[ "title"] = "FIELD: <em>%s</em> against <br />LATITUDE: <em>%s</em> and <br />LONGITUDE:<em>%s</em> along <br />xFIELD: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( field, latitude, longitude, xField, table) info["title"] = Markup(info["title"]) if xField != "'1'": info["div_slider_counter"] = Markup('<p>Slider is at <span id="slider-time"></span></p>') info["div_slider"] = Markup( '<div id="slider-container" style="padding: 10px;"> <input type="hidden" value="40" id="slider" /></div>') # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', ''))
def render(vis, request, info): info["message"] = [] # user parameters table = request.args.get("table", '') field = request.args.get("field", '') where = request.args.get("where", '1=1') reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') limit = request.args.get("limit", '1000') start = request.args.get("start", '0') groupby = request.args.get("groupBy", '') if groupby and len(groupby) > 0: groupby = ' group by %s' % groupby orderBy = request.args.get("orderBy", '1') if orderBy and len(orderBy) > 0: orderBy = ' order by %s ' % orderBy pfield = request.args.get("pfield", []) # fields split into an array sfield = request.args.get("sfield", []) # field captions split into an array if len(table) == 0 or not field: info["message"].append("Table or field missing.") info["message_class"] = "failure" elif len(sfield) < 2: info["message"].append("Not enough fields.") info["message_class"] = "failure" else: if len(sfield) > 4: info["message"].append("Too many fields. Only first 4 are used.") sfield = sfield[:4] info["xlabel"] = pfield[0] info["ylabel"] = pfield[1] # if z,c are not provided sfield.extend(['1'] * (4 - len(sfield))) pfield.extend(['1'] * (4 - len(pfield))) info["field3"] = pfield[3 - 1] info["field4"] = pfield[4 - 1] sql = "select %s from %s where %s %s %s limit %s offset %s" \ % (','.join(sfield), table, where, groupby, orderBy, limit, start) header = "x,y,z,c" (datfile, reload, result) = export_sql(sql, vis.config, reload, header, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = datfile pfield = request.args.get("pfield", []) info["title"] = "FIELD_X: <em>%s</em>, <br />FIELD_Y: <em>%s</em>, <br />FIELD_Z(size): <em>%s</em>, <br />FIELD_C(color): <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (pfield[0], pfield[1], pfield[2], pfield[3], table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join(['<p>%s</p>' % m for m in info["message"] if len(m) > 0]))
def render(vis, request, info): info["message"] = [] reload = int(request.args.get("reload", '0')) table = request.args.get("table", '') where = request.args.get("where", '1=1') field = request.args.get("field", '') view = request.args.get("view", '') minlen = request.args.get("MinCharLength", '3') rStopWords = int(request.args.get("RemoveStopWords", '0')) StemWords = int(request.args.get("StemWords", '0')) start = request.args.get("start", '0') # start at 0 limit = request.args.get("limit", '200') if len(table) == 0 or len(field) == 0: info["message"].append("table or field missing.") info["message_class"] = "failure" else: sql = "select word, count(*) as n from (select regexp_split_to_table(regexp_replace(lower(coalesce(%s,'')),'[^a-z0-9@]+',' ','g'),' ') as word, * from %s where %s) as a where char_length(word) > %s group by 1 order by 2 desc limit %s offset %s" % ( field, table, where, minlen, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") datfileNew = datfile + 'edited.csv' if reload: with open(datfile) as f: terms = csv.reader(f) #terms = map(lambda term: term(0).replace("'s",'').replace("'", '').replace(".", " ").replace(",", " "), terms) if rStopWords: terms = filter(lambda term: term[0] not in STOPWORDS, terms) if StemWords: p = PorterStemmer() d = defaultdict(int) for term in terms: d[p.stem(term[0], 0, len(term[0]) - 1)] += int(term[1]) terms = d.items() header = ["text", "size"] with open(datfileNew, 'w') as f2: cs = csv.writer(f2) cs.writerow(header) for term in terms: cs.writerow(term) info["datfile"] = datfileNew pfield = request.args.get("pfield", []) info["title"] = "FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" \ % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0))
def render(vis, request, info): info["message"] = [] info["results"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs field = request.args.get("field", '') pre_process = request.args.get("pre_process", '') pre_transform = request.args.get("pre_transform", '') orderBy = request.args.get("orderBy", '') groupBy = request.args.get("groupBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s' % orderBy if groupBy and len(groupBy) > 0: groupBy = ' group by %s' % groupBy k = int(request.args.get("k", 2)) pfield = request.args.get("pfield", []) # verify essential parameter details - smell test if len(table) == 0 or len(field) == 0: info["message"].append("Table or field missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: X = [] with open(datfile, 'r') as f: for r in f: row = r.rstrip().split(',') X.append([float(r) for r in row]) xfield = pfield # transform features x2fs(X, xfield, pre_transform) pfield = xfield X = numpy.array(X) if pre_process == "Z-Score": X = stats.zscore(X, axis=0) elif pre_process == "PCA": pca = PCA() (X, V) = fit_transform(pca, X) pfield = ['PCA_%d' % (d + 1) for d in range(len(pfield))] elif pre_process == "Whitened PCA": pca = PCA(whiten=True) (X, V) = fit_transform(pca, X) pfield = ['PCA_%d' % (d + 1) for d in range(len(pfield))] clust = cluster.KMeans(n_clusters=k) cidx = clust.fit_predict(X) cdists = clust.transform(X) # summary results results, clustmeans = evaluate(cdists, cidx, X) info["results"].append( 'Clustering the data using K-means with k=%d' % k) info["results"].append('Average distance to centroid: %.4f' % results['meandist']) hashquery = datfile + hex( hash(request.args.get('query', datfile)) & 0xffffffff) if pre_process == "PCA" or pre_process == "Whitened PCA": #write pca matrix file info["datfile_matrix"] = hashquery + '.pca.csv' with open(info["datfile_matrix"], 'w') as f: f.write("feature,%s\n" % (','.join(xfield))) for i in range(len(V)): f.write('PCA_%d,%s\n' % (i + 1, ','.join([str(v) for v in V[i]]))) info["pca_matrix_divs"] = Markup( '<h2>PCA Components</h2><div id="svg-pca_matrix"></div>') else: info["pca_matrix_divs"] = '' # preparing within cluster distances into a js array f = [] for i in range(k): f.append('{cluster:"%d", distance:%.3f}' % (i, clustmeans[i])) info["clust_data"] = Markup('clust_data=[' + ','.join(f) + '];') #provenance #0:id,1:prediction result (grouping),2:actual label(shape),3:error,4:y,or features info["datfile_provenance"] = hashquery + '.provenance.csv' RES = ['Cluster %d' % (i + 1) for i in range(k)] with open(info["datfile_provenance"], 'w') as f: f.write('Cluster,Error,%s\n' % (','.join(pfield))) for i in range(len(cidx)): e = cdists[i][cidx[i]] f.write('%s,%.4f,%s\n' % (RES[cidx[i]], e, ','.join([str(r) for r in X[i]]))) pfield = ['cluster'] + pfield divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % (pfield[d], d + 1) for d in range(len(pfield)) ] divs = ''.join(divs) divs = '<div class="chart"><div class="title">Distance to Centroid (<span id="active"></span> of <span id="total"></span> items selected.)<a href="javascript:reset(0)" class="reset" style="display: none;">reset</a></div></div>' + divs info['provenance_divs'] = Markup(divs) info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append( "Loading from cache. Use reload=1 to reload.") info["datfile"] = info["datfile_provenance"] # prepare some messages info["title"] = "FIELD_X: <em>%s</em> from <br />TABLE: <em>%s</em>" % ( ','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["results"] = Markup('<ul>' + ''.join('<li>%s</li>' % m for m in info["results"] if len(m) > 0) + '</ul>') # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', '')) t = vis.jinja_env.get_template('explore.html') v1 = t.render(**info) t = vis.jinja_env.get_template('ml_kmeans.html') v2 = t.render(**info) v3 = v1[:-7] + v2 + v1[-7:] + '</html>' return Response(v3, mimetype='text/html')
def render(vis, request, info): info["message"] = [] info["results"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs field = request.args.get("field", '') ratio = float(request.args.get("ratio", 0.9)) regularizer = float(request.args.get("regularizer", 1)) pre_process = request.args.get("pre_process", '') pre_transform = request.args.get("pre_transform", '') orderBy = request.args.get("orderBy", '') groupBy = request.args.get("groupBy", '') if groupBy and len(groupBy) > 0: groupBy = ' group by %s' % groupBy pfield = request.args.get("pfield", []) # verify essential parameter details - smell test if len(table) == 0 or len(field) == 0: info["message"].append("Table or field missing") info["message_class"] = "failure" else: # prepare sql query if orderBy and len(orderBy) > 0: orderbyMessage = ' ordered by %s' % orderBy orderBy = ' order by %s' % orderBy else: orderBy = '' orderbyMessage = 'ordered randomly' sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: X = [] Y = [] with open(datfile, 'r') as f: for r in f: row = r.rstrip().split(',') Y.append(int(row[0])) X.append([float(r) for r in row[1:]]) if ratio == 1: TrainingSize = 0 else: TrainingSize = int(ratio * len(X)) xfield = pfield[1:] # transform features x2fs(X, xfield, pre_transform) pfield = [pfield[0]] + xfield X = numpy.array(X) if pre_process == "Z-Score": X = stats.zscore(X, axis=0) elif pre_process == "PCA": pca = PCA() (X, V) = fit_transform(pca, X) pfield = [pfield[0]] + ['PCA_%d' % (d + 1) for d in range(len(pfield[1:]))] elif pre_process == "Whitened PCA": pca = PCA(whiten=True) (X, V) = fit_transform(pca, X) pfield = [pfield[0]] + ['PCA_%d' % (d + 1) for d in range(len(pfield[1:]))] clf = svm.SVC(C=regularizer, kernel='linear', probability=True, random_state=0, verbose=False) clf.fit(X[1:TrainingSize], Y[1:TrainingSize]) yhat = clf.decision_function(X[TrainingSize:]) yhat = [yhat[i][0] for i in range(len(yhat))] # summary results results = evaluate(yhat, Y[TrainingSize:]) info["results"].append( 'Predicting %s using %2.2f %% of the data %s' % (pfield[0], ratio * 100, orderbyMessage)) info["results"].append('Number of samples: %d' % len(X)) info["results"].append('Number of features: %d' % len(X[0])) info["results"].append('Accuracy: %.4f' % results['accuracy']) info["results"].append('Precision: %.4f' % results['precision']) info["results"].append('Recall: %.4f' % results['recall']) info["results"].append('F1: %.4f' % results['f1']) hashquery = datfile + hex(hash(request.args.get('query', datfile)) & 0xffffffff) if pre_process == "PCA" or pre_process == "Whitened PCA": #write pca matrix file info["datfile_matrix"] = hashquery + '.pca.csv' with open(info["datfile_matrix"], 'w') as f: f.write("feature,%s\n" % (','.join(xfield))) for i in range(len(V)): f.write('PCA_%d,%s\n' % (i + 1, ','.join([str(v) for v in V[i]]))) info["pca_matrix_divs"] = Markup('<div id="svg-pca_matrix"></div>') else: info["pca_matrix_divs"] = '' # preparing weights into a js array f = [] f.append('{feature:"intercept", weight:%.3f}' % clf.intercept_[0]) for i in range(len(clf.coef_[0])): f.append('{feature:"%s", weight:%.3f}' % (pfield[i + 1], clf.coef_[0][i])) info["weights_data"] = Markup('weights_data=[' + ','.join(f) + '];') #provenance #0:id,1:prediction result (grouping),2:actual label(shape),3:error,4:y,or features info["datfile_provenance"] = hashquery + '.provenance.csv' RES = ['Correct', 'Mistake'] with open(info["datfile_provenance"], 'w') as f: f.write('Error,Result,%s\n' % (','.join(pfield))) for i in range(len(yhat)): e = -yhat[i] * Y[i + TrainingSize] f.write('%.4f,%s,%f,%s\n' % (e, RES[e >= 0], Y[i + TrainingSize], ','.join([str(r) for r in X[i + TrainingSize]]))) divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % ( pfield[d], d + 1) for d in range(len(pfield))] divs = ''.join(divs) divs = '<div class="chart"><div class="title">Distance from hyperplane (<span id="active"></span> of <span id="total"></span> items selected.)<a href="javascript:reset(0)" class="reset" style="display: none;">reset</a></div></div>' + divs info['provenance_divs'] = Markup(divs) # auroc fpr, tpr, thresholds = metrics.roc_curve(Y[TrainingSize:], yhat, pos_label=1) info["datfile_auroc"] = hashquery + '.auroc.csv' with open(info["datfile_auroc"], 'w') as f: f.write('fpr,tpr\n') for i in range(len(fpr)): f.write('%f,%f\n' % (fpr[i], tpr[i])) auroc = metrics.auc(fpr, tpr) info["auroc_score"] = '%.4f' % auroc info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info[ "title"] = "FIELD_Y: <em>%s</em>, on <br />FIELD_X(predictors): <em>%s</em> from <br />TABLE: <em>%s</em>" % ( pfield[0], ','.join(xfield), table) info["title"] = Markup(info["title"]) info["datfile"] = info["datfile_provenance"] # prepare some messages info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["results"] = Markup('<ul>' + ''.join('<li>%s</li>' % m for m in info["results"] if len(m) > 0) + '</ul>') # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', '')) t = vis.jinja_env.get_template('explore.html') v1 = t.render(**info) t = vis.jinja_env.get_template('ml_svm_linear.html') v2 = t.render(**info) v3 = v1[:-7] + v2 + v1[-7:] + '</html>' return Response(v3, mimetype='text/html')
def render(vis, request, info): info["message"] = [] info["results"] = [] # module independent user inputs table = request.args.get("table", '') where = request.args.get("where", '1=1') limit = request.args.get("limit", '1000') start = request.args.get("start", '0') # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", '') # module dependent user inputs field = request.args.get("field", '') pre_process = request.args.get("pre_process", '') pre_transform = request.args.get("pre_transform", '') orderBy = request.args.get("orderBy", '') groupBy = request.args.get("groupBy", '') if orderBy and len(orderBy) > 0: orderBy = ' order by %s' % orderBy if groupBy and len(groupBy) > 0: groupBy = ' group by %s' % groupBy k = int(request.args.get("k", 2)) pfield = request.args.get("pfield", []) # verify essential parameter details - smell test if len(table) == 0 or len(field) == 0: info["message"].append("Table or field missing") info["message_class"] = "failure" else: # prepare sql query sql = "select %s from %s where %s %s %s limit %s offset %s" % ( field, table, where, groupBy, orderBy, limit, start) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: X = [] with open(datfile, 'r') as f: for r in f: row = r.rstrip().split(',') X.append([float(r) for r in row]) xfield = pfield # transform features x2fs(X, xfield, pre_transform) pfield = xfield X = numpy.array(X) if pre_process == "Z-Score": X = stats.zscore(X, axis=0) elif pre_process == "PCA": pca = PCA() (X, V) = fit_transform(pca, X) pfield = ['PCA_%d' % (d + 1) for d in range(len(pfield))] elif pre_process == "Whitened PCA": pca = PCA(whiten=True) (X, V) = fit_transform(pca, X) pfield = ['PCA_%d' % (d + 1) for d in range(len(pfield))] clust = cluster.KMeans(n_clusters=k) cidx = clust.fit_predict(X) cdists = clust.transform(X) # summary results results, clustmeans = evaluate(cdists, cidx, X) info["results"].append('Clustering the data using K-means with k=%d' % k) info["results"].append('Average distance to centroid: %.4f' % results['meandist']) hashquery = datfile + hex(hash(request.args.get('query', datfile)) & 0xffffffff) if pre_process == "PCA" or pre_process == "Whitened PCA": #write pca matrix file info["datfile_matrix"] = hashquery + '.pca.csv' with open(info["datfile_matrix"], 'w') as f: f.write("feature,%s\n" % (','.join(xfield))) for i in range(len(V)): f.write('PCA_%d,%s\n' % (i + 1, ','.join([str(v) for v in V[i]]))) info["pca_matrix_divs"] = Markup('<h2>PCA Components</h2><div id="svg-pca_matrix"></div>') else: info["pca_matrix_divs"] = '' # preparing within cluster distances into a js array f = [] for i in range(k): f.append('{cluster:"%d", distance:%.3f}' % (i, clustmeans[i])) info["clust_data"] = Markup('clust_data=[' + ','.join(f) + '];') #provenance #0:id,1:prediction result (grouping),2:actual label(shape),3:error,4:y,or features info["datfile_provenance"] = hashquery + '.provenance.csv' RES = ['Cluster %d' % (i + 1) for i in range(k)] with open(info["datfile_provenance"], 'w') as f: f.write('Cluster,Error,%s\n' % (','.join(pfield))) for i in range(len(cidx)): e = cdists[i][cidx[i]] f.write('%s,%.4f,%s\n' % (RES[cidx[i]], e, ','.join([str(r) for r in X[i]]))) pfield = ['cluster'] + pfield divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % ( pfield[d], d + 1) for d in range(len(pfield))] divs = ''.join(divs) divs = '<div class="chart"><div class="title">Distance to Centroid (<span id="active"></span> of <span id="total"></span> items selected.)<a href="javascript:reset(0)" class="reset" style="display: none;">reset</a></div></div>' + divs info['provenance_divs'] = Markup(divs) info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") info["datfile"] = info["datfile_provenance"] # prepare some messages info["title"] = "FIELD_X: <em>%s</em> from <br />TABLE: <em>%s</em>" % (','.join(pfield), table) info["title"] = Markup(info["title"]) info["message"] = Markup(''.join('<p>%s</p>' % m for m in info["message"] if len(m) > 0)) info["results"] = Markup('<ul>' + ''.join('<li>%s</li>' % m for m in info["results"] if len(m) > 0) + '</ul>') # format the message to encode HTML characters info['query'] = Markup(request.args.get('query', '')) t = vis.jinja_env.get_template('explore.html') v1 = t.render(**info) t = vis.jinja_env.get_template('ml_kmeans.html') v2 = t.render(**info) v3 = v1[:-7] + v2 + v1[-7:] + '</html>' return Response(v3, mimetype='text/html')
def render(vis, request, info): info["message"] = [] # module independent user inputs table = request.args.get("table", "") where = request.args.get("where", "1=1") limit = request.args.get("limit", "1000") # 10 years max if limit: limit = " limit %s" % limit start = request.args.get("start", "0") # start at 0 reload = int(request.args.get("reload", 0)) view = request.args.get("view", "") # module dependent user inputs latitude = request.args.get("latitude", "") longitude = request.args.get("longitude", "") field = request.args.get("field", "") mapField = request.args.get("mapField", " '1' ") pfield = request.args.get("pfield", []) groupBy = request.args.get("groupBy", "") if groupBy and len(groupBy) > 0: groupBy = " group by %s " % groupBy orderBy = request.args.get("orderBy", "") if orderBy and len(orderBy) > 0: orderBy = " order by %s " % orderBy # verify essential parameter details - smell test if len(table) == 0 or len(latitude) == 0 or len(longitude) == 0: info["message"].append("Table or latitude or longitude missing") info["message_class"] = "failure" else: # prepare sql query sql = ( "select row_number() over (order by 1,2) as rnum, * from (select %s,%s,%s,%s from %s where %s %s %s %s offset %s) as a" % (latitude, longitude, mapField, field, table, where, groupBy, orderBy, limit, start) ) (datfile, reload, result) = export_sql(sql, vis.config, reload, None, view) if len(result) > 0: info["message"].append(result) info["message_class"] = "failure" else: info["message_class"] = "success" if reload > 0: info["message"].append("Loaded fresh.") else: info["message"].append("Loading from cache. Use reload=1 to reload.") # if success return data file info["datfile"] = datfile # prepare some messages info["message"] = Markup("".join("<p>%s</p>" % m for m in info["message"] if len(m) > 0)) info["title"] = ( "LATITUDE: <em>%s</em> and <br />LONGITUDE:<em>%s</em> and <br />FIELDS: <em>%s</em> from <br />TABLE: <em>%s</em>" % (latitude, longitude, ",".join(pfield), table) ) info["title"] = Markup(info["title"]) divs = [ '<div class="chart"><div class="title">%s<a href="javascript:reset(%d)" class="reset" style="display: none;">reset</a></div></div>' % (pfield[d], d) for d in range(len(pfield)) ] info["divs"] = Markup("".join(divs)) info["fieldY"] = pfield[0] if len(pfield) > 0 else "Y" # format the message to encode HTML characters info["query"] = Markup(request.args.get("query", ""))