def check_wx(user_open_id):
"""根据用户微信id和密码获取信息"""
message = {}
session = my_db.sql_session()
columns = get_columns()
sql = "select " + ",".join(
columns) + " from user_info where user_open_id='{}'".format(
user_open_id)
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchone()
if result is None:
message['message'] = "not exists"
else:
result = my_db.str_format(result)
result = dict(zip(columns, result))
if result['user_status'] == 1:
message["message"] = "exists"
message['data'] = result
else:
message['message'] = "账户已冻结"
except Exception as e:
print(e)
message['message'] = 'fail'
finally:
session.close()
return message
def page(index=1, length=30):
"""分页查询用户，后台管理用，index是页码，length是每页多少条记录"""
message = {"message": "success"}
if isinstance(index, (int, str)) and isinstance(length, (int, str)):
try:
index = index if isinstance(index, int) else int(index)
length = length if isinstance(length, int) else int(length)
session = my_db.sql_session()
columns = get_columns()
sql = "select " + ",".join(columns) + (
" from user_info order by create_date desc "
"limit {},{}".format((index - 1) * length, length))
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
message['data'] = data
except Exception as e:
print(e)
message['message'] = "查询错误"
finally:
session.close()
except TypeError:
message['message'] = "参数错误"
else:
raise TypeError("参数只能是str或者int")
message['message'] = "参数类型错误"
return message
def topic_detail_user(top_id):
"""用户根据id获取单个话题的详细内容,参数中必须要有一个top_id"""
ses = my_db.sql_session()
message = {'message': "success"}
child_sql = "(SELECT CONCAT_WS(' vs ',SUM(support_a),SUM(support_b)) " \
"FROM vote_count WHERE vote_count.topic_id=topic_info.top_id)" # 查询ab支持度的子查询
sql = "SELECT top_id,top_title,top_content,viewpoint_a,viewpoint_b,can_show,img_url_a,img_url_b," \
"topic_info.channel_id,channel_info.channel_name,topic_info.class_id," \
"class_info.class_name,end_date,begin_date," \
"user_info.user_nickname,{} FROM topic_info,channel_info,class_info,user_info " \
"WHERE user_info.user_id=topic_info.author " \
"AND channel_info.channel_id=topic_info.channel_id and can_show=1 and " \
"class_info.class_id=topic_info.class_id AND " \
"top_id='{}'".format(child_sql, top_id)
columns = [
'top_id', 'top_title', 'top_content', 'viewpoint_a', 'viewpoint_b',
'can_show', 'img_url_a', 'img_url_b', 'channel_id', 'channel_name',
'class_id', 'class_name', 'end_date', 'begin_date', 'author', "a_vs_b"
]
proxy_result = ses.execute(sql)
result = proxy_result.fetchone()
result = my_db.str_format(result)
ses.close()
data = dict(zip(columns, result))
message['data'] = data
return message
def get_user_info(user_id, user_password):
"""根据用户id和密码获取信息"""
message = {"message": "success"}
if my_db.validate_arg(user_password) and my_db.validate_arg(user_password):
session = my_db.sql_session()
columns = get_columns()
sql = "select " + ",".join(
columns) + " from user_info where user_id='{}'".format(user_id)
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchone()
if result is None:
message['message'] = "此ID不存在"
else:
result = my_db.str_format(result)
result = dict(zip(columns, result))
if user_password.lower() == result['user_password'].lower():
if result['user_status'] == 1:
message['data'] = result
else:
message['message'] = "账户已冻结"
else:
message["message"] = "密码错误"
except Exception as e:
print(e)
message['message'] = '查询失败'
finally:
session.close()
else:
message['message'] = "参数错误"
return message
def fetch_joined_topics(user_id):
"""根据用户id获取参加过的话题"""
session = my_db.sql_session()
columns = get_columns()
sql = "select * from topic_info where top_id in " \
"(select topic_id from vote_count where user_id = {})" \
.format(user_id)
data = []
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
for x in data:
x.update({
"view_count": get_view_count(x['top_id']),
"vote_count": sum_vote_count(x['top_id'])
})
else:
data = []
except Exception as e:
print(e)
finally:
session.close()
return data
def login(admin_name, admin_password):
"""管理员登录"""
message = {"message": "success"}
if my_db.validate_arg(admin_name) and my_db.validate_arg(admin_password):
session = my_db.sql_session()
columns = get_columns()
sql = "select " + ",".join(
columns) + " from admin_info where admin_name='{}'".format(
admin_name)
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchone()
if result is None:
message['message'] = "管理员账户不存在"
else:
result = my_db.str_format(result)
result = dict(zip(columns, result))
if admin_password.lower() == result['admin_password'].lower():
if result['admin_status'] == 1:
message['data'] = result
else:
message['message'] = "此管理员账户已禁用"
else:
message["message"] = "密码错误"
except Exception as e:
print(e)
message['message'] = '查询失败'
finally:
session.close()
else:
message['message'] = "参数错误"
return message
def fetch_created_topics(user_id):
"""根据用户id获取创建过的话题"""
session = my_db.sql_session()
columns = get_columns()
sql = "select * from topic_info where author = {}".format(user_id)
data = []
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
except Exception as e:
print(e)
finally:
session.close()
return data
def fetch_by_user_id(user_id):
"""根据用户id获取其通知信息"""
session = my_db.sql_session()
columns = get_columns()
sql = "select * from notification where user_id = {} order by date desc".format(
user_id)
data = []
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
except Exception as e:
print(e)
finally:
session.close()
return data
def fetch_topics_by_id_list(ids):
"""根据话题id列表获取话题内容"""
session = my_db.sql_session()
columns = get_columns()
id_lists = ",".join(map(lambda x: str(x), ids))
sql = "select * from topic_info where top_id in ({})".format(id_lists)
data = []
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
except Exception as e:
print(e)
finally:
session.close()
return data
def manage_comment(**kwargs):
"""管理用户留言"""
message = {"message": "success"}
ses = my_db.sql_session()
try:
the_type = kwargs.pop("the_type")
if the_type == "add":
"""添加留言"""
kwargs['create_date'] = my_db.current_datetime()
sql = my_db.structure_sql("add", "comment_info", **kwargs)
ses.execute(sql)
ses.commit()
elif the_type == "edit":
comment_id = kwargs.pop("comment_id")
sql = my_db.structure_sql("edit", "comment_info",
"where comment_id={}".format(comment_id),
**kwargs)
ses.execute(sql)
ses.commit()
elif the_type == 'delete':
comment_id = kwargs.pop("comment_id")
sql = my_db.structure_sql("delete", "comment_info",
"where comment_id={}".format(comment_id),
**kwargs)
ses.execute(sql)
ses.commit()
elif the_type == "by_comment_id":
"""根据评论id获取话题"""
comment_id = kwargs.pop("comment_id")
child_sql = "(SELECT CONCAT_WS(' vs ',SUM(up_it),SUM(down)) " \
"FROM up_down_info WHERE up_down_info.comment_id=comment_info.comment_id)" # 查询赞和踩的子查询
column_str = "comment_id,comment_text,comment_author,user_info.user_nickname,user_info.user_img_url," \
"create_date,support_side,topic_id,parent_comment,comment_status,{}".format(child_sql)
sql = "select {} from comment_info,user_info where comment_status>0 user_id=comment_author " \
"and comment_id={}".format(column_str, comment_id)
proxy = ses.execute(sql)
result = proxy.fetchone()
columns = [
'comment_id', 'comment_text', 'user_id', 'user_nickname',
'user_img_url', 'create_date', 'support_side', 'topic_id',
'parent_comment', 'comment_status', "up_vs_down"
]
"""
列名分别代表：评论id，评论内容,用户id，用户昵称，用户头像，发布时间，支持方向，话题id，评论状态
被评论对象id，赞和踩的数量
"""
if result is None:
message['data'] = dict()
else:
message['data'] = dict(zip(columns, result))
elif the_type == "by_topic_id":
"""根据话题id获取话题"""
topic_id = kwargs.pop("topic_id")
child_sql = "(SELECT CONCAT_WS(' vs ',SUM(up_it),SUM(down_it)) " \
"FROM up_down_info WHERE up_down_info.comment_id=comment_info.comment_id)" # 查询赞和踩的子查询
column_str = "comment_id,comment_text,comment_author,user_info.user_nickname,user_info.user_img_url," \
"comment_info.create_date,support_side,topic_id,parent_comment,comment_status,{}".\
format(child_sql)
sql = "select {} from comment_info,user_info where comment_status>0 and user_id=comment_author " \
"and topic_id={}".format(column_str, topic_id)
proxy = ses.execute(sql)
result = proxy.fetchall()
columns = [
'comment_id', 'comment_text', 'user_id', 'user_nickname',
'user_img_url', 'create_date', 'support_side', 'topic_id',
'parent_comment', 'comment_status', "up_vs_down"
]
"""
列名分别代表：评论id，评论内容,用户id，用户昵称，用户头像，发布时间，支持方向，话题id，评论状态
被评论对象id，赞和踩的数量
"""
if len(result) == 0:
message['data'] = list()
else:
result = [dict(zip(columns, x)) for x in result]
message['data'] = result
elif the_type == "all":
"""获取所有评论，用于后台管理"""
topic_id = kwargs.pop("topic_id")
child_sql = "(SELECT CONCAT_WS(' vs ',SUM(up_it),SUM(down)) " \
"FROM up_down_info WHERE up_down_info.comment_id=comment_info.comment_id)" # 查询赞和踩的子查询
column_str = "comment_id,comment_text,comment_author,user_info.user_nickname,user_info.user_img_url," \
"create_date,support_side,topic_id,parent_comment,comment_status,{}".format(child_sql)
sql = "select {} from comment_info,user_info where user_id=comment_author " \
"and topic_id={}".format(column_str, topic_id)
proxy = ses.execute(sql)
result = proxy.fetchall()
columns = [
'comment_id', 'comment_text', 'user_id', 'user_nickname',
'user_img_url', 'create_date', 'support_side', 'topic_id',
'parent_comment', 'comment_status', "up_vs_down"
]
"""
列名分别代表：评论id，评论内容,用户id，用户昵称，用户头像，发布时间，支持方向，话题id，评论状态
被评论对象id，赞和踩的数量
"""
if len(result) == 0:
message['data'] = list()
else:
message['data'] = [dict(zip(columns, x)) for x in result]
elif the_type == "page":
"""分页查询所有评论"""
index = kwargs.get("index")
length = kwargs.get("page_length")
try:
index = int(index)
length = int(length)
columns = [
'comment_id', 'comment_text', 'comment_author',
'comment_info.create_date', 'support_side',
'user_info.user_nickname', 'topic_id',
'topic_info.top_title', 'parent_comment', 'comment_status'
]
sql = "select " + ",".join(columns) + (
" from comment_info,user_info,topic_info where "
"topic_info.top_id=comment_info.topic_id and "
"user_id=comment_author order by create_date desc "
"limit {},{}".format((index - 1) * length, length))
try:
columns = [
'comment_id', 'comment_text', 'comment_author',
'create_date', 'support_side', 'user_nickname',
'topic_id', 'topic_title', 'parent_comment',
'comment_status'
]
proxy_result = ses.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
message['data'] = data
except Exception as e:
print(e)
message['message'] = "查询错误"
except ValueError:
message['message'] = "无效的页码或者步长"
except KeyError:
message['message'] = "不理解的操作"
except Exception as e:
print(e)
message['message'] = "数据库执行错误"
finally:
ses.close()
return message
def manage_topic_admin(**kwargs):
"""后台对话题的管理"""
message = {"message": "success"}
sql_session = my_db.sql_session()
try:
the_type = kwargs.pop("the_type")
if the_type == "add":
"""添加"""
if kwargs['begin_date'] == "":
kwargs['begin_date'] = my_db.current_datetime()
if kwargs['end_date'] == "":
kwargs.pop('end_date')
kwargs['can_show'] = 0
sql = my_db.structure_sql("add", "topic_info", **kwargs)
print(sql)
sql_session.execute(sql)
sql_session.commit()
elif the_type == "edit":
"""编辑"""
try:
top_id = kwargs.pop("top_id")
if kwargs['begin_date'] == "":
kwargs['begin_date'] = my_db.current_datetime()
if kwargs['end_date'] == "":
kwargs['end_date'] = my_db.current_datetime(365)
sql = my_db.structure_sql("edit", "topic_info",
"where top_id='{}'".format(top_id),
**kwargs)
sql_session.execute(sql)
sql_session.commit()
except KeyError:
message['message'] == '错误的话题id'
except Exception as e:
message['message'] = "数据库执行错误"
print(e)
elif the_type == "drop":
"""删除"""
try:
top_id = kwargs.pop("top_id")
sql = "delete from topic_info where top_id={}".format(top_id)
sql_session.execute(sql)
sql_session.commit()
except KeyError:
message['message'] == '错误的话题id'
except Exception as e:
message['message'] = "数据库执行错误"
print(e)
elif the_type == "status":
"""话题状态的调整，审核/拒绝/置顶等"""
try:
top_id = kwargs.pop("top_id")
topic_status = kwargs.pop("topic_status")
sql = "update topic_info set can_show={} where top_id={}".format(
topic_status, top_id)
sql_session.execute(sql)
sql_session.commit()
except KeyError:
message['message'] = '错误的话题id'
except Exception as e:
print(e)
message['message'] = "数据库执行错误"
elif the_type == "single":
"""根据id获取单个话题的内容"""
top_id = kwargs.pop("top_id")
child_sql = "(SELECT CONCAT_WS(' vs ',SUM(support_a),SUM(support_b)) " \
"FROM vote_count WHERE vote_count.topic_id=topic_info.top_id)" # 查询ab支持度的子查询
sql = "SELECT top_id,top_title,top_content,viewpoint_a,viewpoint_b,can_show,img_url_a,img_url_b," \
"topic_info.channel_id,channel_info.channel_name,topic_info.class_id," \
"class_info.class_name,end_date,begin_date," \
"user_info.user_nickname,{} FROM topic_info,channel_info,class_info,user_info " \
"WHERE user_info.user_id=topic_info.author " \
"AND channel_info.channel_id=topic_info.channel_id and " \
"class_info.class_id=topic_info.class_id AND " \
"top_id='{}'".format(child_sql, top_id)
columns = [
'top_id', 'top_title', 'top_content', 'viewpoint_a',
'viewpoint_b', 'can_show', 'img_url_a', 'img_url_b',
'channel_id', 'channel_name', 'class_id', 'class_name',
'end_date', 'begin_date', 'author', "a_vs_b"
]
proxy_result = sql_session.execute(sql)
result = proxy_result.fetchone()
result = my_db.str_format(result)
sql_session.close()
data = dict(zip(columns, result))
message['data'] = data
elif the_type == "page":
"""分页查询话题"""
index = kwargs.get("index")
length = kwargs.get("page_length")
try:
index = int(index)
length = int(length)
columns = get_columns()
sql = "select " + ",".join(columns) + (
" from topic_info order by create_date desc "
"limit {},{}".format((index - 1) * length, length))
try:
proxy_result = sql_session.execute(sql)
result = proxy_result.fetchall()
if len(result) != 0:
result = [my_db.str_format(x) for x in result]
data = [dict(zip(columns, x)) for x in result]
else:
data = []
message['data'] = data
except Exception as e:
print(e)
message['message'] = "查询错误"
except ValueError:
message['message'] = "无效的页码或者步长"
except KeyError as e:
print(e)
message['message'] = "不理解的操作"
except Exception as e:
print(e)
message['message'] = "数据库执行错误"
finally:
sql_session.close()
return message
