def data_graph(**params): conn = MysqlPython('localhost', 'root', 'mysql123', 'SCG'); days = params['days'] tablename= params['tablename'] column_one= params['column_one'] column_two= params['column_two'] timestamp_col= params['timestamp_col'] where_condition= params['where_condition'] convert = params['convert'] graph_sql = "select "+column_one+" , "+column_two+" , "+timestamp_col+" from "+tablename+" where "+where_condition+" and "+timestamp_col+">=DATE_SUB(CURDATE(),INTERVAL "+str(days)+" DAY) ORDER BY "+column_one+" , "+column_two+" , "+timestamp_col+" asc"; print "\n"+graph_sql+"\n" data = conn.select_advanced(graph_sql); series_list=[]; json_string='[ { "name" : "Show/Hide All" } '; for row in data: if row[0] not in series_list: series_list.append(row[0]); for series in series_list: graph_data=[] for row in data: if row[0]==series: timestamp=str(row[2]); timestamp=int(datetime.datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S").strftime('%s')) * 1000; count=row[1] if convert == 'uptime': count= convert_to_min(row[1]) if convert == 'percent': count= convert_percent(row[1]) if count != None: graph_data.append("["+str(timestamp)+","+str(count)+"]"); json_string=json_string+', { "name" : "'+series+'" , "data" : '+'[ '+', '.join(graph_data)+' ] }'; json_string=json_string+' ]'; return json_string;
def big_table(**params): conn = MysqlPython('localhost', 'root', 'mysql123', 'SCG'); page = params['page'] limit= params['limit'] tablename= params['tablename'] columns= params['columns'] where_condition= params['where_condition'] search= params['search'] page = page-1; start= limit*page; where_clause=" where "; #print search for key in search: where_clause=where_clause+key+" like '%%"+search[key]+"%%' and "; where_clause=where_clause+where_condition; count_sql="select count(*) from "+tablename; full_count_sql=count_sql+" where "+where_condition; #print full_count_sql; acdata = conn.select_advanced(full_count_sql); acount = acdata[0]; filter_count_sql=count_sql+where_clause; #print filter_count_sql; fcdata = conn.select_advanced(filter_count_sql); fcount = fcdata[0]; if fcount < start: start=0; #print columns; data_sql="select "+",".join(columns)+" from "+tablename+where_clause+" limit "+str(start)+","+str(limit); print "\n"+data_sql+"\n" result=[]; data = conn.select_advanced(data_sql); for row in data: #print row; result.append(dict(zip(columns, row))); json_data={'count':acount, 'items':result, 'filter_count':fcount }; #print "\n\n" #returns the data as json return json.dumps(json_data, default=json_util.default)