async def cateSelect(self,request,payload):
data = {
'code': 0,
'data': {},
'msg' :''
}
try:
param = json.loads(request._payload._buffer[0])["data"]
limit = param['pageSize']
offset = (param['pageIndex']-1)*param['pageSize']
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_cate.select().limit(limit).offset(offset)
sql2 = blog_cate.select()
if param['cate_id']:
sql = blog_cate.select().where(blog_cate.c.cate_id == param['cate_id']).limit(limit).offset(offset)
sql2 = blog_cate.select().where(blog_cate.c.cate_id == param['cate_id'])
if param['cate_name']:
sql = blog_cate.select().where(blog_cate.c.cate_name == param['cate_name']).limit(limit).offset(offset)
sql2 = blog_cate.select().where(blog_cate.c.cate_name == param['cate_name'])
if param['cate_id'] and param['cate_name']:
sql = blog_cate.select().\
where(and_(blog_cate.c.cate_name == param['cate_name'],blog_cate.c.cate_id == param['cate_id'])).\
limit(limit).offset(offset)
sql2 = blog_cate.select().\
where(and_(blog_cate.c.cate_name == param['cate_name'],blog_cate.c.cate_id == param['cate_id']))
# print(sql)
# print(sql2)
result = await SQL.querySql(sql) # sql执行
# print(result)
res = await result.fetchall() # fetchall()/fetchone()/fetchmany()/first()
result2 = await SQL.querySql(sql2) # sql执行
# print(res)
data['data']['data'] = [{'cate_id':i[0],'cate_name':i[1],'cate_title':i[2],'cate_keywords':i[3],'cate_description':i[4],'cate_img':i[5],'cate_order':i[6],'cate_show':i[7],'cate_parent_id':i[8],'cate_icon':i[9]} for i in res] if res else []
data['data']['total'] = result2.rowcount
# print(data['data'])
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def catelevelsArr(self, cate_id):
data = {'code': 0, 'data': None, 'msg': ''}
try:
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_cate.select()
result = await SQL.querySql(sql) # sql执行
res = await result.fetchall(
) # fetchall()/fetchone()/fetchmany()/first()
catelist = [{
'cate_id': i[0],
'cate_parent_id': i[8]
} for i in res] if res else []
arr = returnCateChild(int(cate_id), catelist)
return arr
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
return web.json_response(data)
async def cateMenuAll(self,request,payload):
data = {
'code': 0,
'data': None,
'msg' :''
}
try:
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_cate.select().where(blog_cate.c.cate_parent_id == 0)
result = await SQL.querySql(sql) # sql执行
# print(result)
res = await result.fetchall() # fetchall()/fetchone()/fetchmany()/first()
# print(res)
data['data'] = [{'cate_id':i[0],'cate_name':i[1],'cate_title':i[2],'cate_keywords':i[3],'cate_description':i[4],'cate_img':i[5],'cate_order':i[6],'cate_show':i[7],'cate_parent_id':i[8],'cate_icon':i[9]} for i in res] if res else []
# print(data['data'])
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def articleSelectId(self, request, payload=None):
data = {'code': 0, 'data': {}, 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_article.select().where(
blog_article.c.article_id == param['article_id'])
sql2 = blog_cate.select()
sql3 = 'SELECT tag_id,tag_name FROM blog_tag WHERE tag_id IN (SELECT tag_id FROM blog_tag_and_article WHERE article_id=%s)' % param[
'article_id']
# print(sql)
result = await SQL.querySql(sql) # sql执行
# print(result)
res = await result.fetchone(
) # fetchall()/fetchone()/fetchmany()/first()
result2 = await SQL.querySql(sql2) # sql执行
res2 = await result2.fetchall()
cateArr = [{
'cate_id': i[0],
'cate_name': i[1],
'cate_parent_id': i[8]
} for i in res2] if res else []
# print(res)
tuple1 = (
'article_id',
'user_id',
'cate_id',
'article_title',
'article_keywords',
'article_description',
'article_is_hot',
'article_is_push',
'article_img',
'article_content',
'article_publish_time',
'article_update_time',
'article_browse_count',
'article_like_count',
'article_status',
'article_comment_status',
'article_password',
'article_order',
'article_type',
)
# print(res)
data['data'] = retutnObj(tuple1, res)
if data['data']['cate_id']:
cateItem = [
i for i in cateArr
if i['cate_id'] == data['data']['cate_id']
][0]
if cateItem:
data['data']['cate_name'] = cateItem['cate_name']
cateArr = returnCateArr(data['data']['cate_id'], cateArr)
cateArr.sort()
data['data']['cateArr'] = cateArr
# print(data['data']['cate_id'])
# print(cateArr)
# print(data['data'])
result3 = await SQL.querySql(sql3) # sql执行
res3 = await result3.fetchall()
if res3 and len(res3):
data['data']['tags'] = [{
'tag_id': i[0],
'tag_name': i[1]
} for i in res3]
# data['data']['tags'] = res3
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
data['data'] = None
finally:
return web.json_response(data)