def get_data(): schemas, tables = [], [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list schemas and tables'): cur.execute(query('list-schemas')) schemas = cur.fetchall() cur.execute(query('list-tables')) tables = cur.fetchall() schema_cols = ['', 'tables', 'views', 'foreign tables', 'temporary tables', 'functions', 'sequences'] table_cols = ['Column name', 'Column type', 'Column default', 'Column is nullable'] table_data = {'public': OrderedDict()} for table in tables: if table[0] not in table_data: table_data[table[0]] = OrderedDict() table_data[table[0]][table[1]] = { 'column-data': OrderedDict([ ('colum-names', parse_pg_array(table[2])), ('column-types', parse_pg_array(table[3])), ('column-defaults', parse_pg_array(table[4])), ('column-is-nullable', parse_pg_array(table[5])), ]), 'table-size': table[6], } return (schemas, schema_cols, table_data, table_cols)
def create_tables(db_table): db = db_table[0] table = db_table[1] pg.query('CREATE TABLE IF NOT EXISTS {} \ ( id integer NOT NULL,\ name varchar(20) NOT NULL,\ PRIMARY KEY (id) ) ;'.format(table), database=db) for i in range(10): insert_rows(db, table, i)
def delNoName(type, name): sql = """ update user_info set %s=null where lower(%s)=lower('%s') """ % ( type, type, name, ) pg.query(sql) print "del", type, name
def GET(self): uid = getUserId() today = time.strftime('%Y-%m-%d', time.localtime(time.time())) template_data = {} user_info_sql = """ select u.f_name as user_name ,(select f_name from t_street ts where ts.f_id = u.f_street_id) as street_name from t_user u where u.f_id = %s """ % uid user = pg.query(user_info_sql) if user: user = user[0] template_data["user_id"] = uid template_data["today"] = today template_data["street_name"] = user.street_name template_data["coster_name"] = user.user_name template_data["print"] = "" parking_count_sql = """ select sum(case when f_leave_stamp is null then 1 else 0 end) as uncost_times ,sum(case when f_cost_type = '正常缴费' then 1 else 0 end) as cost_times ,round(sum(case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as parking_range ,sum(f_act_cost) as act_cost ,sum(case when f_cost_type like '%免费%' then 1 else 0 end) as free_times ,round(sum(case when f_cost_type like '%免费%' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as free_range from t_parking_record tpr where f_coster_id = %s and date(f_parking_stamp) = CURRENT_DATE """ % uid parking_count_info = pg.query(parking_count_sql) if parking_count_info: parking_count_info = parking_count_info[0] template_data["car_count"] = parking_count_info.cost_times template_data["cost_count"] = parking_count_info.act_cost template_data["free_car_count"] = parking_count_info.free_times template_data["free_car_time_count"] = parking_count_info.free_range template_data["stay_car"] = parking_count_info.uncost_times template_data["print"] = rendeHtml("mobile/dailyreport_print", **template_data) return rendeHtml("mobile/dailyreport", **template_data)
def create_database(tables_db): tables = tables_db[0] db = tables_db[1] pg.query('CREATE DATABASE {}'.format(db)) table_list = [] for i in range(tables): table_list.append((db, get_random_string(10))) pool = ThreadPool(int(math.sqrt(MAX_DB_CONNS))) result = pool.map(create_tables, table_list) pool.close() pool.join()
def POST(self): u = web.input().get("un", "").strip() p = web.input().get("pw", "").strip() ret = { "error": 0 } if u and p: user = list(pg.db.select("t_user", where="f_account='%s'" % u, limit=1)) if user and user[0]["f_password"] == p: loginUser(user[0]["f_id"], u) ret["u_id"] = user[0]["f_id"] sql = """ select f_icon as icon ,f_name as title ,-1 as count ,f_key as key ,f_uri as url from t_unit tu where tu.f_key like 'Mobile.%%' and tu.f_type = 'Func' and tu.f_id in (select f_unit_id from t_role_unit tru where tru.f_role_id in (select f_role_id from t_user_role where f_user_id = %s)) order by f_index desc """ % ret["u_id"] units = pg.query(sql) url = "/mobile/main" sql_user = """select * from t_user where f_id = %s""" % ret["u_id"] user = pg.query(sql_user) if user: user = user[0] if user.f_type == "收费人员": url = "/mobile/main?u_id=" + str(ret["u_id"]) else: url = "/mobile/queryparkingrecord_checkfilter" ret["units"] = units ret["url"] = url else: ret["error"] = 1 ret["errorMsg"] = "用户名或密码错误!" else: ret["error"] = 1 ret["errorMsg"] = "没有输入用户名和密码!" return json.dumps(ret)
def GET(self, parking_id): sql = """ select p.f_id ,p.f_act_cost ,p.f_parking_code ,p.f_key ,p.f_car_type ,p.f_parking_stamp as f_parking_stamp1 ,to_char(p.f_parking_stamp, 'HH24:MI:SS') as f_parking_stamp ,p.f_car_no ,(select f_name from t_street s where s.f_id = p.f_street_id) as f_street_name from t_parking_record p where p.f_id = %s """ % parking_id data = pg.query(sql)[0] data["f_time"] = datetime.datetime.now() - data["f_parking_stamp1"] data["f_cost"] = str(utils.getActualCost(data["f_time"]) if utils.getActualCost(data["f_time"]) > 0 else 0.00) data["f_time"] = str(data["f_time"])[0:8] data["f_act_cost"] = str(data["f_act_cost"])[1:-3] return rendeHtml("mobile/checkout", parking=data)
def getParkingData(self, uid): sql = """ select pr.f_id, pr.f_act_cost::numeric, pr.f_parking_code, pr.f_key, pr.f_car_no, ( select count(*) from t_parking_record tpr where tpr.f_cost::numeric > 0.00 and tpr.f_cost_type = '逃逸' and tpr.f_car_no = pr.f_car_no ) as f_escape_count, to_char(pr.f_parking_stamp, 'HH24:MI:SS') as f_parking_stamp from t_parking p left join t_parking_record pr on p.f_key = pr.f_key where p.f_id in (select f_parking_id from t_user_parking where f_user_id = %s) and p.f_state = 1 """ % (uid) return pg.query(sql)
def getGodInfoFollow(user_id=None, god_name=None, recommand=False): ''' modify by bigzhu at 15/08/06 17:05:22 可以根据god_name来取 modify by bigzhu at 15/08/28 17:09:31 推荐模式就是只查随机5个 modify by bigzhu at 15/08/28 17:30:38 没有社交帐号的不要查出来 ''' sql = ''' select u.id as god_id, u.created_date as u_created_date, * from god_info u where not ((twitter is null or twitter='') and (github is null or github='') and (instagram is null or instagram='')) order by u.created_date desc ''' if god_name: sql = ''' select * from (%s) s where lower(user_name)=lower('%s') ''' % (sql, god_name) if user_id: sql = ''' select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id order by ut.u_created_date desc ''' % (sql, user_id) if recommand: sql = ''' select * from (%s) s where s.followed is null ''' % sql sql = ''' select * from (%s) s order by random() limit 5; ''' % sql return pg.query(sql)
def getUserInfoGithub(): ''' create by bigzhu at 15/07/15 22:45:42 ''' sql = ''' select * from user_info u left join github_user g on u.user_name=g.login ''' return pg.query(sql)
def GET(self): uid = getUserId() today = time.strftime('%Y-%m-%d', time.localtime(time.time())) template_data = {} user_info_sql = """ select u.f_name as user_name ,(select f_name from t_street ts where ts.f_id = u.f_street_id) as street_name from t_user u where u.f_id = %s """ % uid user = pg.query(user_info_sql) if user: user = user[0] template_data["today"] = today template_data["street_name"] = user.street_name template_data["coster_name"] = user.user_name template_data["print"] = "" parking_count_sql = """ select count(p.f_state) as uncost_times, sum(pr.f_act_cost) as act_cost from t_parking p left join t_parking_record pr on pr.f_key = p.f_key where p.f_id in (select f_parking_id from t_user_parking where f_user_id = %s) and p.f_state = 1 """ % uid parking_count_info = pg.query(parking_count_sql) if parking_count_info: parking_count_info = parking_count_info[0] template_data["act_cost"] = parking_count_info.act_cost template_data["stay_car"] = parking_count_info.uncost_times template_data["print"] = rendeHtml("mobile/dailyshift_print", **template_data) return rendeHtml("mobile/dailyshift", **template_data)
def followedWho(user_id): sql = ( """ select god_id from follow_who where user_id=%s """ % user_id ) return pg.query(sql)
def getOpenidsByName(type, name): sql = ''' select w.openid from user_info u, follow_who f, user_info u2, wechat_user w where lower(u.%s)=lower('%s') and u.id = f.god_id and u2.id = f.user_id and w.user_name=u2.user_name ''' % (type, name) return pg.query(sql)
def getUserInfoGithub(user_name=None): ''' create by bigzhu at 15/07/15 22:45:42 ''' sql = ''' select * from user_info u left join github_user g on lower(u.user_name)=lower(g.login) ''' if user_name: sql += " where user_name='%s' " % user_name return pg.query(sql)
def Storage(params=None): handle_params(params) data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list storage query'): cur.execute(query('list-storage')) data = cur.fetchall() display_schemas = params.getlist('schema') schemas = set() treemap_data = {'name': 'root', 'children': []} for (schema_name, table_name, t_size, t_tuples) in data: schemas.add(schema_name) if schema_name in display_schemas or len(display_schemas) == 0: treemap_data['children'].append({'schema_name': schema_name, 'name': table_name, 'size': int(t_size), 'rows': int(t_tuples)}) p = Page() # Header p.add_page(Header(title='Storage', js=['static/pages/storage.js', 'static/lib/d3/d3.js', 'static/lib/d3/lib/colorbrewer/colorbrewer.js'], css=['static/pages/storage.css'])) p.add_page(Navigation(page='storage')) with p.div({'class': 'container-fluid'}): with p.div({'class': 'row form-row'}): with p.div({'class': 'col-md-10'}): with p.form({'id': 'schema-form', 'class': 'form-inline'}): for schema in schemas: with p.label({'class': 'checkbox-inline'}): checked = (schema in display_schemas or len(display_schemas) == 0) and 'checked' or '' with p.input({'type': 'checkbox', 'name': 'schema', 'value': '%s' % schema}, args=[checked]): p.content(schema) with p.div({'class': 'col-md-2'}): with p.form({'id': 'mode-form', 'class': 'form-inline'}): with p.label({'class': 'radio-inline'}): with p.input({'type': 'radio', 'name': 'mode', 'value': 'size'}, args=['checked']): p.content('Size') with p.label({'class': 'radio-inline'}): with p.input({'type': 'radio', 'name': 'mode', 'value': 'rows'}): p.content('Rows (est.)') with p.div({'class': 'row'}): with p.div({'class': 'col-md-12'}): with p.div({'id': 'treemap-chart'}): pass with p.script(): p.content('PGUI.STORAGE.mk_treemap_chart(%s);' % json.dumps(treemap_data)) p.add_page(Footer()) return p
def getPayInfo(openid): sql = ( """ select id, card_number, stat_date, card_number, total_fee, status from pay where openid='%s' and status='payed' order by created_date desc """ % openid ) return list(pg.query(sql))
def GET(self): sql = """ select * from t_document """ docs = pg.query(sql) return rendeHtml("mobile/doclist", docs=docs)
def getBindInfoByOpenid(openid): sql = ''' select b.openid, b.card_number, b.car_number, b.car_type, -- 车型 b.phone_number, -- 手机号 b.name, -- 姓名 b.id_number -- 身份证号 from bind_card_info b where b.openid='%s' ''' % openid return pg.query(sql)
def GET(self): uid = getUserId() sql = """ select count(*) as count from t_message where f_is_done = 0 and abs(extract(epoch from now() - f_create_time)/60) < 5 and f_street_id = (select f_street_id from t_user where f_id = %s limit 1) """ % uid msg_count = pg.query(sql)[0] return json.dumps(msg_count)
def POST(self): web.header('Content-Type', 'application/json;charset=UTF-8') try: u_id = getUserId() print "u_id is %s" % u_id sql = """ select f_icon as icon ,f_name as title ,-1 as count ,f_key as key ,f_uri as url from t_unit tu where tu.f_key like 'Mobile.%%' and tu.f_type = 'Func' and tu.f_id in (select f_unit_id from t_role_unit tru where tru.f_role_id in (select f_role_id from t_user_role where f_user_id = %s)) order by f_index desc """ % u_id data = pg.query(sql) url = "/mobile/index" sql_user = """select * from t_user where f_id = %s""" % u_id user = pg.query(sql_user) if user: user = user[0] if user.f_type == "收费人员": url = "/mobile/index?u_id=" + u_id else: url = "/mobile/queryparkingrecord_checkfilter" result = {"success": "true", "data": data, "url": url} return json.dumps(result, cls=ExtendedEncoder) except Exception: # 输出错误信息 msg = traceback.print_exc() json_data = {'success': "false", 'message': '新增数据失败,详细信息:%s' % msg} return json.dumps(json_data, cls=ExtendedEncoder)
def get_col_size(): if 'table-schema' in request.args and 'table-name' in request.args: params = {'table-schema': escape(request.args['table-schema']), 'table-name': escape(request.args['table-name'])} data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('fetching column size for %s' % params): cur.execute(query('get-column-size'), params) data = cur.fetchall() return json.dumps(data) return 'Please specify a table!'
def getMessages(user_id=None, god_name=None, type=None, id=None, limit=50, offset=None, last_message_id=None): ''' create by bigzhu at 15/07/14 15:11:44 查出我 Follow 的用户的twitter message modify by bigzhu at 15/07/17 01:39:21 过于复杂,合并sql,根据god_name也可以查 modify by bigzhu at 15/07/19 15:30:55 可以根据type和id查出某一条记录 modify by bigzhu at 15/07/22 12:49:35 limit 设定取多少条 modify by bigzhu at 15/08/13 17:20:15 建立view,查询简化得不行 modify by bigzhu at 15/08/16 18:09:53 支持对last_time的查询 modify by bigzhu at 15/12/11 11:25:32 改用last_message_id 不再用last_time ''' sql = "select * from messages" if last_message_id is not None: where = ' where id>%s' % last_message_id sql += where sql += " order by created_at desc " if type and id: sql = ''' select * from (%s) s where s.m_type='%s' and s.id = %s ''' % (sql, type, id) if god_name: sql = ''' select * from (%s) s where lower(s.user_name)=lower('%s') ''' % (sql, god_name) if user_id: sql = ''' select * from (%s) s where lower(s.user_name) in ( select lower(user_name) from user_info where id in( select god_id from follow_who where user_id=%s ) ) ''' % (sql, user_id) if limit: sql = ''' select * from (%s) s limit %s ''' % (sql, limit) if offset: sql = sql + ' offset %s' % offset return pg.query(sql)
def getUserInfoTwitterUser(user_id=None): sql = ''' select u.id as god_id, 0 followed, u.created_date as u_created_date, * from user_info u, twitter_user tu where u.twitter = tu.screen_name order by tu.created_date desc ''' if user_id: sql = ''' select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id order by ut.u_created_date desc ''' % (sql, user_id) return pg.query(sql)
def GET(self, id): sql = """ select * from t_document where f_id=%d """ % int(id) docs = pg.query(sql) doc = None if docs: doc = docs[0] return rendeHtml("mobile/doccontent", doc=doc)
def get_size_db(db_name): """ Return the size of the data of a given database. This query only check the data of all tables excluding indexes data If something goes wrong, return 0 """ status, result_list = pg.query( "SELECT SUM(pg_table_size(c.oid)) \ FROM pg_class c \ JOIN pg_database d ON d.datdba = c.relowner \ WHERE c.relkind = 'r' \ AND d.datname = \'{}\';".format(db_name), db_name) if status: return result_list[0][0] else: return 0
def getPayInfo(openid=None, statuses=None, user_id=None, id=None): sql = ''' select id, openid, card_number, stat_date, card_number, total_fee, status from pay where 1=1 ''' if openid: sql += " and openid='%s' " % openid if statuses: in_statuses = db_bz.formatToInSql(statuses) sql += " and status in (%s) " % in_statuses if user_id: sql += " and user_id=%s " % user_id if id: sql += " and id=%s " % id sql += " order by created_date desc " return list(pg.query(sql))
def getWechatUserBindInfoByOpenid(openid): ''' 查出用户信息 ''' sql = ''' select w.openid, w.id as wechat_user_id, b.card_number, b.car_number, b.car_type, -- 车型 b.phone_number, -- 手机号 b.name, -- 姓名 b.id_number -- 身份证号 from wechat_user w left join bind_card_info b on w.id = b.wechat_user_id where w.openid='%s' ''' % openid return pg.query(sql)
def getMessages(user_id=None, god_name=None, type=None, id=None, limit=50, offset=None, last_time=None): ''' create by bigzhu at 15/07/14 15:11:44 查出我 Follow 的用户的twitter message modify by bigzhu at 15/07/17 01:39:21 过于复杂,合并sql,根据god_name也可以查 modify by bigzhu at 15/07/19 15:30:55 可以根据type和id查出某一条记录 modify by bigzhu at 15/07/22 12:49:35 limit 设定取多少条 modify by bigzhu at 15/08/13 17:20:15 建立view,查询简化得不行 modify by bigzhu at 15/08/16 18:09:53 支持对last_time的查询 ''' sql = "select * from messages" if last_time is not None: last_time = time_bz.datetimeToTimestamp(last_time) # 转为timestamp last_time = last_time - 30*10*10 # 后30分钟的也取出来 where = ' where created_at>to_timestamp(%s)' % last_time sql += where if type and id: sql = ''' select * from (%s) s where s.m_type='%s' and s.id = %s ''' % (sql, type, id) if god_name: sql = ''' select * from (%s) s where lower(s.user_name)=lower('%s') ''' % (sql, god_name) if user_id: sql = ''' select * from (%s) s where lower(s.user_name) in ( select lower(user_name) from user_info where id in( select god_id from follow_who where user_id=%s ) ) ''' % (sql, user_id) if limit: sql = ''' select * from (%s) s limit %s ''' % (sql, limit) if offset: sql = sql + ' offset %s' % offset return pg.query(sql)
def GET(self): uid = getUserId() sql = """ select * ,(select f_name from t_parking tp1 where tp1.f_id = tm.f_parking_id) as f_parking_name from t_message tm where f_is_done = 0 and abs(extract(epoch from now() - f_create_time)/60) < 5 and f_street_id = (select f_street_id from t_user where f_id = %s limit 1) order by f_create_time desc """ % uid msgs = pg.query(sql) return rendeHtml("mobile/msglist", msgs=msgs)
def getCardinfos(openid=None, id=None): sql = ''' select b.id, b.openid, b.card_number, b.car_number, b.car_type, -- 车型 b.phone_number, -- 手机号 b.name, -- 姓名 b.id_number -- 身份证号 from bind_card_info b where is_delete=0 ''' if openid: sql += " and b.openid='%s' " % openid if id: sql += " and b.id=%s " % id return pg.query(sql)
def GET(self): sql = """ select f_street_id ,(select f_name from t_street ts where ts.f_id = tpr.f_street_id) as f_street_name ,sum(case when f_leave_stamp is null then 1 else 0 end) as uncost_times ,sum(case when f_cost_type = '正常缴费' then 1 else 0 end) as cost_times ,round(sum(case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as parking_range ,sum(f_act_cost) as act_cost ,sum(case when f_cost_type like '%免费%' then 1 else 0 end) as free_times ,round(sum(case when f_cost_type like '%免费%' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as free_range from t_parking_record tpr where date(f_parking_stamp) = CURRENT_DATE group by tpr.f_street_id """ data = pg.query(sql) return rendeHtml("mobile/querystreetcount", result=data)
def POST(self, sid): sql = """ select * ,(select f_phone from t_user tu where tu.f_id = tpr.f_coster_id) as f_phone ,round((case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as f_time from t_parking_record tpr where f_id = %s """ % sid datas = pg.query(sql) parking_record = {} if datas: parking_record = datas[0] template_data = dict(parking_record.items()) template_data["f_time"] = str(parking_record["f_leave_stamp"] - parking_record["f_parking_stamp"])[0:8] template_data["f_cost"] = str(parking_record["f_cost"])[1:-3] template_data["f_act_cost"] = str(parking_record["f_act_cost"])[1:-3] template_data["f_phone"] = template_data["f_phone"][-4:] return rendeHtml("mobile/checkout_report", **template_data)
def GET(self): isAjax = bool(web.input().get("ajax")) u_id = getUserId() sql = """ select p.*,rd.f_car_type,to_char(rd.f_parking_stamp,'HH24:MI:SS'), rd.f_car_no,s.f_name as f_street_name from t_parking p left join t_parking_record rd on rd.f_key = p.f_key left join t_street s on p.f_street_id = s.f_id left join t_parking_image tpi on tpi.f_key = rd.f_key left join t_user_parking tup on tup.f_parking_id = p.f_id where tup.f_user_id = %s order by p.f_id """ % u_id data = pg.query(sql) if isAjax: return json.dumps(data) else: return rendeHtml("mobile/index", parkings=data)
def insert_rows(db, table, insert_id): f_name = get_random_string(10) pg.query( 'INSERT INTO {} (id, name) \ VALUES ( \'{}\', \'{}\')'.format(table, insert_id, f_name), db)
def delNoName(type, name): sql = ''' update user_info set %s=null where lower(%s)=lower('%s') ''' % (type, type, name) pg.query(sql) print 'del', type, name
def getTwitterMessages(): sql = ''' select * from twitter_message tm, twitter_user tu where tm.t_user_id=tu.id_str order by tm.created_at desc ''' return pg.query(sql)
def Index(params=None): """ Renders the index page which displays some generic connections information and links to all the activated modules. """ handle_params(params) p = Page() # Header p.add_page(Header(title='Index')) p.add_page(Navigation(page='index')) with p.div({'class': 'container-fluid'}): # Connection information data, params = [], defaultdict(str) param_keys = ('server_version', 'server_encoding', 'client_encoding', 'is_superuser', 'TimeZone') with pg_connection(*current_user.get_config()) as (con, cur, err): for k in param_keys: params[k] = con.get_parameter_status(k) with pg_log_err('list databases'): cur.execute(query('list-databases')) data = cur.fetchall() with p.div({'class': 'row'}): with p.div({'class': 'col-md-2'}): with p.div({'class': 'btn-group'}): with p.button({ 'class': 'btn btn-default dropdown-toggle', 'data-toggle': 'dropdown', 'aria-expanded': 'false' }): p.content( 'Switch database <span class="caret"></span>') with p.ul({'class': 'dropdown-menu', 'role': 'menu'}): for n, d in enumerate(data): with p.li(): with p.a({'href': 'index?database=%s' % d[0]}): p.content(d[0]) if n < len(data) - 1: with p.li({'class': 'divider'}): pass with p.div({'class': 'col-md-4 small'}): p.content('<strong>%s</strong>' % current_user.database) p.content( '<br>%s@%s:%s' % (current_user.name, current_user.host, current_user.port)) with p.div({'class': 'col-md-6 small'}): with p.ul({'class': 'list-inline'}): for k, v in sorted(params.items()): with p.li(): p.content('%s: %s' % (k, v)) with p.hr(): pass # Modules cols = 12 col_size = 4 col = 0 for page in PAGES[1:]: if col == 0: with p.div({'class': 'row'}, close=False): pass with p.div({'class': 'col-md-%s' % col_size}): with p.a({'href': '/%s' % page['name']}): with p.div({'class': 'page-link'}): with p.span({ 'class': 'page-icon glyphicon glyphicon-%s' % page['icon'] }): pass with p.h3(): p.content(page['caption']) p.content(page['desc']) col = (col + col_size) % cols if col == 0: p.close('div') p.add_page(Footer()) return p
def Storage(params=None): handle_params(params) data = [] with pg_connection(*current_user.get_config()) as (con, cur, err): with pg_log_err('list storage query'): cur.execute(query('list-storage')) data = cur.fetchall() display_schemas = params.getlist('schema') schemas = set() treemap_data = {'name': 'root', 'children': []} for (schema_name, table_name, t_size, t_tuples) in data: schemas.add(schema_name) if schema_name in display_schemas or len(display_schemas) == 0: treemap_data['children'].append({ 'schema_name': schema_name, 'name': table_name, 'size': int(t_size), 'rows': int(t_tuples) }) p = Page() # Header p.add_page( Header(title='Storage', js=[ 'static/pages/storage.js', 'static/lib/d3/d3.js', 'static/lib/d3/lib/colorbrewer/colorbrewer.js' ], css=['static/pages/storage.css'])) p.add_page(Navigation(page='storage')) with p.div({'class': 'container-fluid'}): with p.div({'class': 'row form-row'}): with p.div({'class': 'col-md-10'}): with p.form({'id': 'schema-form', 'class': 'form-inline'}): for schema in schemas: with p.label({'class': 'checkbox-inline'}): checked = (schema in display_schemas or len(display_schemas) == 0) and 'checked' or '' with p.input( { 'type': 'checkbox', 'name': 'schema', 'value': '%s' % schema }, args=[checked]): p.content(schema) with p.div({'class': 'col-md-2'}): with p.form({'id': 'mode-form', 'class': 'form-inline'}): with p.label({'class': 'radio-inline'}): with p.input( { 'type': 'radio', 'name': 'mode', 'value': 'size' }, args=['checked']): p.content('Size') with p.label({'class': 'radio-inline'}): with p.input({ 'type': 'radio', 'name': 'mode', 'value': 'rows' }): p.content('Rows (est.)') with p.div({'class': 'row'}): with p.div({'class': 'col-md-12'}): with p.div({'id': 'treemap-chart'}): pass with p.script(): p.content('PGUI.STORAGE.mk_treemap_chart(%s);' % json.dumps(treemap_data)) p.add_page(Footer()) return p
def delGithubUser(user_name): sql = ''' update user_info set github=null where lower(github)=lower('%s') ''' % user_name pg.query(sql)