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