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):
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"] = []
# 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", ""))
