async def tagSearchName(self, request, payload):
data = {'code': 0, 'data': {}, 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
print(param)
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_tag.select().where(
blog_tag.c.tag_name == param['tag_name'])
result = await SQL.querySql(sql) # sql执行
res = await result.fetchone()
print(res)
if res:
data['data'] = {
'tag_id': res[0],
'tag_name': res[1],
}
else:
sql2 = blog_tag.insert(None).values(tag_name=param['tag_name'],
tag_order=10)
result2 = await SQL.querySql(sql2) # sql执行
res2 = result2.lastrowid # fetchall()/fetchone()/fetchmany()/first()/lastrowid
data['data'] = {
'tag_id': res2,
'tag_name': param['tag_name'],
}
print(data['data'])
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def cateUpdate(self,request,payload):
data = {
'code': 0,
'data': '',
'msg' :''
}
try:
param = json.loads(request._payload._buffer[0])["data"]
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_cate.update(None).where(blog_cate.c.cate_id == param['cate_id'])\
.values(
cate_name=param['cate_name'],
cate_title=param['cate_title'],
cate_icon=param['cate_icon'],
cate_keywords=param['cate_keywords'],
cate_description=param['cate_description'],
cate_img=param['cate_img'],
cate_show=param['cate_show'],
cate_order=param['cate_order']
)
result = await SQL.querySql(sql) # sql执行
# print(result)
res = result.rowcount # fetchall()/fetchone()/fetchmany()/first()
# print(res)
data['data'] = res
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def login(self, request, payload={}):
data = {'code': 0, 'data': '', 'msg': ''}
param = json.loads(request._payload._buffer[0])["data"]
username = param['username']
password = jm_md5('blog', param['password'])
Header = {
"alg": "HS256",
"typ": "JWT",
}
timenow = datetime.now().timestamp()
Payload = {
'username': None, # 用户名
'user_id': None, #用户id
}
timenow = datetime.now().timestamp()
overtime = timenow + 60 * 60 * 60 # JWT过期时间
try:
SQL = SQLcontroller() # 创建sql操作对象
querysql = blog_user.select().where(
and_(blog_user.c.user_name == username,
blog_user.c.user_password == password)) # sql 语句
print(querysql)
result = await SQL.querySql(querysql) # sql执行
res = await result.first()
print(res)
# data['data'] = {'user_id':i[0],'user_name':i[1],'user_registered_time':formatDate(i[7],'Y-m-d H-M-S')} for i in res]
if res: # 有结果
data['data'] = {
'user_id': res[0],
'user_name': res[1],
'user_registered_time': formatDate(res[7], 'Y-m-d H-M-S')
}
Payload['username'] = res[1]
Payload['user_id'] = res[0]
else: # 无结果
data['code'] = -100
data['msg'] = '账号或密码错误!'
except Exception as e: # 异常
print(e)
data['code'] = -100
data['msg'] = str(e)
finally:
secret = 'pyblog'
Signature = hmac_sha256(
base_64(json.dumps(Header, indent=2)) + "." +
base_64(json.dumps(Payload, indent=2)), secret)
JWT = base_64(json.dumps(Header, indent=2)) + '.' + base_64(
json.dumps(Payload,
indent=2)) + '.' + Signature + '.' + base_64(
str(overtime))
headers = {'Authorization': JWT}
print(data)
if data['code'] == 0:
print(web.json_response(data, headers=headers))
return web.json_response(data, headers=headers)
else:
return web.json_response(data)
async def tagSelect(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_tag.select().limit(limit).offset(offset)
sql2 = blog_tag.select()
if param['tag_id']:
sql = blog_tag.select().where(
blog_tag.c.tag_id == param['tag_id']).limit(limit).offset(
offset)
sql2 = blog_tag.select().where(
blog_tag.c.tag_id == param['tag_id'])
if param['tag_name']:
sql = blog_tag.select().where(
blog_tag.c.tag_name == param['tag_name']).limit(
limit).offset(offset)
sql2 = blog_tag.select().where(
blog_tag.c.tag_name == param['tag_name'])
if param['tag_id'] and param['tag_name']:
sql = blog_tag.select().\
where(and_(blog_tag.c.tag_name == param['tag_name'],blog_tag.c.tag_id == param['tag_id'])).\
limit(limit).offset(offset)
sql2 = blog_tag.select().\
where(and_(blog_tag.c.tag_name == param['tag_name'],blog_tag.c.tag_id == param['tag_id']))
print(sql)
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'] = [{
'tag_id': i[0],
'tag_name': i[1],
'tag_order': i[2],
'tag_img': i[3],
'tag_description': i[4]
} 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 updateSiteIndex(self, request, payload={}):
data = {'code': 0, 'data': '', 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
stmt = blog_site.update(None).where(
blog_site.c.site_key == bindparam('oldname')).values(
site_value=bindparam('newname'))
sqldata = [
{
'oldname': 'site_name',
'newname': param['site_name']
},
{
'oldname': 'site_url',
'newname': param['site_url']
},
{
'oldname': 'site_title',
'newname': param['site_title']
},
{
'oldname': 'site_keywords',
'newname': param['site_keywords']
},
{
'oldname': 'site_descript',
'newname': param['site_descript']
},
{
'oldname': 'site_email',
'newname': param['site_email']
},
{
'oldname': 'site_copy',
'newname': param['site_copy']
},
]
result = await SQL.queryManySql(stmt, sqldata) # sql执行
print(result)
res = result.rowcount # fetchall()/fetchone()/fetchmany()/first()
print(res)
data['data'] = res
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def addArticleTag(self, article_id, tagList, request):
data = {'code': 0, 'data': None, 'msg': ''}
try:
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_tag_and_article.insert(None)
dataList = [{
'article_id': int(article_id),
'tag_id': int(i['tag_id'])
} for i in tagList]
result = await SQL.queryManySql(sql, dataList) # sql执行
res = await result.rowcount(
) # fetchall()/fetchone()/fetchmany()/first()
return res
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
async def tagDelete(self, request, payload):
data = {'code': 0, 'data': '', 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_tag.delete(None).where(
blog_tag.c.tag_id == param['tag_id'])
result = await SQL.querySql(sql) # sql执行
# print(result)
res = result.rowcount # fetchall()/fetchone()/fetchmany()/first()
# print(res)
data['data'] = res
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def siteIndex(self, request, payload):
data = {'code': 0, 'data': '', 'msg': ''}
try:
SQL = SQLcontroller() # 创建sql操作对象
querysql = blog_site.select() # sql 语句
print(querysql)
result = await SQL.querySql(querysql) # sql执行
res = await result.fetchall(
) # fetchall()/fetchone()/fetchmany()/first()
print(res)
obj = {}
for i in res:
obj[i[1]] = i[3]
data['data'] = obj
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def sql(request):
data = {
'code': 0,
'data': None,
'msg' :''
}
try:
SQL = SQLcontroller() # 创建sql操作对象
querysql = blog_site.select() # sql 语句
result = await SQL.querySql(querysql) # sql执行
res = await result.fetchall()
data['data'] = [{'id':i[0],'site_key':i[1],'site_name':i[2],'site_value':i[3]} for i in res]
except Exception as e :
print(e)
data['code'] = -100
data['msg'] = str(e)
finally:
print(data)
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 addArticle(self, request, payload):
data = {'code': 0, 'data': '', 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
print(param)
SQL = SQLcontroller() # 创建sql操作对象
timenow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
print(timenow)
cate_id = param['cate_id'] if param['cate_id'] else None
# sql 语句
sql = blog_article.insert(None).\
values(
user_id=payload['user_id'],
cate_id=cate_id,
article_title=param['article_title'],
article_keywords=param['article_keywords'],
article_description=param['article_description'],
article_is_hot=param['article_is_hot'],
article_is_push=param['article_is_push'],
article_img=param['article_img'],
article_content=param['article_content'],
article_publish_time=timenow,
article_update_time=timenow,
article_browse_count=0,
article_like_count=0,
article_status=param['article_status'],
article_comment_status=0,
article_order=param['article_order'],
article_type=param['article_type'],
)
# print(sql)
result = await SQL.querySql(sql) # sql执行
res = result.lastrowid # fetchall()/fetchone()/fetchmany()/first()
print(param['tags'])
if res and len(param['tags']):
res = await self.addArticleTag(res, param['tags'], request)
# print(res)
data['data'] = res
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
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 addTag(self, request, payload):
data = {'code': 0, 'data': '', 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
print(param)
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_tag.insert(None).values(tag_name=param['tag_name'],
tag_order=10)
# print(sql)
result = await SQL.querySql(sql) # sql执行
res = result.lastrowid # fetchall()/fetchone()/fetchmany()/first()/lastrowid
# print(res)
data['data'] = {
'tag_id': res,
'tag_name': param['tag_name'],
}
except Exception as e:
data['code'] = -100
data['msg'] = str(e)
finally:
return web.json_response(data)
async def addCate(self,request,payload):
data = {
'code': 0,
'data': '',
'msg' :''
}
try:
param = json.loads(request._payload._buffer[0])["data"]
print(param)
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_cate.insert(None).values(cate_name=param['cate_name'],cate_order=10,cate_show=1,cate_parent_id=param['cate_parent_id'])
# print(sql)
result = await SQL.querySql(sql) # sql执行
res = result.rowcount # fetchall()/fetchone()/fetchmany()/first()
# print(res)
data['data'] = res
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)
async def articleSelect(self, request, payload=None):
data = {'code': 0, 'data': {}, 'msg': ''}
try:
param = json.loads(request._payload._buffer[0])["data"]
limit = param['pageSize']
offset = (param['pageIndex'] - 1) * param['pageSize']
if 'cateArr' in param:
cateArr = tuple(param['cateArr'].split(',')) if len(
param['cateArr']) else ()
else:
cateArr = ()
SQL = SQLcontroller() # 创建sql操作对象
# sql 语句
sql = blog_article.select().order_by(
blog_article.c.article_update_time.desc()).limit(limit).offset(
offset)
sql2 = blog_article.select().order_by(
blog_article.c.article_update_time.desc())
# if param['article_id']:
# sql = blog_article.select().where(blog_article.c.article_id == param['article_id']).limit(limit).offset(offset)
# sql2 = blog_article.select().where(blog_article.c.article_id == param['article_id'])
if ('article_title' in param) and param['article_title']:
sql = blog_article.select().where(
blog_article.c.article_title ==
param['article_title']).order_by(
blog_article.c.article_update_time.desc()).limit(
limit).offset(offset)
sql2 = blog_article.select().where(
blog_article.c.article_title == param['article_title'])
if ('cate_id' in param) and param['cate_id']:
cateArr = await self.catelevelsArr(param['cate_id'])
# cate_id
if len(cateArr):
sql = blog_article.select().where(
blog_article.c.cate_id.in_(cateArr)).order_by(
blog_article.c.article_update_time.desc()).limit(
limit).offset(offset)
sql2 = blog_article.select().where(
blog_article.c.cate_id.in_(cateArr)).order_by(
blog_article.c.article_update_time.desc())
if len(cateArr) and ('article_title'
in param) and param['article_title']:
sql = blog_article.select().\
where(and_(blog_article.c.article_title == param['article_title'],blog_article.c.cate_id.in_(cateArr))).order_by(blog_article.c.article_update_time.desc()).\
limit(limit).offset(offset)
sql2 = blog_article.select().\
where(and_(blog_article.c.article_title == param['article_title'],blog_article.c.cate_id.in_(cateArr))).order_by(blog_article.c.article_update_time.desc())
# print(sql)
result = await SQL.querySql(sql) # sql执行
# print(result)
res = await result.fetchall(
) # fetchall()/fetchone()/fetchmany()/first()
result2 = await SQL.querySql(sql2) # sql执行
# 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']['data'] = [retutnObj(tuple1, i)
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)