def callback(): # 页面索引 page_number = convert_helper.to_int_default( web_helper.get_query('page', '', False), 1) # 页面显示记录数量 page_size = convert_helper.to_int_default( web_helper.get_query('rows', '', False), 10) # page_size = 2 # 排序字段 sidx = web_helper.get_query('sidx', '', False) # 排序方式 sord = web_helper.get_query('sord', '', False) # 初始化输出格式 data = { 'records': 0, # 总记录数 'total': 0, # 总页数 'page': 1, # 页数 'rows': [] } # 获取记录总数 sql_count = 'select count(1) as records from product_class' result = db_helper.read(sql_count) if not result or result[0]['records'] == 0: return data data['records'] = result[0].get('records', 0) # 计算总页数 if data['records'] % page_size == 0: page_total = data['records'] // page_size else: page_total = data['records'] // page_size + 1 data['total'] = page_total data['page'] = page_number # 排序sql order_by = 'sort asc' if sidx: order_by = sidx + ' ' + sord if not order_by: order_by = ' id desc ' # 分页sql record_number = (page_number - 1) * page_size paging = ' limit ' + str(page_size) + ' offset ' + str(record_number) # 获取数据 sql_data = 'select * from product_class order by %(orderby)s %(paging)s' % { 'orderby': order_by, 'paging': paging } result = db_helper.read(sql_data) if result: data['rows'] = result if data: return web_helper.return_raise( json.dumps(data, cls=json_helper.CJsonEncoder)) else: return web_helper.return_msg(-1, '没有数据', '')
def ArticalList(): selectArticalList = """ select "id", "artical_name", "artical_type", "likesnum", "answernum", "readnum" from "articaltable" """ recode_result = db_helper.read(selectArticalList) print(recode_result) return web_helper.return_msg(0, '获取成功', recode_result)
def callback(): # 排序字段 sidx = web_helper.get_query('sidx', '', False) # 排序方式 sord = web_helper.get_query('sord', '', False) # 初始化输出格式 data = {'rows': []} # 排序sql order_by = 'sort asc' if sidx: order_by = sidx + ' ' + sord if not order_by: order_by = ' id desc ' # 获取数据 sql_data = 'select * from product_class order by %(orderby)s ' % { 'orderby': order_by } result = db_helper.read(sql_data) if result: data['rows'] = result if data: return web_helper.return_raise( json.dumps(data, cls=json_helper.CJsonEncoder)) else: return web_helper.return_msg(-1, '没有数据', '')
def callback(id): sql = 'select * from product_class where id=%s' % str(id) result = db_helper.read(sql) if result: return web_helper.return_msg(0, '成功', result[0]) else: return web_helper.return_msg(-1, '')
def ArticalOnly(): uid = web_helper.get_query('id', '主键(时间戳)') print("uid", uid) selectThisArtical = """ select "artical_name", "artical_type", "likesnum", "answernum", "readnum", "artical" from "articaltable" where id = '%s' """ % (uid) recode_result = db_helper.read(selectThisArtical) return web_helper.return_msg(0, '查询成功', recode_result)
def callback(): """ 获取指定记录 """ sql = """select * from infomation where id = 2""" # 读取记录 result = db_helper.read(sql) if result: # 直接输出json return web_helper.return_msg(0, '成功', result[0]) else: return web_helper.return_msg(-1, "查询失败")
def callback(id): """ 获取指定记录 """ sql = """select * from product_class where id = %s""" % (id,) # 读取记录 result = db_helper.read(sql) if result: # 直接输出json return web_helper.return_msg(0, '成功', result[0]) else: return web_helper.return_msg(-1, "查询失败")
def get_visit(): scanEmail_helper.fun_timer() ip_inner = web_helper.get_ip() ip_outer = web_helper.get_query("IP", "ip_outer") province = web_helper.get_query('province', '省') city = web_helper.get_query('city', '市') district = web_helper.get_query('district', '区县') sql = """SELECT code FROM "cityInfomation" WHERE name = '%s' and "parentCode" = (select code from "cityInfomation" where name = '%s')""" % ( district, city, ) manager_result = db_helper.read(sql) # 判断用户记录是否存在 code = manager_result[0].get('code', '').rstrip() sql_2 = """SELECT COUNT(1) FROM "visitRecode" WHERE code='%s'""" % (code) recode_result = db_helper.read(sql_2) count = recode_result[0].get('count', '') if count > 0: sql = """ UPDATE "visitRecode" SET valueindex=valueindex+1 WHERE code='%s'; """ % (code) data = () db_helper.write(sql, data) else: t = int(time.time()) insert = """ insert into "visitRecode" ("dateTime", "visitIP", "code", "ip_inner", valueIndex) values (%s, %s, %s, %s, 1) """ data = (t, ip_outer, code, ip_inner) db_helper.write(insert, data) return web_helper.return_msg(0, 'success')
def callback(): """ 获取指定记录 """ sql = """select * from user_log WHERE id=4 ORDER BY id DESC""" # 读取记录 result = db_helper.read(sql) if result: # 直接输出json print(result) return web_helper.return_msg(0, '成功', result) else: return web_helper.return_msg(-1, "查询失败")
def get_record(): # 初始化输出格式 data = { 'records': 0, # 总记录数 'total': 0, # 总页数 'page': 1, # 页数 'rows': [] } sql = '''select * from searchrecord limit 500 offset 0 ''' result = db_helper.read(sql) if result: data['rows'] = result if data: return web_helper.return_raise( json.dumps(data, cls=json_helper.CJsonEncoder)) else: return web_helper.return_msg(-1, '没有数据', '')
def callback(): id = web_helper.get_query('id', '', False) id = convert_helper.to_int_default(id, 0) # 判断分类有没有被引用 sql = 'select count(1) as records from product where product_class_id=%s' % str( id) result = db_helper.read(sql) if result and result[0].get('records', -1) > 0: return web_helper.return_msg(-1, '该分类已被引用,请清除对该分类的绑定后再来删除') else: sql = 'delete from product_class where id=%s returning id' % (id) val = (id) result = db_helper.write(sql, val) if result: return web_helper.return_msg(0, '成功') else: return web_helper.return_msg(-1, '失败')
def datainterface(): # 数据接口 str = '-1' b_Tip = 1 sql = """select * from public.naviList order by id""" manager_result = db_helper.read(sql) result = [] for l in manager_result: if l.get('father_Link').strip() == str.strip(): result.append(l) elif l.get('father_Link').strip() != str.strip(): for j in result: if j.get("linkTo").strip() == l.get("father_Link", 0).strip(): if b_Tip == 1: b_Tip += 1 j.update(child=[]) j.get('child').append(l) return web_helper.return_msg(2, '数据接口页面', result)
def deleteArtical(): uid = web_helper.get_form('uid', '主键(时间戳)') deleteArtical = """ delete from "articaltable" where id = %s """ data = (uid, ) beTip = db_helper.write(deleteArtical, data) if beTip == "False": return web_helper.return_msg( -1, '删除失败', ) else: selectArticalList = """ select "id", "artical_name", "artical_type", "likesnum", "answernum", "readnum" from "articaltable" """ recode_result = db_helper.read(selectArticalList) return web_helper.return_msg(0, '删除成功', recode_result)
def callback(id): """ 删除指定记录 """ # 判断该分类是否已经被引用,是的话不能直接删除 sql = """select count(*) as total from product where product_class_id=%s""" % (id,) # 读取记录 result = db_helper.read(sql) if result and result[0].get('total', -1) > 0: return web_helper.return_msg(-1, "该分类已被引用,请清除对该分类的绑定后再来删除") # 编辑记录 sql = """delete from product_class where id=%s returning id""" vars = (id,) # 写入数据库 result = db_helper.write(sql, vars) # 判断是否提交成功 if result: return web_helper.return_msg(0, '成功') else: return web_helper.return_msg(-1, "删除失败")
def test(self): # 新增记录,不带return参数 sql = """ INSERT INTO product_class( name, is_enable) VALUES (%s, %s) """ data = ('糖果', 1) result = db_helper.write(sql, data) print(result) # 新增记录,使用return参数返回新增id sql = """ INSERT INTO product_class( name, is_enable) VALUES (%s, %s) RETURNING id; """ data = ('饼干', 1) result = db_helper.write(sql, data) print(result) # 修改不存在的记录 sql = """ UPDATE product_class SET name=%s, is_enable=%s WHERE id=10000 RETURNING id; """ data = ('糖果', 1) result = db_helper.write(sql, data) print(result) # 查询记录 sql = """ SELECT * FROM product_class """ result = db_helper.read(sql) print(result)
def callback(): """ 获取列表数据 """ # 页面索引 page_number = convert_helper.to_int1(web_helper.get_query('page', '', False)) # 页面显示记录数量 page_size = convert_helper.to_int0(web_helper.get_query('rows', '', False)) # 排序字段 sidx = web_helper.get_query('sidx', '', False) # 顺序还是倒序排序 sord = web_helper.get_query('sord', '', False) # 初始化排序字段 order_by = 'sort asc' if sidx: order_by = sidx + ' ' + sord ############################################################# # 初始化输出格式(前端使用jqgrid列表,需要指定输出格式) data = { 'records': 0, 'total': 0, 'page': 1, 'rows': [], } ############################################################# # 执行sql,获取指定条件的记录总数量 sql = 'select count(1) as records from product_class' result = db_helper.read(sql) # 如果查询失败或不存在指定条件记录,则直接返回初始值 if not result or result[0]['records'] == 0: return data # 保存总记录数量 data['records'] = result[0].get('records', 0) ############################################################# ### 设置分页索引与页面大小 ### # 设置分页大小 if page_size is None or page_size <= 0: page_size = 10 # 计算总页数 if data['records'] % page_size == 0: page_total = data['records'] // page_size else: page_total = data['records'] // page_size + 1 # 记录总页面数量 data['total'] = page_total # 判断提交的页码是否超出范围 if page_number < 1 or page_number > page_total: page_number = page_total # 记录当前页面索引值 data['page'] = page_number # 计算当前页面要显示的记录起始位置 record_number = (page_number - 1) * page_size # 设置查询分页条件 paging = ' limit ' + str(page_size) + ' offset ' + str(record_number) ### 设置排序 ### if not order_by: order_by = 'id desc' ############################################################# # 组合sql查询语句 sql = "select * from product_class order by %(orderby)s %(paging)s" % \ {'orderby': order_by, 'paging': paging} # 读取记录 result = db_helper.read(sql) if result: # 存储记录 data['rows'] = result if data: # 直接输出json return web_helper.return_raise(json.dumps(data, cls=json_helper.CJsonEncoder)) else: return web_helper.return_msg(-1, "查询失败")
def callback(): """ 获取列表数据 """ # 设置查询条件 wheres = '' # 产品分类id product_class_id = convert_helper.to_int0(web_helper.get_query('product_class_id', '产品分类id', is_check_null=False)) if product_class_id > 0: wheres = 'where product_class_id=' + str(product_class_id) # 页面索引 page_number = convert_helper.to_int1(web_helper.get_query('page', '', is_check_null=False)) # 页面显示记录数量 page_size = convert_helper.to_int0(web_helper.get_query('rows', '', is_check_null=False)) # 排序字段 sidx = web_helper.get_query('sidx', '', is_check_null=False) # 顺序还是倒序排序 sord = web_helper.get_query('sord', '', is_check_null=False) # 初始化排序字段 order_by = 'id desc' if sidx: order_by = sidx + ' ' + sord # 类型 type = web_helper.get_query('type', '类型', is_check_null=False) # 判断是否是前台提交获取数据 if type != 'backstage': # 判断是否已经存在查询条件了,是的话在原查询条件后面拼接 if wheres: wheres = wheres + ' and is_enable=1' else: wheres = 'where is_enable=1' ############################################################# # 初始化输出格式(前端使用jqgrid列表,需要指定输出格式) data = { 'records': 0, 'total': 0, 'page': 1, 'rows': [], } ############################################################# # 执行sql,获取指定条件的记录总数量 sql = 'select count(1) as records from product %(wheres)s' % {'wheres': wheres} result = db_helper.read(sql) # 如果查询失败或不存在指定条件记录,则直接返回初始值 if not result or result[0]['records'] == 0: return data # 保存总记录数量 data['records'] = result[0].get('records', 0) ############################################################# ### 设置分页索引与页面大小 ### # 设置分页大小 if page_size is None or page_size <= 0: page_size = 10 # 计算总页数 if data['records'] % page_size == 0: page_total = data['records'] // page_size else: page_total = data['records'] // page_size + 1 # 记录总页面数量 data['total'] = page_total # 判断提交的页码是否超出范围 if page_number < 1 or page_number > page_total: page_number = page_total # 记录当前页面索引值 data['page'] = page_number # 计算当前页面要显示的记录起始位置 record_number = (page_number - 1) * page_size # 设置查询分页条件 paging = ' limit ' + str(page_size) + ' offset ' + str(record_number) ### 设置排序 ### if not order_by: order_by = 'id desc' ############################################################# # 组合sql查询语句 sql = "select * from product %(wheres)s order by %(orderby)s %(paging)s" % \ {'wheres': wheres, 'orderby': order_by, 'paging': paging} # 读取记录 result = db_helper.read(sql) if result: # 存储记录 data['rows'] = result if data: # 直接输出json return web_helper.return_raise(json.dumps(data, cls=json_helper.CJsonEncoder)) else: return web_helper.return_msg(-1, "查询失败")
def post_login(): """用户登陆验证""" ############################################################## # 获取并验证客户端提交的参数 ############################################################## username = web_helper.get_form('username', '帐号') password = web_helper.get_form('password', '密码') verify = web_helper.get_form('verify', '验证码') ip = web_helper.get_ip() ############################################################## # 从session中读取验证码信息 ############################################################## s = web_helper.get_session() verify_code = s.get('verify_code') print(verify, s) # 删除session中的验证码(验证码每提交一次就失效) if 'verify_code' in s: del s['verify_code'] s.save() # 判断用户提交的验证码和存储在session中的验证码是否相同 if verify.upper() != verify_code: return web_helper.return_msg(-1, '验证码错误') ############################################################## ### 获取登录用户记录,并进行登录验证 ### ############################################################## sql = """select * from manager where login_name='%s'""" % (username, ) # 从数据库中读取用户信息 manager_result = db_helper.read(sql) # 判断用户记录是否存在 if not manager_result: return web_helper.return_msg(-1, '账户不存在') ############################################################## ### 验证用户登录密码与状态 ### ############################################################## # 对客户端提交上来的验证进行md5加密将转为大写(为了密码的保密性,这里进行双重md5加密,加密时从第一次加密后的密串中提取一段字符串出来进行再次加密,提取的串大家可以自由设定) # pwd = encrypt_helper.md5(encrypt_helper.md5(password)[1:30]).upper() # 对客户端提交上来的验证进行md5加密将转为大写(只加密一次) pwd = encrypt_helper.md5(password).upper() # 检查登录密码输入是否正确 if pwd != manager_result[0].get('login_password', ''): return web_helper.return_msg(-1, '密码错误') # 检查该账号虽否禁用了 if manager_result[0].get('is_enable', 0) == 0: return web_helper.return_msg(-1, '账号已被禁用') ############################################################## ### 把用户信息保存到session中 ### ############################################################## manager_id = manager_result[0].get('id', 0) s['id'] = manager_id s['login_name'] = username s.save() ############################################################## ### 更新用户信息到数据库 ### ############################################################## # 更新当前管理员最后登录时间、Ip与登录次数(字段说明,请看数据字典) sql = """update manager set last_login_time=%s, last_login_ip=%s, login_count=login_count+1 where id=%s""" # 组合更新值 vars = ( 'now()', ip, manager_id, ) # 写入数据库 db_helper.write(sql, vars) data = [{'a': 1, 'b': 2, 'c': 3, 'd': 4, 'e': 5}] return web_helper.return_msg(0, '登录成功', data)