def feedback(username, content, feed_time):
"""
意见反馈
:param username: 用户名
:param content: 反馈内容
:param feed_time: 提交反馈的时间
:return:
"""
db_manager = DBManager()
props = dict()
props['username'] = username
props['content'] = content
props['feed_time'] = feed_time
sql = forEachPlusInsertProps("tb_feedback", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
return True
def agregar_colmado():
if request.method == 'POST':
data = request.get_json()
print(data)
DBManager.insertColmado(data['idusuario'], data['nombre'], data['RNC'],
data['ubicacion'], data['dueno'])
return "true"
def publicarPedido():
if request.method == 'POST':
data = request.get_json()
print(data)
DBManager.insertPedido(data['idcliente'], data['cantidad'],
data['ubicacion'], data['marca'])
return "true"
def ad():
if request.method == 'POST':
data = request.get_json()
print(data)
DBManager.insertAd(data['idusuario'], data['titulo'], data['info'],
data['precio'])
return "true"
def get_latest_id(username, is_new=False):
"""
根据用户名获取tb_account中对应主键ID
:param username: 用户名
:param is_new: 是否是注册用户
:return:
"""
db_manager = DBManager()
if not is_new:
sql0 = "select id from `%s` where username='******'" % ("tb_account", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0:
return result0['id']
sql1 = "SELECT Auto_increment FROM information_schema.tables WHERE table_schema = 'Question_Answer_Platform' " \
"and table_name='%s'" % "tb_account"
cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor1.execute(sql1)
result1 = cursor0.fetchone()
cursor1.close()
db_manager.close()
return result1['Auto_increment']
def query_followers(username):
"""
请求关注/粉丝数量
:param username: 用户名
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql = "select count(*) as follows from `%s` where username='******' and relation_type=0" % ("tb_relation", username)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
if result is None:
follows_num = 0
else:
follows_num = result['follows']
cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql1 = "select count(*) as fans from `%s` where other_username='******' and relation_type=0" % ("tb_relation", username)
cursor_1.execute(sql1)
result1 = cursor_1.fetchone()
cursor_1.close()
if result1 is None:
fans_num = 0
else:
fans_num = result1['fans']
db_manager.close()
return True, follows_num, fans_num
def query_user_points_detail(username):
"""
请求用户积分详情
:param username: 用户名
:return:
"""
db_manager = DBManager()
# 拿到模板数据
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None or len(result0) == 0:
return False, None
identifier = result0['identifier']
if identifier == 0:
score_rule_list = [1, 2, 6, 7]
elif identifier == 1:
score_rule_list = [3, 4, 6, 7]
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql = "select user_operation_type, user_operation_desc, point_value from `%s` as u right " \
"join `%s` as s on u.point_type=s.user_operation_type and username='******'" % ("tb_user_points",
"tb_score_rule_template",
username)
msg = "[in query_user_points_detail] sql=" + sql
logging.info(msg)
cursor_0.execute(sql)
result = cursor_0.fetchall()
cursor_0.close()
db_manager.close()
if result is None:
return False, None
data = []
total_points = 0
for item in result:
tmp = dict()
if item['point_value'] is None and item['user_operation_type'] in score_rule_list:
tmp['point_type'] = item['user_operation_type']
tmp['point_desc'] = item['user_operation_desc']
tmp['point_value'] = 0
data.append(tmp)
elif item['point_value'] is not None:
tmp['point_type'] = item['user_operation_type']
tmp['point_desc'] = item['user_operation_desc']
tmp['point_value'] = item['point_value']
total_points += tmp['point_value']
data.append(tmp)
return True, {
"total_points": total_points,
"point_detail": data
}
def about_us(username):
"""
关于我们
:param username: 用户名
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql = "select content from `%s`" % "tb_about_us_template"
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
db_manager.close()
if result is None:
return False, ""
return True, result
def login():
if request.method == "POST":
data = request.get_json()
print(data)
access = DBManager.login(data['username'], data['password'])
if access == True:
with sqlite3.connect("db/database2.db") as conn:
c = conn.cursor()
c.execute(
"SELECT idusuario,admin,colmado,cliente FROM usuarios where username = (?)",
(data['username'], ))
user_data = c.fetchone()
if user_data[3] == 1:
print("user type: cliente", user_data[3])
tipo = "cliente"
elif user_data[2] == 1:
print("user type: colmado", user_data[2])
tipo = "colmado"
elif user_data[1] == 1:
print("user type: admin", user_data[1])
tipo = "admin"
else:
tipo = "cliente"
return json.dumps({"user_id": user_data[0], "user_type": tipo})
else:
return "0"
def valid_email(email_address):
"""
验证邮箱是否唯一
:param email_address: 邮箱地址
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor()
# 判断邮箱地址是否存在
sql = "select * from `%s` where username='******'" % ("tb_account", email_address)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
db_manager.close()
if result:
return False, "邮箱已被注册"
return True, ""
def get_latest_id(table_name):
"""
根据用户名获取数据表对应主键ID
:param table_name: 数据表名
:return:
"""
db_manager = DBManager()
sql1 = "SELECT Auto_increment FROM information_schema.tables WHERE table_schema = 'Question_Answer_Platform' " \
"and table_name='%s'" % table_name
msg = "[in get_latest_id(dbQuestion)] sql1=" + sql1
logging.info(msg)
cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor1.execute(sql1)
result1 = cursor1.fetchone()
cursor1.close()
db_manager.close()
return result1['Auto_increment']
def reset_user_password(user_name, email):
"""
用户重置密码
:param user_name: 用户名
:param email: 邮箱
:return:
"""
recipient = email
subject = "reset password"
# 重新生成8位长度的密码
from tool.util import random_str
new_password = random_str()
content = "您的新密码: " + new_password + "\n请在24小时内修改默认密码!"
print recipient, subject, content
if recipient and subject and content:
from lib.email import mailnotify
mbres = mailnotify.send_notifymail(recipient, subject, content)
if not mbres:
return False, "发送失败"
else:
return False, "请求参数不正确"
# 邮件发送成功
# 更新用户密码, 添加用户重置记录
db_manager = DBManager()
password = hashlib.sha224(new_password).hexdigest()
sql0 = "update `%s` set password='******' where username='******'" % ("tb_account", password, user_name)
cursor0 = db_manager.conn_r.cursor()
try:
cursor0.execute(sql0)
db_manager.conn_r.commit()
cursor0.close()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
raise Exception
return False, "密码更新失败"
sql1 = "insert into `%s` (username, reset_time) values ('%s', '%s')" % ("tb_reset", user_name, time_now_str())
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(sql1)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
return False, "添加重置记录失败"
db_manager.close()
is_success = True
result = dict()
result['new_password'] = new_password
return is_success, result
def sign_up(username, sign_time):
"""
每日签到
:param username: 用户名
:param sign_time: 签到时间
:return:
"""
db_manager = DBManager()
# 验证用户当天是否已经签到过
sql0 = "select *from `%s` where username='******' and to_days(sign_time)=to_days('%s')" % ("tb_sign", username,
string_toDatetime(sign_time))
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is not None:
return False, "今天已签到过", None
props = dict()
props['username'] = username
props['sign_time'] = sign_time
sql1 = forEachPlusInsertProps("tb_sign", props)
try:
cursor1 = db_manager.conn_r.cursor()
cursor1.execute(sql1)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
return False, "签到失败", None
# 获取系统赠送的积分
user_operation_type = 6
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points']
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False, "签到失败", None
db_manager.close()
msg0 = "[in sign_up] sql_0=" + sql_0
logging.info(msg0)
return True, "签到成功", point_value
def query_collection_question_list(username):
"""
请求用户收藏问题列表
:param username: 用户名
:return:
"""
db_manager = DBManager()
sql = "select tc.question_id, tq.question_content, tq.question_pic_url, tq.question_sound_url, (select count(*) from `tb_answer` where " \
"question_id=tc.question_id) as counts from `tb_question_collection` as tc inner join `tb_question` as tq on " \
"tc.question_id=tq.question_id and tc.collecter_username='******';" % username
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
results = cursor.fetchall()
cursor.close()
db_manager.close()
if results is None or len(results) == 0:
return False, None
return True, results
def signup():
if request.method == "POST":
data = request.get_json()
print(data)
#Validate if username exists before creating it
with sqlite3.connect("db/database2.db") as conn:
c = conn.cursor()
c.execute("SELECT * FROM usuarios where username = (?)",
(data['username'], ))
resultado = c.fetchall()
print(resultado)
if not resultado:
DBManager.insertUser(data['username'], data['password'],
int(data['status']), int(data['admin']),
int(data['colmado']),
int(data['cliente']))
return "true"
else:
return "false"
def follow_other(username, other_username):
"""
关注某人
:param username: 用户名
:param other_username: 其他的用户名
:return:
"""
db_manager = DBManager()
# 校验两者关系
sql0 = "select *from `%s` where (username='******' and other_username='******' and relation_type=0) or (username='******' " \
"and other_username='******' and relation_type=1)" % ("tb_relation", username, other_username, other_username,
username)
msg = "[in follow_other] sql0=" + sql0
logging.info(msg)
cursor0 = db_manager.conn_r.cursor()
result0 = cursor0.execute(sql0)
cursor0.close()
logging.info(result0)
if result0 is not None and result0 != 0:
return False
props = dict()
props['username'] = username
props['other_username'] = other_username
props['relation_type'] = 0
sql = forEachPlusInsertProps("tb_relation", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
# 插入反向关系
props = dict()
props['username'] = other_username
props['other_username'] = username
props['relation_type'] = 1
sql = forEachPlusInsertProps("tb_relation", props)
try:
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True
def connect_question(username, question_id):
"""
收藏问题
:param username: 用户名
:param question_id: 问题ID
:return:
"""
db_manager = DBManager()
# 问题是否存在
sql0 = "select *from `%s` where question_id=%s" % ("tb_question", question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False, "问题不存在"
# 查询是否收藏过该问题
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is not None:
return False, "已收藏过该问题"
sql = "insert into `%s` (question_id, collecter_username, collect_time) values (%s, '%s', '%s')" % ("tb_question_collection",
question_id, username, time_now_str())
msg = "[in connect_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
except Exception:
db_manager.conn_r.rollback()
raise Exception
db_manager.close()
return True, ""
def run():
# *****************************************************
# logger 설정
FORMAT = "[%(asctime)s]:%(levelname)s: %(filename)s | %(funcName)s - %(message)s"
logging.basicConfig(format=FORMAT)
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
# *****************************************************
# environment variables 설정
config = ConfigLoader()
config.init()
config.load()
# *****************************************************
# DB 설정
db_url = "mysql+pymysql://%s:%s@%s:%s/%s?charset=%s" % (
config.DB_USER, config.DB_PASSWORD,
config.DB_HOST, config.DB_PORT,
config.DB_DATABASE, config.DB_CHARSET
)
dao = DBManager.initialize(db_url)
DBManager.initialize_db()
# DB 컨트롤러 인스턴스 생성
db_controller = DBController(dao)
# *****************************************************
# 크롤러 인스턴스 생성
DRIVER_PATH = get_driver_path()
af = afreeca_crawler.AfreecaNoteCrawler(
DRIVER_PATH, db_controller, config, logger)
# 크롤러 시작
af.start()
# 크롤러 종료시, 커넥션 종료
DBManager.dispose()
class DataProcedure():
def __init__(self):
self.cm = ConfigManager()
self.db = DBManager()
def __del__(self):
self.db.close()
def run(self):
sql = self.get_sql()
queue_data = self.cm.get_config('taskqueue')['data']
queue_back = self.cm.get_config('taskqueue')['backup']
r = redis.Redis(
host=self.cm.get_config('redis')['host'],
port=self.cm.get_config('redis')['port']
)
if not r:
print 'Redis服务未启动'
else:
print 'Redis服务正常'
# 处理当前任务
cur_task = r.rpoplpush(queue_data, queue_back)
while cur_task is not None:
# print cur_task
is_success, rows = self.db.save(sql, eval(cur_task))
if is_success:
# 提交成功, 清空备份队列
r.delete(queue_back)
cur_task = r.rpoplpush(queue_data, queue_back)
print '队列中没有要处理的任务'
def get_sql(self):
table_name = '%sresult' % self.cm.get_config('table')[0]['song']['prefix']
sql_data_save = ("INSERT INTO %s " % table_name) + "(`sid`, `author`, `sname`, `counts`, `durl`) " \
"VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE " \
"counts=counts+1;"
return sql_data_save
def change_password(username, old_password, new_password):
"""
修改密码
:param username: 用户名
:param old_password: 旧密码
:param new_password: 新密码
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
# 判断用户是否合法
sql = "select password from `%s` where username='******'" % ("tb_account", username)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
if result is None:
return False, "用户不存在"
tmp_password = result['password']
password = hashlib.sha224(old_password).hexdigest()
if tmp_password != password:
return False, "原密码错误"
props = dict()
prere = dict()
props['password'] = hashlib.sha224(new_password).hexdigest()
prere['username'] = username
sql0 = forEachUpdateProps("tb_account", props, prere)
try:
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
db_manager.conn_r.commit()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
raise Exception
db_manager.close()
return True, "修改密码成功"
def modify_personal_information(username, props=None, options=None):
"""
更新个人信息
:param username: 用户名
:param props: 更新的数据域
:return:
"""
if len(props) > 0:
is_avatar = False
assert isinstance(props, dict)
if options:
assert isinstance(options, dict)
is_avatar = True
props.update(options)
update_str = FormatUpdateStr(props)
sql0 = "update `%s` set %s where username='******'" % ("tb_user", update_str, username)
msg = "[in modify_personal_information] sql0=" + sql0
logging.info(msg)
try:
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql0)
db_manager.conn_r.commit()
cursor_0.close()
except Exception:
db_manager.conn_r.rollback()
cursor_0.close()
db_manager.close()
raise Exception
return False, None
db_manager.close()
if is_avatar:
return True, options
return True, None
elif options:
return True, options
def search_question(username, question_content, cur_page, page_size, grade=None, subject=None):
"""
搜索问题 (按照问题内容搜索)
:param username: 用户名
:param question_content: 问题内容
:param grade: 年级
:param subject: 科目
:return:
"""
db_manager = DBManager()
question_list = []
condition_props = dict()
if grade is not None:
condition_props['question_grade'] = grade
if subject is not None:
condition_props['question_subject'] = subject
# 默认搜索文字
con_str = FormatCondition(condition_props)
# 拿到总条数
signal = False
if con_str is not None and len(con_str) > 0:
signal = True
sql_1 = "select count(*) as counts from `%s` where %s and question_content like '%s%s%s'" % \
("tb_question", con_str, '%', question_content, '%')
else:
sql_1 = "select count(*) as counts from `%s` where question_content like '%s%s%s'" % \
("tb_question", '%', question_content, '%')
msg0 = "[in search_question] sql_1=" + sql_1
logging.info(msg0)
cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_1.execute(sql_1)
tmp = cursor_1.fetchone()
cursor_1.close()
if tmp is None or len(tmp) == 0:
return True, question_list, 0
counts = tmp['counts']
if signal:
sql = "select *from `%s` where %s and question_content like '%s%s%s' order by question_time desc limit %s,%s " % \
("tb_question", con_str, '%', question_content, '%', (cur_page - 1) * page_size, page_size)
else:
sql = "select *from `%s` where question_content like '%s%s%s' order by question_time desc limit %s,%s " % \
("tb_question", '%', question_content, '%', (cur_page - 1) * page_size, page_size)
msg = '[in search_question] sql=' + sql
logging.info(msg)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql)
result = cursor_0.fetchall()
cursor_0.close()
logging.info(result)
if result:
for item in result:
tmp = item.copy()
question_id = item['question_id']
tmp_username = item['question_username']
# 拿到用户信息
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp['avatar_url'] = result0['avatar_url']
tmp['nickname'] = result0['nickname']
# 用户是否收藏过该问题
has_collect = 0
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result_121 = cursor0.fetchone()
cursor0.close()
if result_121:
has_collect = 1
tmp['has_collect'] = has_collect
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", question_id)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
counter = 0
if result0:
counter = result0['counts']
tmp['answer_counts'] = counter
question_list.append(tmp)
db_manager.close()
return True, question_list, counts
def query_user_question_or_answer_list(username, identifier, is_part, cur_page, page_size):
"""
请求用户的问题列表或者回答列表
:param username: 用户名
:param identifier: 身份标志 (0: 学生 1: 教师)
:param is_part: 按照条件搜索 (学生: 问题完成数 教师： 回答采纳数)
:param cur_page: 当前数据分页
:param page_size: 每页显示数据条数
:return:
"""
db_manager = DBManager()
order_key = 'question_time'
table_name = 'tb_question'
question_list = []
counts = 0
# 学生
if identifier == 0:
# 全部搜索
if is_part == 0:
query_str = FormatCondition(props={
"question_username": username
})
# 部分搜索
elif is_part == 1:
query_str = FormatCondition(props={
"question_username": username,
"question_status": 1
})
# 拿到总条数
sql = "select count(*) as counts from `%s` where %s" % (table_name, query_str)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
if result is None or len(result) == 0:
return True, question_list, 0
counts = result['counts']
sql0 = "select *from `%s` where %s order by %s limit %s,%s" % \
(table_name, query_str, order_key, (cur_page - 1) * page_size, page_size)
# 教师
elif identifier == 1:
# 全部搜索
if is_part == 0:
query_str = FormatCondition(props={
"answer_username": username
})
# 部分搜索
elif is_part == 1:
query_str = FormatCondition(props={
"answer_username": username,
"is_accepted": 1
})
# 拿到总条数
sql = "select count(question_id) as counts from `tb_answer` where %s and question_id not in (select ask_question_id from " \
"`tb_ask` where be_asked_username='******')" % (query_str, username)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
if result is None or len(result) == 0:
return True, question_list, 0
counts = result['counts']
sql0 = "select *from `%s` where question_id in (select question_id from `tb_answer` where " \
"%s and question_id not in (select ask_question_id from `tb_ask` where be_asked_username='******')) order by " \
"%s limit %s,%s" % (table_name, query_str, username, order_key, (cur_page - 1) * page_size,
page_size)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
results = cursor0.fetchall()
cursor0.close()
for t in results:
# 拿到用户信息
tmp = dict()
tmp_username = t['question_username']
tmp_question_id = t['question_id']
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp['avatar_url'] = result0['avatar_url']
tmp['nickname'] = result0['nickname']
# 用户是否收藏过该问题
has_collect = 0
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, tmp_question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result_120 = cursor0.fetchone()
cursor0.close()
if result_120:
has_collect = 1
tmp['has_collect'] = has_collect
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", tmp_question_id)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
counter = 0
if result0:
counter = result0['counts']
tmp['answer_counts'] = counter
question_list.append(tmp)
db_manager.close()
return True, question_list, counts
def veruser():
return DBManager.veruser()
def answer_question(username, question_id, answer_content, is_original_answer, options=None):
"""
回答问题
:param username: 用户名
:param question_id: 问题ID
:param answer_content: 回答内容
:param is_original_answer: 是否是原回答(1: 原回答 0: 追答)
:return:
"""
db_manager = DBManager()
# 判断用户是否有权限回答问题
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False, "用户不存在"
identifier = result0['identifier']
if identifier != 1:
return False, "没有回答权限"
original_question_id = question_id
# 如果是追答
if is_original_answer == 0:
# 拿到问题原始ID
sql_0 = "select original_question_id from `%s` where ask_question_id=%s" % ("tb_ask", question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
original_question_id = result_0['original_question_id']
# 问题是否已经被解决
sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
question_status = result_0['question_status']
# 问题已经被解决
if question_status == 1:
return False, "问题已经被解决"
props = dict()
props['answer_username'] = username
props['answer_time'] = time_now_str()
props['question_id'] = question_id
props['is_accepted'] = 0
props['answer_content'] = answer_content
if options:
assert isinstance(options, dict)
props.update(options)
sql = forEachPlusInsertProps("tb_answer", props)
msg = "[in answer_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
cursor.close()
except Exception:
db_manager.conn_r.rollback()
cursor.close()
db_manager.close()
raise Exception
# 更新用户积分 (只有原始问题的回答加分, 追答不加分)
if is_original_answer == 1:
user_operation_type = 4
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points']
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
msg_110 = "[in answer_question refresh user points] sql_0=" + sql_0
logging.info(msg_110)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True, "回答成功"
def pedidos():
return DBManager.verpedidos()
def post_question(username, grade, subject, question_content, question_score=0, options=None):
"""
用户提问
:param username: 用户名
:param grade: 年级
:param subject: 学科
:param question_content: 问题内容
:param question_score: 悬赏积分
:param options: 可变字段
:return:
"""
db_manager = DBManager()
# 判断用户是否有权限提问
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False
identifier = result0['identifier']
if identifier != 0:
return False
# 客户端提交的问题内容为空
if question_content is None or question_content == '':
# 获取随机问题内容 (随机数的上限取决于后期配置的模板表的大小)
# 拿到模板表的大小
template_size = 0
sql0 = "select count(*) as counts from `%s`" % "tb_question_content_template"
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0:
template_size = result0['counts']
if template_size < 1:
return False
r_index = randint(1, template_size)
sql0 = "select *from `%s` where content_id=%s" % ("tb_question_content_template", r_index)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0:
question_content = result0['content_value']
# 拿到插入的数据
props = dict()
props['question_username'] = username
props['question_content'] = question_content
props['question_score'] = question_score
props['question_grade'] = grade
props['question_subject'] = subject
props['question_time'] = time_now_str()
props['question_status'] = 0
if options:
assert isinstance(options, dict)
props.update(options)
sql = forEachPlusInsertProps("tb_question", props)
msg = "[in post_question] sql=" + sql
logging.info(msg)
cursor = db_manager.conn_r.cursor()
try:
cursor.execute(sql)
db_manager.conn_r.commit()
except Exception:
db_manager.conn_r.rollback()
raise Exception
# 提问成功, 更新积分
# 更新用户积分
user_operation_type = 2
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points'] - question_score
if point_value >= 0:
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
else:
# 查询是否存在记录
sql_110 = "select *from `%s` where username='******' and point_type=%s" % ("tb_user_points", username,
user_operation_type)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_110)
result_110 = cursor_0.fetchone()
cursor_0.close()
if result_110:
point_value += result_110['point_value']
sql_0 = "update `%s` set point_value=%s where username='******' and point_type=%s" % \
("tb_user_points", point_value, username, user_operation_type)
else:
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s)" % \
("tb_user_points", username, user_operation_type, point_value)
msg_110 = "[in post_question refresh user points] sql_0=" + sql_0
logging.info(msg_110)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True
# loop forever
scheduler.loopForever()
log.info("Stopping pi contoller")
if __name__ == '__main__':
log = setupLogging()
log.info("Starting pi controller")
config = getConfig()
log.info("Using config : " + str(config))
# connect to aws iot
plantMQTTClient = setupIotClient(config)
log.info("IOT client connected sucessfully!")
# Connect to db
dbmanager = DBManager(config)
# Set driver
driver = None
if not debug:
from device.driver import Driver
driver = Driver(config)
# Global scheduler object
scheduler = Scheduler(debug, config, dbmanager, driver)
main()
def verAds():
return DBManager.verAds()
def adopt_answer(username, question_id, answer_id, answer_username):
"""
采纳回答
:param username: 用户名
:param question_id: 原问题ID
:param answer_id: 回答的ID
:param answer_username: 回答者的用户名
:return:
"""
db_manager = DBManager()
# 判断用户是否有权限采纳答案
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
return False, "用户不存在"
identifier = result0['identifier']
if identifier != 0:
return False, "没有采纳权限"
# 问题是否已经被解决
sql_0 = "select question_status, question_score from `%s` where question_id=%s" % ("tb_question", question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
question_status = result_0['question_status']
question_score = result_0['question_score']
# 问题已经被解决
if question_status == 1:
return False, "问题已经被解决"
# 关闭问题
update_prop = dict()
update_prop['question_status'] = 1
update_sql_0 = FormatUpdateStr(update_prop)
sql0 = "update `%s` set %s where question_id=%s" % ("tb_question", update_sql_0, question_id)
cursor0 = db_manager.conn_r.cursor()
try:
cursor0.execute(sql0)
db_manager.conn_r.commit()
cursor0.close()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
raise Exception
return False
# 更新回答的状态
update_prop = dict()
update_prop['is_accepted'] = 1
update_sql_0 = FormatUpdateStr(update_prop)
sql0 = "update `%s` set %s where answer_id=%s" % ("tb_answer", update_sql_0, answer_id)
cursor0 = db_manager.conn_r.cursor()
try:
cursor0.execute(sql0)
db_manager.conn_r.commit()
cursor0.close()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
raise Exception
return False
# 更新教师积分
user_operation_type = 3
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
cursor2.close()
point_value = result2['score_points'] + question_score
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", answer_username, user_operation_type,
point_value)
msg_120 = "[in adopt_answer fresh_user_points] sql_0=" + sql_0
logging.info(msg_120)
cursor2 = db_manager.conn_r.cursor()
try:
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
return False
db_manager.close()
return True, ""
def ask_question(username, answer_id, ask_content, original_question_id, be_asked_username, options=None):
"""
用户追问
:param username: 用户名
:param answer_id: 回答的ID
:param ask_content: 追问内容
:param original_question_id: 原问题ID
:param be_asked_username: 被追问的用户
:return:
"""
db_manager = DBManager()
# 问题是否已经被解决
sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
return False, "问题不存在"
question_status = result_0['question_status']
# 问题已经被解决
if question_status == 1:
return False, "问题已经被解决"
# 检验被提问的用户是否回答过该问题
sql_0 = "select *from `tb_answer` where answer_username='******' and question_id=%s" % (be_asked_username,
original_question_id)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql_0)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None or len(result_0) == 0:
return False, "没有回答该问题"
# 拿到追问顺序ask_order
sql_1 = "select max(ask_order) as ask_order0 from `tb_ask` where be_asked_username='******' and original_question_id=" \
"%s" % (be_asked_username, original_question_id)
cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_1.execute(sql_1)
result_1 = cursor_1.fetchone()
cursor_1.close()
if result_1 is None or len(result_1) == 0:
ask_order = 0
else:
ask_order = result_1['ask_order0'] if result_1['ask_order0'] else 0
# 添加一条追问记录
props = dict()
props['ask_content'] = ask_content
props['ask_time'] = time_now_str()
props['original_question_id'] = original_question_id
props['be_asked_username'] = be_asked_username
props['ask_order'] = ask_order + 1
props['answer_id'] = answer_id
if options:
assert isinstance(options, dict)
props.update(options)
insert_sql = forEachPlusInsertProps("tb_ask", props)
msg0 = "[in ask_question] insert_sql=" + insert_sql
logging.info(msg0)
cursor_2 = db_manager.conn_r.cursor()
try:
cursor_2.execute(insert_sql)
db_manager.conn_r.commit()
cursor_2.close()
except Exception:
db_manager.conn_r.rollback()
cursor_2.close()
db_manager.close()
raise Exception
db_manager.close()
return True, ""
def register(username, password, grade, identifier, nickname, subject, serial_number, options=None):
"""
用户注册 (用户获取登录账户的唯一途径)
1. 学生注册: username, password, grade为必填项, invitation_code为选填项
2. 教师注册: username, password, grade, subject, serial_number为必填项
:param username: 用户名 (需要检查唯一性)
:param password: 密码
:param grade: 年级
:param identifier: 用户类型 (0: 学生 1: 教师)
:param subject: 科目 (教师才有该选项)
:param serial_number: 教师证 (教师才有)
:param options: 可变参数 (由user_type决定)
:return:
"""
db_manager = DBManager()
cursor_0 = db_manager.conn_r.cursor()
# 判断用户名是否存在
sql = "select * from `%s` where username='******'" % ("tb_account", username)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
if result:
db_manager.close()
return False
# 键值对
prop_dict = dict()
prop_dict['username'] = username
prop_dict['identifier'] = identifier
prop_dict['grade'] = grade
prop_dict['nickname'] = nickname
if subject is not None:
prop_dict['subject'] = subject
if serial_number is not None:
prop_dict['serial_number'] = serial_number
if options:
assert isinstance(options, dict)
prop_dict.update(options)
insert_sql = forEachPlusInsertProps('tb_user', prop_dict)
msg0 = "[in register] insert_sql=" + insert_sql
logging.info(msg0)
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(insert_sql)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
password = hashlib.sha224(password).hexdigest()
sql0 = "insert into `%s` (%s, %s) values ('%s', '%s')" % ("tb_account", "username", "password", username, password)
msg = "[in register] sql=" + sql0
logging.info(msg)
cursor0 = db_manager.conn_r.cursor()
try:
cursor0.execute(sql0)
db_manager.conn_r.commit()
cursor0.close()
except Exception:
db_manager.conn_r.rollback()
cursor0.close()
db_manager.close()
db_manager.close()
return True
def query_ask_and_answer_page(answer_id):
"""
获取某个问题的追问追答列表
:param answer_id: 回答的ID
:return:
"""
db_manager = DBManager()
sql0 = "select *from `tb_answer` where answer_id=%s" % answer_id
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
answer = cursor0.fetchone()
cursor0.close()
if answer is None or len(answer) == 0:
return False, "没有该回答"
# 拿到问题信息
question_id = answer['question_id']
sql0 = "select *from `tb_question` where question_id=%s" % question_id
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
data = dict()
answer_list = []
question_username = result0['question_username']
# 用户是否收藏过该问题
has_collect = 0
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
question_username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result_112 = cursor0.fetchone()
cursor0.close()
if result_112:
has_collect = 1
result0['has_collect'] = has_collect
# 拿到提问者的信息
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", question_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result_150 = cursor0.fetchone()
cursor0.close()
result0['avatar_url'] = result_150['avatar_url']
result0['nickname'] = result_150['nickname']
data['question_info'] = result0
data['answers_info'] = answer_list
t_username = answer['answer_username']
# 添加回答信息
tmp_dict = answer.copy()
# 拿到回答者信息
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", t_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp_dict['avatar_url'] = result0['avatar_url']
tmp_dict['nickname'] = result0['nickname']
# 原始问题的回答
tmp_dict['type'] = 0
answer_list.append(tmp_dict)
# 是否有追问
sql2 = "select *from (select *from `tb_ask` where be_asked_username='******' and original_question_id=%s and answer_id=%s) as ta left join `tb_answer` as tb on ta.ask_question_id=" \
"tb.question_id and ta.be_asked_username=tb.answer_username " % (t_username, question_id, answer_id)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
results = cursor2.fetchall()
cursor2.close()
if results is None or len(results) == 0:
return True, data
# 去重
ask_question_id = 0
need_sort_list = []
# 记录需要排序的数据段
start_index = 1
end_index = 1
for t in results:
tmp_ask_dict = dict()
tmp_ask_question_id = t['ask_question_id']
# logging.info("ZZZ###")
# msg = str(tmp_ask_question_id) + "," + str(t['ask_question_id']) + ", " + str(start_index) + "," + str(end_index)
# logging.info(msg)
# 拿到追问者的信息
if ask_question_id != tmp_ask_question_id:
# 更新游标
ask_question_id = tmp_ask_question_id
# 记录需要排序的数据区间
if end_index - start_index >= 1:
t_list = [start_index, end_index]
need_sort_list.append(t_list)
end_index += 1
start_index = end_index
# 拿到提问者的信息
tmp_ask_dict['avatar_url'] = result_150['avatar_url']
tmp_ask_dict['nickname'] = result_150['nickname']
# 追问
tmp_ask_dict['type'] = 1
old_keys = ['answer_id', 'be_asked_username', 'ask_time', 'ask_question_id', 'ask_content',
'ask_pic_url', 'ask_sound_url']
new_keys = ["answer_id", "answer_username", "answer_time", "question_id", "answer_content",
"answer_pic_url", "answer_sound_url"]
tmp_ask_dict['is_accepted'] = 0
copy_dict_by_keys_with_new_keys(old_keys, new_keys, t, tmp_ask_dict)
answer_list.append(tmp_ask_dict)
else:
# 处理多个追答的情形
end_index += 1
if t['question_id'] is None:
if end_index - start_index >= 1:
need_sort_list.append([start_index, end_index])
start_index = end_index
else:
tmp_answer_dict = dict()
# 拿到回答者的信息
tmp_username = t['answer_username']
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp_answer_dict['avatar_url'] = result0['avatar_url']
tmp_answer_dict['nickname'] = result0['nickname']
# 追答
tmp_answer_dict['type'] = 2
copy_dict_by_keys(['answer_username', 'answer_time', 'question_id', 'is_accepted', 'answer_content',
'answer_pic_url', 'answer_sound_url', 'answer_id'], t, tmp_answer_dict)
answer_list.append(tmp_answer_dict)
if end_index - start_index >= 1:
need_sort_list.append([start_index, end_index])
# logging.info('########start')
# logging.info(answer_list)
# 进行数据排序 (维度选择answer_time)
if need_sort_list and answer_list:
# logging.info('to sort')
logging.info(need_sort_list)
for v in need_sort_list:
# logging.info("hhhh")
# logging.info(v)
for i in xrange(v[0], v[1]):
for j in xrange(v[0], v[1]-i):
if answer_list[j]['answer_time'] > answer_list[j+1]['answer_time']:
# test swap
# logging.info("before swap####")
t_data = answer_list[j+1]
logging.info(t_data)
answer_list[j+1] = answer_list[j]
answer_list[j] = t_data
logging.info(answer_list[j+1])
# logging.info('########end')
logging.info(answer_list)
db_manager.close()
return True, data
def login(username, password, identifier):
"""
用户登录 (学生, 教师登录入口)
:param username: 用户名
:param password: 密码
:return:
"""
password = hashlib.sha224(password).hexdigest()
db_manager = DBManager()
sql = "select *from `tb_account` as ta inner join `tb_user` as tu on ta.username=tu.username and ta.username='******'" \
" and ta.password='******' and tu.identifier=%s" % (username, password, identifier)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
is_ok = False
data = None
if result:
# 是否为新用户登录
is_new_user = False
sql = "select *from `%s` where username='******'" % ("tb_user_log", username)
cursor_0 = db_manager.conn_r.cursor()
cursor_0.execute(sql)
result_0 = cursor_0.fetchone()
cursor_0.close()
if result_0 is None:
is_new_user = True
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select *from `%s` where username='******'" % ("tb_user", username)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
is_ok = True
data = result0
# 拿到签到数据
sql1 = "select *from `%s` where username='******' and to_days(sign_time)=to_days(now())" % ("tb_sign", username)
cursor1 = db_manager.conn_r.cursor()
cursor1.execute(sql1)
result1 = cursor1.fetchone()
cursor1.close()
has_sign_today = 0
if result1 is not None:
has_sign_today = 1
data['has_sign_today'] = has_sign_today
# 如果是新用户登录, 更新积分
if is_new_user:
user_operation_type = 1
sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template",
user_operation_type)
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
result2 = cursor2.fetchone()
point_value = result2['score_points']
sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \
" point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type,
point_value)
try:
cursor2 = db_manager.conn_r.cursor()
cursor2.execute(sql_0)
db_manager.conn_r.commit()
cursor2.close()
except Exception:
db_manager.conn_r.rollback()
cursor2.close()
db_manager.close()
raise Exception
# 记录用户登录日志
sql_0 = "insert into `%s` (username, login_time) values ('%s', '%s') ON DUPLICATE KEY UPDATE" \
" login_time=login_time" % ("tb_user_log", username, time_now_str())
cursor1 = db_manager.conn_r.cursor()
try:
cursor1.execute(sql_0)
db_manager.conn_r.commit()
cursor1.close()
except Exception:
db_manager.conn_r.rollback()
cursor1.close()
db_manager.close()
raise Exception
db_manager.close()
return is_ok, data
def query_collect_question_list(username, cur_page, page_size):
"""
请求用户收藏的问题列表
:param username: 用户名
:param cur_page: 当前数据分页
:param page_size: 每页显示数据条数
:return:
"""
db_manager = DBManager()
question_list = []
# 获取总页数
sql_0 = "select count(*) as counts from `%s` where collecter_username='******'" % ("tb_question_collection", username)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
tmp = cursor_0.fetchone()
cursor_0.close()
if tmp is None or len(tmp) == 0:
return True, question_list, 0
counts = tmp['counts']
sql0 = "select *from `%s` where collecter_username='******' order by collect_time limit %s,%s" % \
("tb_question_collection", username, (cur_page - 1) * page_size, page_size)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
results = cursor0.fetchall()
cursor0.close()
for t in results:
# 拿到用户信息
tmp = dict()
tmp_username = t['collecter_username']
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp['avatar_url'] = result0['avatar_url']
tmp['nickname'] = result0['nickname']
# 拿到问题信息
tmp_question_id = t['question_id']
sql0 = "select *from `%s` where question_id=%s" % ("tb_question", tmp_question_id)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
if result0 is None:
continue
tmp.update(result0)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", tmp_question_id)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
counter = 0
if result0:
counter = result0['counts']
tmp['has_collect'] = 1
tmp['answer_counts'] = counter
question_list.append(tmp)
db_manager.close()
return True, question_list, counts
def query_user_question_detail(username, question_id):
"""
请求用户的问题详情
:param username: 用户名
:param question_id: 问题ID
:return:
"""
db_manager = DBManager()
sql0 = "SELECT tq.question_id, question_username, avatar_url, nickname, question_grade, question_subject, " \
"question_content, question_pic_url, question_sound_url, question_time, question_status, question_score from `tb_question` as tq inner join `tb_user` as tu on " \
"tq.question_username=tu.username and tq.question_id=%s and tq.question_username='******';" % (question_id,
username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
# 没有该问题
if result0 is None or len(result0) == 0:
return False, None
data = dict()
answer_list = []
# 用户是否收藏过该问题
has_collect = 0
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result_110 = cursor0.fetchone()
cursor0.close()
if result_110:
has_collect = 1
result0['has_collect'] = has_collect
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result_150 = cursor0.fetchone()
cursor0.close()
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", question_id)
cursor0.execute(sql0)
result_1000 = cursor0.fetchone()
cursor0.close()
counter = 0
if result_1000:
counter = result_1000['counts']
result0['answer_counts'] = counter
result0['avatar_url'] = result_150['avatar_url']
result0['nickname'] = result_150['nickname']
data['question_info'] = result0
data['answers_info'] = answer_list
# 找出回答列表(根据回答时间排序)
sql1 = "select *from `tb_answer` where question_id=%s order by answer_time" % question_id
msg0 = "[in query_user_question_detail] sql1=" + sql1
logging.info(msg0)
cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor1.execute(sql1)
answers = cursor1.fetchall()
cursor1.close()
# 没有回答
if answers is None or len(answers) == 0:
return True, data
for a_answer in answers:
t_username = a_answer['answer_username']
# 添加回答信息
tmp_dict = a_answer.copy()
# 拿到回答者信息
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", t_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp_dict['avatar_url'] = result0['avatar_url']
tmp_dict['nickname'] = result0['nickname']
# 是否有追问
sql2 = "select *from (select *from `tb_ask` where be_asked_username='******' and original_question_id=%s and answer_id=%s) as ta left join `tb_answer` as tb on ta.ask_question_id=" \
"tb.question_id and ta.be_asked_username=tb.answer_username" % (t_username, question_id,
a_answer['answer_id'])
cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor2.execute(sql2)
results = cursor2.fetchall()
cursor2.close()
if results is None or len(results) == 0:
# 没有追问
tmp_dict['counts'] = 1
answer_list.append(tmp_dict)
continue
counts = 0
ask_question_id = 0
for t in results:
tmp_ask_question_id = t['ask_question_id']
if ask_question_id != tmp_ask_question_id:
counts += 1
ask_question_id = tmp_ask_question_id
# 是否有回答
if t['question_id']:
counts += 1
tmp_dict['counts'] = counts
answer_list.append(tmp_dict)
db_manager.close()
return True, data
def query_user_question_list(username, cur_page, page_size):
"""
查询用户问题列表
:param username: 用户名
:return:
"""
db_manager = DBManager()
sql = "select *from `%s` where username='******'" % ("tb_user", username)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
question_list = []
if result is None or len(result) == 0:
return False, None, None
identifier = result['identifier']
grade = result['grade']
if identifier == 0:
# 获取总页数
sql_0 = "select count(*) as counts from `%s` where question_grade=%s" % ("tb_question", grade)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
tmp = cursor_0.fetchone()
cursor_0.close()
if tmp is None or len(tmp) == 0:
return True, question_list, 0
counts = tmp['counts']
sql1 = "select *from `%s` where question_grade=%s order by question_time desc limit %s, %s" % \
("tb_question", grade, (cur_page - 1) * page_size, page_size)
elif identifier == 1:
subject = result['subject']
# 获取总页数
sql_0 = "select count(*) as counts from `%s` where question_grade=%s and question_subject=%s" % \
("tb_question", grade, subject)
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor_0.execute(sql_0)
tmp = cursor_0.fetchone()
cursor_0.close()
if tmp is None or len(tmp) == 0:
return True, question_list, 0
counts = tmp['counts']
sql1 = "select *from `%s` where question_grade=%s and question_subject=%s order by question_time desc limit %s, %s" % \
("tb_question", grade, subject, (cur_page - 1) * page_size, page_size)
msg0 = "[in query_user_question_list] sql1=" + sql1
logging.info(msg0)
cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor1.execute(sql1)
result1 = cursor1.fetchall()
cursor1.close()
if result1:
for item in result1:
# 拿到用户信息
tmp = dict()
tmp_username = item['question_username']
question_id = item['question_id']
sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username)
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
tmp['avatar_url'] = result0['avatar_url']
tmp['nickname'] = result0['nickname']
tmp.update(item)
# 用户是否收藏过该问题
has_collect = 0
sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection",
username, question_id)
cursor0 = db_manager.conn_r.cursor()
cursor0.execute(sql0)
result_119 = cursor0.fetchone()
cursor0.close()
if result_119:
has_collect = 1
tmp['has_collect'] = has_collect
cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql0 = "select count(*) as counts from `%s` where question_id='%s'" % ("tb_answer", question_id)
cursor0.execute(sql0)
result0 = cursor0.fetchone()
cursor0.close()
counter = 0
if result0:
counter = result0['counts']
tmp['answer_counts'] = counter
question_list.append(tmp)
db_manager.close()
return True, question_list, counts
class Baidu():
def __init__(self):
self.cm = ConfigManager()
self.db = DBManager()
def __del__(self):
self.db.close()
def get_song_nums(self, *args, **kwargs):
"""
获取实际歌曲数，存在同歌曲名，不同sid的情形，故需要去重
:param args: 查询的集合域
:param kwargs: 过滤条件，可以取值歌手，歌名或者其他
:return:
"""
table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix']
category = ', '.join(args)
filter_key = kwargs.keys()[0]
filter_value = kwargs.get(filter_key)
sql_search = ('SELECT COUNT(DISTINCT %s) FROM %s ' % (category, table)) + 'WHERE %s = \'%s\'' % (filter_key,
filter_value)
data = self.db.query(sql_search)
return data[0][0]
def searchBySinger(self, singer):
"""
根据歌曲名称查询
优先搜索数据库, 若找到, 直接返回该数据
提示用户是否仍要继续下载
否则, 联网搜索, 并将新数据存入数据库
:param singer: 歌手
:return:
"""
table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix']
sql_search = ('SELECT sname, durl FROM %s ' % table) + 'WHERE author = \'%s\'' % singer
data = self.db.query(sql_search)
size = self.get_song_nums('sname', author=singer)
print('数据库目前收录%d首' % size)
print '分别有:'
for l in data:
print(l[0])
print('是否开始下载?(y/n)')
choice = raw_input()
if choice == 'y':
base_dir = self.cm.get_config('dir')['path']
download.download_with_singer(data, base_dir, singer, size)
else:
print '已取消下载'
def searchBySname(self, song, singer=None):
"""
提供对外调用的接口, 接受歌曲名作为参数
:param song: 歌曲名
:param singer: 歌手名(选项)
:return:
"""
table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix']
sql_search = ('SELECT author, durl FROM %s ' % table) + 'WHERE sname = \'%s\'' % song
data = self.db.query(sql_search)
size = self.get_song_nums('author', sname=song)
print('数据库目前收录%d首' % size)
print '分别有:'
for l in data:
print(l[0])
print('是否开始下载?(y/n)')
choice = raw_input()
if choice == 'y':
base_dir = self.cm.get_config('dir')['path']
download.download_with_sname(song, data, base_dir)
else:
print '已取消下载'
def verpedido(iduser):
return DBManager.verpedido(iduser)
def __init__(self):
self.cm = ConfigManager()
self.db = DBManager()
def pedidosClientes():
return DBManager.verPedidosClientes()
def plantas():
return DBManager.verplantas()
def colmados():
return DBManager.vercolmados()
def query_all_information(username, identifier):
"""
请求我的页面数据
:param username: 用户名
:param identifier: 身份标志
:return:
"""
db_manager = DBManager()
data = {}
# 学生
if identifier == 0:
# 拿到称号
# 拿到总条数
sql = "select count(*) as counts from `%s` where question_username='******'" % ("tb_question", username)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
sums = 0
if result:
sums = result['counts']
# 拿到已解决的问题数
sql = "select count(*) as counts from `%s` where question_username='******' and question_status=%s" % \
("tb_question", username, 1)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
solved_num = 0
if result:
solved_num = result['counts']
# 拿到总问题数/已解决的问题数
data['question_info'] = {
"total_questions": sums,
"solved_questions": solved_num
}
# 教师
elif identifier == 1:
# 拿到称号
# 拿到总回答数
sql = "select count(question_id) as counts from `tb_answer` where answer_username='******' and question_id not in (select ask_question_id from " \
"`tb_ask` where be_asked_username='******')" % (username, username)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
sums = 0
if result:
sums = result['counts']
# 拿到没有被采纳的回答列表
query_str = FormatCondition(props={
"answer_username": username,
"is_accepted": 0
})
sql = "select count(question_id) as counts from `tb_answer` where %s and question_id not in (select ask_question_id from " \
"`tb_ask` where be_asked_username='******')" % (query_str, username)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
counts = 0
if result:
counts = result['counts']
# 拿到总回答数/被采纳的回答数
data['answer_info'] = {
"total_answers": sums,
"accepted_answers": sums - counts
}
# 拿到总学分
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql = "select user_operation_type, user_operation_desc, point_value from `%s` as u inner " \
"join `%s` as s on u.point_type=s.user_operation_type and username='******'" % ("tb_user_points",
"tb_score_rule_template", username)
msg = "[in query_user_points_detail] sql=" + sql
logging.info(msg)
cursor_0.execute(sql)
result = cursor_0.fetchall()
cursor_0.close()
if result is None:
return False, None
total_points = 0
for item in result:
total_points += item['point_value']
# 拿到称号模板数据
sql = "select *from `%s` where level_type=%s order by level" % ("tb_level_rule_template", identifier)
cursor = db_manager.conn_r.cursor(cursorclass=DictCursor)
cursor.execute(sql)
user_level_templates = cursor.fetchall()
cursor.close()
# 默认的数据
user_level = 0
level_desc = user_level_templates[0]['level_desc']
for t in user_level_templates:
level_section = safe_str_to_list(t['level_section'])
if sums in xrange(level_section[0], level_section[1]):
user_level = t['level']
level_desc = t['level_desc']
break
data['user_info'] = {
"user_level": user_level,
"level_desc": level_desc,
"total_points": total_points
}
# 拿到关注/粉丝数
cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql = "select count(*) as follows from `%s` where username='******' and relation_type=0" % ("tb_relation", username)
cursor_0.execute(sql)
result = cursor_0.fetchone()
cursor_0.close()
if result is None:
follows_num = 0
else:
follows_num = result['follows']
cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor)
sql1 = "select count(*) as fans from `%s` where other_username='******' and relation_type=0" % ("tb_relation", username)
cursor_1.execute(sql1)
result1 = cursor_1.fetchone()
cursor_1.close()
if result1 is None:
fans_num = 0
else:
fans_num = result1['fans']
data['relation_info'] = {
"follows_num": follows_num,
"fans_num": fans_num
}
db_manager.close()
return True, data
