def edit_admin(**kwargs):
"""修改管理员资料,参数必须是键值对的形式"""
message = {"message": "success"}
if not check_admin_args(**kwargs):
message["message"] = "参数错误"
else:
admin_id = kwargs.pop("admin_id", None)
if admin_id is None or not my_db.validate_arg(admin_id):
message["message"] = "无效的用户ID"
else:
sql = my_db.structure_sql(
"edit",
"admin_info",
query_terms="where admin_id='{}'".format(admin_id),
**kwargs)
session = my_db.sql_session()
try:
session.execute(sql)
session.commit()
except Exception as e:
print(e)
message['message'] = '编辑管理员信息失败'
finally:
session.close()
return message
def mark_as_read(notification_id):
"""根据用户id标记消息为已读"""
session = my_db.sql_session()
sql = "update notification set `read` = 1 where notification_id = {}".format(
notification_id)
session.execute(sql)
session.commit()
def add_admin(**kwargs):
"""增加管理员,参数必须是键值对的形式,"""
message = {"message": "success"}
if not check_admin_args(**kwargs):
message["message"] = "参数错误"
else:
try:
sql = my_db.structure_sql("add", "admin_info", **kwargs)
session = my_db.sql_session()
session.execute(sql)
session.commit()
except sqlalchemy.exc.IntegrityError as e1:
"""
re.findall(r"for key '(.+?)'",str) 是从str中找到匹配以for key 'PRIMARY'")
句子中的PRIMARY,findall方法返回的是数组
"""
print(e1.args)
error_cause = re.findall(r"for key '(.+?)'", e1.args[-1])[0]
if error_cause == "admin_phone":
message["message"] = "管理员手机重复"
else:
print(error_cause)
message['message'] = "添加管理员失败"
except Exception as e2:
print(e2)
print("未知错误")
finally:
session.close()
return message
def edit_user(**kwargs):
"""修改用户资料,参数必须是键值对的形式"""
message = {"message": "success"}
flag, msg = check_user_args(**kwargs)
if not flag:
message["message"] = "参数错误%s" % (msg)
else:
user_id = kwargs.pop("user_id", None)
if user_id is None or len(user_id) != 20:
message["message"] = "无效的用户ID"
else:
sql = my_db.structure_sql(
"edit",
"user_info",
query_terms="where user_id='{}'".format(user_id),
**kwargs)
session = my_db.sql_session()
try:
session.execute(sql)
session.commit()
except Exception as e:
print(e)
message['message'] = '编辑用户信息失败'
finally:
session.close()
return message
def add_user(**kwargs):
"""增加用户,参数必须是键值对的形式,注意，暂时没追加微信登录的方式"""
message = {"message": "success"}
if not check_user_args(**kwargs):
message["message"] = "参数错误"
else:
try:
sql = my_db.structure_sql("add", "user_info", **kwargs)
session = my_db.sql_session()
session.execute(sql)
session.commit()
except sqlalchemy.exc.IntegrityError as e1:
"""
re.findall(r"for key '(.+?)'",str) 是从str中找到匹配以for key 'PRIMARY'")
句子中的PRIMARY,findall方法返回的是数组
"""
print(e1.args)
error_cause = re.findall(r"for key '(.+?)'", e1.args[-1])[0]
if error_cause == "user_phone":
message["message"] = "此手机已注册过"
elif error_cause == "PRIMARY":
message["message"] = "用户ID重复"
else:
print(error_cause)
message['message'] = "注册失败，请联系客服"
except Exception as e2:
print(e2)
print("未知错误")
finally:
session.close()
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 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 up_down(**kwargs):
"""赞和踩"""
message = {"message": "success"}
up_down_str = kwargs.get("up_down_str")
if up_down_str is None:
message['message'] = 'error'
else:
kwargs.pop("up_down_str")
if up_down_str == "up":
kwargs['up_it'] = 1
kwargs['down_it'] = 0
else:
kwargs['up_it'] = 0
kwargs['down_it'] = 1
kwargs['create_date'] = my_db.current_datetime()
ses = my_db.sql_session()
comment_id = kwargs['comment_id']
only_id= kwargs['only_id']
sql = "delete from up_down_info where comment_id={} and only_id='{}'".format(comment_id, only_id)
ses.execute(sql)
ses.commit()
sql = my_db.structure_sql("add", "up_down_info", **kwargs)
ses.execute(sql)
ses.commit()
ses.close()
return message
def change_status(the_type, user_id):
"""启用/禁用/删除账户 ,第一个参数是up/down/delete ，启用或者禁用，第二个是用户id"""
message = {"message": "success"}
if my_db.validate_arg(user_id) and my_db.validate_arg(the_type):
if the_type.strip().lower() == "up":
verb = "启用"
sql = "update user_info set user_status=1 where user_id='{}'".format(
user_id)
elif the_type.strip().lower() == "delete":
verb = "删除"
sql = "delete from user_info where user_id='{}'".format(user_id)
else:
verb = "禁用"
sql = "update user_info set user_status=0 where user_id='{}'".format(
user_id)
session = my_db.sql_session()
try:
session.execute(sql)
session.commit()
except Exception as e:
print(e)
message['message'] = "{}账户失败".format(verb)
finally:
session.close()
else:
message['message'] = "用户ID错误"
return message
def manage_topic(**kwargs):
"""用户对话题的管理"""
message = {"message": "success"}
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['end_date'] = my_db.current_datetime(365)
kwargs['can_show'] = 0
sql = my_db.structure_sql("add", "topic_info", **kwargs)
sql_session = my_db.sql_session()
print(sql)
sql_session.execute(sql)
sql_session.commit()
sql_session.close()
elif the_type == "edit":
pass
except KeyError:
message['message'] = "不理解的操作"
except Exception as e:
message['message'] = "数据库执行错误"
print(e)
finally:
return message
def index_topic_list():
"""获取首页的各频道的话题列表，返回的是是字典
字典以channel_id为ｋｅｙ，值是话题字典的数组，排序以优先级＋发布时间来排序
这里不包含置顶帖子，置顶帖子会在视图函数中与此结果重新组合。
"""
cache = my_db.cache
key = "index_topic_dict"
index_topic_dict = cache.get(key)
if index_topic_dict is None:
"""从数据库查询"""
channel_id_list = [x['channel_id'] for x in channel_list()]
current_date = my_db.current_datetime()
index_topic_dict = dict() # 结果集容器
columns_str = "channel_id,class_id,top_id,top_title"
columns = columns_str.split(",")
columns.append('view_count')
ses = my_db.sql_session()
for channel_id in channel_id_list:
"""首页查询的结果将不会排除过期的话题／投票"""
sql = "select {0},(select count(view_count.view_id) from view_count where " \
"topic_info.top_id=view_count.topic_id) from topic_info where can_show=1 " \
"and channel_id={1} " \
"order by create_date desc limit 0,5".format(columns_str, channel_id)
proxy = ses.execute(sql)
temp = proxy.fetchall()
result = [dict(zip(columns, x)) for x in temp]
index_topic_dict[channel_id] = result
ses.close()
"""将字典按照左右两列排序，以帖子被浏览总数降序排列"""
pass
cache.set(key, index_topic_dict, timeout=60 * 1)
return index_topic_dict
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 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 vote(**kwargs):
"""投票计数"""
message = {"message": "success"}
kwargs['create_date'] = my_db.current_datetime()
sql = my_db.structure_sql("add", "vote_count", **kwargs)
sql_session = my_db.sql_session()
try:
sql_session.execute(sql)
sql_session.commit()
"""同步道缓存"""
lock = RLock()
lock.acquire()
add_vote_cache(kwargs['topic_id'], kwargs['support_a'],
kwargs['support_b'])
lock.release()
except sqlalchemy.exc.IntegrityError as e1:
"""
re.findall(r"for key '(.+?)'",str) 是从str中找到匹配以for key 'PRIMARY'")
句子中的PRIMARY,findall方法返回的是数组
"""
print(e1.args)
error_cause = re.findall(r"for key '(.+?)'", e1.args[-1])[0]
if error_cause == "only_once":
message["message"] = "你已经投过票了"
else:
print(error_cause)
message['message'] = "投票失败，请联系管理员"
finally:
sql_session.close()
return message
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 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 comment_count():
"""统计所有评论的数量"""
sql_session = my_db.sql_session()
sql = "select count(1) from comment_info"
proxy = sql_session.execute(sql)
result = proxy.fetchone()[0]
sql_session.close()
return result
def save_class(small_class_dict):
"""保存类别信息,目前只能增加和修改，不能删除类别"""
old_dict_raw = get_cache("small_class_dict")
new_dict = small_class_dict
add_list = list() # 存放插入的小类的数组
update_list = list() # 存放更新的小类的数组
drop_list = list() # 存放删除的小类的id的数组
old_list = []
for k, v in old_dict_raw.items():
for i in v:
temp = {
"channel_id": k,
"class_id": i['class_id'],
"class_name": i['class_name']
}
old_list.append(temp)
new_list = [{
"channel_id": new_dict[key]['channel_id'],
"class_id": new_dict[key]['class_id'],
"class_name": key
} for key in new_dict.keys()]
if len(new_list) == 0:
return {"message": "success"}
else:
new_keys = [x['class_id'] for x in new_list]
drop_list = [
x['class_id'] for x in old_list
if str(x['class_id']) not in new_keys
]
old_keys = [x['class_id'] for x in old_list]
for x in new_list:
if x['class_id'] == "":
add_list.append(x)
else:
update_list.append(x)
sql_session = my_db.sql_session()
if len(drop_list) > 0:
for x in drop_list:
sql = my_db.structure_sql("delete", "class_info",
"where class_id={}".format(x))
sql_session.execute(sql)
sql_session.commit()
if len(update_list) > 0:
for x in update_list:
class_id = x.pop("class_id")
sql = my_db.structure_sql("edit", "class_info",
"where class_id={}".format(class_id),
**x)
sql_session.execute(sql)
sql_session.commit()
if len(add_list) > 0:
for x in add_list:
x.pop("class_id")
sql = my_db.structure_sql("add", "class_info", **x)
sql_session.execute(sql)
sql_session.commit()
sql_session.close()
return {"message": "success"}
def add(user_id, detail):
"""对某个用户添加一个新的消息通知。
当遇到评论过千、审核通过时，调用此方法将消息写入数据库。
detail为通知的详细内容。"""
session = my_db.sql_session()
sql = "insert into zvoter.notification (`user_id`, `detail`, `read`, `date`) values ('{}', '{}', {}, '{}')".\
format(user_id, detail, 0, my_db.current_datetime())
session.execute(sql)
session.commit()
def __get_view_count(topic_id):
"""低级，根据话题id获取话题的浏览人数"""
sql = "select count(1) from view_count where topic_id='{}'".format(
topic_id)
ses = my_db.sql_session()
proxy = ses.execute(sql)
result = proxy.fetchone()[0]
ses.close()
return result
def __init__(self):
"""使用数据库中最大的手机号码初始化内部存储的伪手机号码值"""
session = my_db.sql_session()
sql = "SELECT MAX(user_phone) FROM zvoter.user_info"
result = session.execute(sql)
if result is not None:
maxphone = int(result.fetchone()[0])
if self.dumb_phone_num < maxphone:
self.dumb_phone_num = maxphone
def __get_banner():
"""低级方法从数据库查询所有的banner配置信息,返回字典的数组"""
sql = "select {} from banner_info order by order_index".format(column_str) # 以人工排序
ses = my_db.sql_session()
proxy = ses.execute(sql)
result = proxy.fetchall()
columns = column_str.split(",")
result = [dict(zip(columns, x)) for x in result]
ses.close()
return result
def __add_view_count(topic_id, only_id, from_ip, browser_type):
"""低级方法。对页面浏览进行计数"""
message = {'message': "success"}
sql = "insert into view_count(topic_id,only_id,from_ip,browser_type,create_date) " \
"values('{0}','{1}','{2}','{3}','{4}')".format(topic_id, only_id, from_ip,
browser_type, my_db.current_datetime())
ses = my_db.sql_session()
ses.execute(sql)
ses.commit()
ses.close()
return message
def __get_vote_count(topic_id):
"""低级方法。根据话题id获取相关的投票人数"""
message = {'message': "success"}
sql = "select sum(vote_count.support_a),sum(vote_count.support_b) from vote_count " \
"where vote_count.topic_id='{}'".format(topic_id)
ses = my_db.sql_session()
proxy = ses.execute(sql)
result = list(proxy.fetchone())
ses.close()
message['support_a'] = result[0]
message['support_b'] = result[1]
return message
def manage_keywords(**kwargs):
"""对搜索热词，keywords,title,description的操作"""
message = {"message": "success"}
ses = my_db.sql_session()
try:
the_type = kwargs.pop('the_type')
if the_type == "add":
sql = my_db.structure_sql("add", 'key_word_info', kwargs)
ses.execute(sql)
ses.commit()
elif the_type == 'delete':
try:
key_word_id = kwargs['key_word_id']
try:
key_word_id = int(key_word_id)
sql = 'select key_word_id from key_word_info'
proxy = ses.execute(sql)
result = [x[0] for x in proxy.fetchall()]
if key_word_id not in result:
message['message'] = '错误的id'
else:
sql = "delete from key_word_info where key_word_id={}".format(key_word_id)
ses.execute(sql)
ses.commit()
except ValueError:
raise ValueError("待删除id格式错误")
message['message'] = '待删除id格式错误'
except KeyError:
raise KeyError("删除key_word_info没有找到key_word_id")
message['message'] == '待删除id无效'
elif the_type == 'edit':
key_word_id = kwargs.get('key_word_id')
if key_word_id is None:
message['message'] = '没有key_word_id'
else:
if key_word_id.isdigit():
adict = {k: v for k, v in kwargs.items() if v != "" or v is not None}
sql = my_db.structure_sql("edit", "key_word_info", "where key_word_id={}".format(key_word_id),
**adict)
ses.execute(sql)
ses.commit()
else:
message['message'] = 'id非法'
except KeyError:
raise KeyError("the_type参数不存在")
message['message'] = '操作类型错误'
finally:
ses.close()
FromDB.yes("key_word_info") # 提醒要从数据库重新加载banner数据，参数为表明
return message
def get_columns(first=False):
"""获取所有的user_info表的列名，只在启动程序时运行一次,参数
first是代表是否第一次启动，如果第一次启动要强制重新加载列名"""
redis_client = my_db.MyRedis.redis_client()
value = redis_client.get("notification_columns")
if value is None or first:
sql = "SHOW columns FROM notification"
session = my_db.sql_session()
proxy_result = session.execute(sql)
session.close()
result = proxy_result.fetchall()
value = json.dumps([x[0] for x in result]).encode()
redis_client.set('notification', value)
return json.loads(value.decode())
def get_class_list():
"""获取所有的小类信息，高级方法。from_db代表是否直接从数据读读取返回的是以
[{"class_id":class_id,"class_name:class_name},'...]这样字典的数组
"""
cache = my_db.cache
key = "small_class_list"
data = cache.get(key)
if data is None:
sql = "SELECT class_id,class_name FROM class_info"
ses = my_db.sql_session()
proxy = ses.execute(sql)
result = proxy.fetchall()
ses.close()
data = [dict(zip(['class_id', 'class_name'], x)) for x in result]
cache.set(key, data, timeout=60 * 15)
return data
def check_phone_registered(phone):
"""查看手机号是否被注册"""
session = my_db.sql_session()
columns = get_columns()
sql = "select " + ",".join(
columns) + " from user_info where user_phone='{}'".format(phone)
try:
proxy_result = session.execute(sql)
result = proxy_result.fetchone()
if result is None:
return False
else:
return True
except Exception as e:
return True
finally:
session.close()
def save_channel(channel_dict):
"""保存频道信息"""
new_dict = channel_dict
if new_dict:
sql_session = my_db.sql_session()()
for k, v in new_dict:
sql = my_db.structure_sql("edit",
"channel_info",
"where channel_id={}".format(k),
channel_name=v)
sql_session.execute(sql)
sql_session.commit()
sql_session.close()
return True
else:
raise ValueError("频道信息错误")
return False
def get_channel():
"""获取所有频道,低级方法"""
channel_sql = "SELECT channel_id,channel_name,channel_img_url FROM channel_info"
sql_session = my_db.sql_session()
proxy_result = sql_session.execute(channel_sql)
channel_result = proxy_result.fetchall()
sql_session.close()
if len(channel_result) == 0:
channel_dict = dict()
else:
channel_dict = [{
"channel_id": x[0],
"channel_name": x[1],
"channel_img_url": x[2]
} for x in channel_result]
set_cache("channel_list", channel_dict)
return channel_dict
