def delReply(uid, rid):
"""
刪除一個評論
:param uid:
:param rid:
:return:
"""
recode = None
try:
recode = dbsession.query(Reply).filter(
and_(Reply.id == rid, Reply.u_id == uid)).one()
except NoResultFound as e:
raise e
except Exception as e:
dbsession.rollback()
raise e
if recode:
try:
recode.r_status = 1
dbsession.merge(recode)
dbsession.commit()
### 這個回復是回復的話題， 所有在此回復上的回復消息 都應該刪除
if recode.r_reply_id == 0:
dbsession.query(Reply).filter(
Reply.r_reply_id == recode.id).update(
{Reply.r_status: 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def getReplyByArticleId(aid, page=0, count=40):
"""
獲取話題的評論
:param aid:
:return:
"""
res = None
try:
query = dbsession.query(Reply, User.u_name, User.u_headurl).filter(
and_(Reply.a_id == aid, User.id == Reply.u_id))
query = query.limit(count).offset(count * page)
res = query.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
recode = it[0].to_json()
recode['uname'] = it[1]
recode['uheadurl'] = it[2]
# type: 2
# 類型為2， 描述為： 回復了帖子
recode['type'] = 2
recode['type_desc'] = u"評論了帖子"
# 計算距當前時間多久
recode['time'] = timeutil.Datetime2HowLong(str(it[0].r_time))
# type： 3
# 回復了用戶的評論
if it[0].r_reply_id != 0:
aritcleid = it[0].r_reply_id
try:
query = dbsession.query(Reply, User.u_name).filter(
and_(Reply.id == aritcleid, User.id == Reply.u_id))
result = query.one()
except Exception as e:
dbsession.rollback()
raise e
recode['type'] = 3
recode['type_desc'] = u"回復了"
recode['reply_uname'] = result[1]
lst.append(recode)
return lst
def get_contest_detail_rank_users(id, page=0, count=20):
"""
獲取單個比賽的排名數據：
:param id: 比賽id
:return:
"""
res = None
try:
res = dbsession.query(
ContestDetail.c_win_rate, ContestDetail.u_id,
User.u_name).filter(
and_(ContestDetail.c_id == id,
ContestDetail.c_status != 1)).join(
User, User.id == ContestDetail.u_id).order_by(
ContestDetail.c_win_rate.desc()).limit(
count).offset(page * count).all()
except Exception as e:
dbsession.rollback()
raise e
lst = []
if res:
idx = 1
for it in res:
t = {}
t['rate'] = it[0] # 盈利率
t['id'] = it[1] # uid
t['user'] = it[2] # 用戶名
t['rank'] = idx # 排名
lst.append(t)
idx += 1
return lst
def add_stock(uid, stock_code, stock_name):
if uid == None or uid == '':
logging.info("uid is None..")
return
if len(stock_code) == 0 or len(stock_name) == 0:
logging.info("stock_code, stock_name 不合法..")
return
try:
stock = dbsession.query(UserFavoriteStocks).filter_by(
u_id=uid).filter_by(s_stock_code=stock_code).one()
except NoResultFound as e:
stock = None
if stock is not None:
return "exist"
stock = UserFavoriteStocks()
stock.u_id = uid
stock.s_stock_code = stock_code
stock.s_stock_name = stock_name
dbsession.add(stock)
dbsession.commit()
def quite_contest(uid, id):
"""
用戶退出比賽
:param uid:
:param id:
:return:
"""
contest_detail = None
try:
contest_detail = dbsession.query(ContestDetail).filter(
and_(ContestDetail.c_id == id,
ContestDetail.u_id == uid)).one()
except NoResultFound as e:
return
except Exception as e:
dbsession.rollback()
raise e
contest_detail.c_status = 1
try:
dbsession.merge(contest_detail)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def update_user(user=None, **kwargs):
"""
:param kwargs:
:return:
"""
user_obj = {}
if isinstance(user, User):
user_obj = user
logging.debug("user is User instance.")
print("user is User instance.")
else:
if 'id' in kwargs:
user_obj = dbsession.query(User).filter_by(
id=kwargs['id']).one()
for k, v in kwargs.items():
if k == 'id':
continue
if v is not None:
setattr(user_obj, k, v)
try:
logging.debug(user_obj)
print(user_obj)
dbsession.merge(user_obj)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
def get_contest_user_info(uid, cid):
"""
獲取用戶在某一場次比賽中的表現：
:param uid:
:param cid:
:return: json 串
"""
info = None
try:
info = dbsession.query(
ContestDetail, User.u_name, User.u_headurl).filter(
and_(ContestDetail.u_id == uid,
ContestDetail.c_id == cid)).join(
User, User.id == ContestDetail.u_id).one()
except NoResultFound as e:
return []
except Exception as e:
dbsession.rollback()
raise e
logging.debug(info)
if info:
tmp = info[0].to_json()
tmp['uname'] = info[1]
tmp['uheadurl'] = info[2]
return tmp
return []
def getArticleByUid(uid, page=0, count=40):
"""
獲取用戶發表的
:param uid:
:return:
"""
res = None
try:
res = dbsession.query(Article, User.u_name, User.u_headurl).join(User, Article.u_id == User.id)\
.filter(Article.u_id==uid).order_by(desc(Article.a_pub_time)).limit(count).offset(page*count).all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
tmp = it[0].to_json()
tmp['uname'] = it[1]
tmp['uheadurl'] = it[2]
# 計算距當前時間多久
tmp['time'] = timeutil.Datetime2HowLong(str(it[0].a_pub_time))
lst.append(tmp)
return lst
def get_contest_list(page=0, count=20):
"""
獲取比賽列表：默認獲取的是未結束（關閉）的
:param page:
:param count:
:return:
"""
ret = None
try:
# ret = dbsession.query(Contest).all()
# ret = dbsession.query(Contest).limit(count).offset(page * count).all()
ret = dbsession.query(
Contest, User.u_name).filter(Contest.u_id == User.id).order_by(
Contest.c_create_time).limit(count).offset(page *
count).all()
except Exception as e:
dbsession.rollback()
raise e
lst = []
if ret:
for it in ret:
tmp = it[0].to_json()
tmp['createuser'] = it[1]
lst.append(tmp)
return lst
def delArticle(uid, id):
"""
刪除一個話題， 邏輯刪除
:param uid:
:param id:
:return: None, throw exception if error
"""
article = None
try:
article = dbsession.query(Article).filter(
and_(Article.id == id, Article.u_id == uid)).one()
except NoResultFound as e:
return "話題找不到了。。。"
except Exception as e:
dbsession.rollback()
raise e
if not article:
return "話題找不到了。。。"
article.a_status = 2 # 關閉（邏輯刪除）
try:
dbsession.merge(article)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def getArticleById(id):
"""
獲取文章數據
:param id:
:return:
"""
article = None
try:
query = dbsession.query(Article, User.u_name,
User.u_headurl).filter(
and_(Article.id == id,
Article.u_id == User.id))
article = query.one()
except NoResultFound as e:
raise e
except Exception as e:
dbsession.rollback()
raise e
if article:
retdata = article[0].to_json()
retdata['uname'] = article[1]
retdata['uheadurl'] = article[2]
# 計算距當前時間多久
retdata['time'] = timeutil.Datetime2HowLong(
str(article[0].a_pub_time))
return retdata
return None
def getNewestReply(page=0, count=40):
"""
獲取最新的回復消息
:param page:
:param count:
:return: 返回dict， 包括回復相關的所有數據和話題標題，話題作者
"""
res = None
try:
res = dbsession.query(Reply, Article, User.u_name, User.u_headurl).join(Article, Article.id==Reply.a_id)\
.filter(User.id == Reply.u_id) \
.order_by(desc(Reply.r_time)).limit(count).offset(page*count) \
.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
tmp = it[0].to_json()
tmp.update(it[1].to_json())
tmp['uname'] = it[2]
tmp['uheadurl'] = it[3]
# 計算距當前時間多久
tmp['time'] = timeutil.Datetime2HowLong(str(it[0].r_time))
lst.append(tmp)
return lst
def upcountArticle(aid, uid):
"""
給文章點贊, 如果電讚了得 就取消點讚
:param id:
:param uid:
:return:
"""
recode = None
try:
recode = dbsession.query(ArticleUpcounts).filter(
and_(ArticleUpcounts.a_id == aid,
ArticleUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ArticleUpcounts()
recode.u_id = uid
recode.a_id = aid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
else:
try:
dbsession.delete(recode)
dbsession.commit()
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts - 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
def upcountReply(uid, rid):
"""
給評論點贊, 如果電讚了得 就取消點讚
:param id:
:param uid:
:return:
"""
recode = None
try:
recode = dbsession.query(ReplyUpcounts).filter(
and_(ReplyUpcounts.r_id == rid,
ReplyUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ReplyUpcounts()
recode.u_id = uid
recode.r_id = rid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Reply).filter(Reply.id == rid).update(
{Reply.r_upcounts: Reply.r_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
else:
try:
dbsession.delete(recode)
dbsession.commit()
dbsession.query(Reply).filter(Reply.id == rid).update(
{Reply.r_upcounts: Reply.r_upcounts - 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
def sale_stock(uid, stock_code, stock_name, stock_count):
"""
賣出
:param uid:
:param stock_code:
:param stock_name:
:param stock_count:
:return: if success return True,"" ortherwise return False, "errmsg" or raise the exceptions
"""
if not all([uid, stock_code, stock_name, stock_count]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count)
raise Exception("參數錯誤!")
if not all( [isinstance(uid, str), isinstance(stock_code, str),
isinstance(stock_name, str), isinstance(stock_count, int)]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count)
raise Exception("參數錯誤!")
# 查詢是否已經購買此股
stock = None
try:
stock = dbsession.query(UserStock).filter_by(u_id=uid).filter_by(s_stock_code=stock_code).one()
except NoResultFound as e:
dbsession.rollback()
logging.error(e)
if stock == None:
logging.info("用戶沒有持有持有該股票：", uid, stock_code)
return False, "沒有持有持有該股票"
### 剛好清倉
if stock.s_stock_count == stock_count:
try:
dbsession.delete(stock)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
## 數據有錯誤
elif stock.s_stock_count < stock_count:
logging.info("用戶未持有這麽多股份：", uid, stock_code, stock_count)
return False, "未持有這麽多股份"
#正常買出
else:
stock.s_stock_count -= stock_count
try:
dbsession.merge(stock)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return True, ""
def upcountArticleOnly(aid, uid):
"""
給文章點贊：只點贊，不存在重複點贊
:param aid:
:param uid:
:return: success：return true,""
fail： return false,"失敗原因"
:exception: exception returned if sql err
"""
recode = None
try:
recode = dbsession.query(ArticleUpcounts).filter(
and_(ArticleUpcounts.a_id == aid,
ArticleUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ArticleUpcounts()
recode.u_id = uid
recode.a_id = aid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
return True, ""
else:
return False, "你已經點過贊了"
def get_user_bymobile(mobile):
if not isinstance(mobile, str):
raise TypeError("name 类型错误", mobile)
try:
user = dbsession.query(User).filter_by(u_mobilephone=mobile).one()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def get_user_byemail(email):
if not isinstance(email, str):
raise TypeError("name 类型错误", email)
try:
user = dbsession.query(User).filter_by(u_email=email).one()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def join_contest(uid, id):
"""
用戶參加比賽：根據id獲取比賽的相關信息，例如初始資金，將數據填入tb_contest_detail中
:param uid:
:param id:
:return:
"""
test = None
user_contest_detail = None
try:
test = dbsession.query(Contest).filter_by(id=id).one()
except Exception as e:
dbsession.rollback()
raise e
try:
user_contest_detail = dbsession.query(ContestDetail).filter(
and_(ContestDetail.u_id == uid,
ContestDetail.c_id == test.id)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
# 已經存在數據，説明已經加入比賽
if user_contest_detail:
raise Exception("已經加入該比賽！")
user_contest_detail = ContestDetail()
user_contest_detail.c_join_time = datetime.now()
user_contest_detail.c_money = test.c_default_capital # 初始資金
user_contest_detail.c_id = test.id
user_contest_detail.u_id = uid
try:
dbsession.add(user_contest_detail)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def add_stock(uid, stock_code, stock_name, stock_count, stock_price):
"""
no return val
raise 參數錯誤異常
:param uid:
:param stock_code:
:param stock_name:
:param stock_count:
:param stock_price: 成本價
:return:
"""
if not all([uid, stock_code, stock_name, stock_count, stock_price]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count, stock_price)
raise Exception("參數錯誤!")
### 查詢是否已經買過該股票，是：添加持股數，否添加紀錄
stock = None
try:
stock = dbsession.query(UserStock).filter_by(u_id=uid).filter_by(s_stock_code=stock_code).one()
except NoResultFound as e:
dbsession.rollback()
logging.error(e)
# 沒記錄，沒買過
if stock is None:
stock = UserStock()
stock.u_id = uid
stock.s_stock_price = stock_price
stock.s_stock_code = stock_code
stock.s_stock_name = stock_name
stock.s_stock_count = stock_count
try:
dbsession.add(stock)
dbsession.commit()
except Exception as e:
dbsession.rollback()
logging.error(e)
raise e
else:
price = ((float)(stock.s_stock_price) * stock.s_stock_count + stock_price*stock_count) / (stock.s_stock_count+stock_count)
stock.s_stock_count += stock_count
stock.s_stock_price = price
try:
dbsession.merge(stock)
dbsession.commit()
except Exception as e:
dbsession.rollback()
logging.error(e)
raise e
def checkUpcount(uid, aid):
"""
檢查是否是點過讚了
:param uid:
:param aid:
:return: True or False
"""
recode = None
try:
dbsession.query(ArticleUpcounts).filter(
and_(ArticleUpcounts.a_id == aid,
ArticleUpcounts.u_id == uid)).one()
except NoResultFound as e:
return False
# except Exception as e:
# raise e
if recode:
return True
return False # 沒有點過
def get_user_byid(id):
"""
:param id: 用户id
:return: 用户对象
"""
user = None
try:
user = dbsession.query(User).get(id)
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def get_stocks(uid, lmt=100):
if not isinstance(uid, str) or not isinstance(lmt, int):
logging.error("參數類型錯誤:", uid, lmt)
raise TypeError("參數類型錯誤")
ret = dbsession.query(UserFavoriteStocks).filter_by(
u_id=uid).limit(lmt).all()
res = []
for it in ret:
res.append(it.to_json())
# test
sz = len(res)
logging.debug("get_stocks: ret.length: %d" % sz)
return res
def delete_user(arg):
user = None
try:
if isinstance(arg, int):
user = dbsession.query(User).filter(User.id == arg).one()
if user is not None:
dbsession.delete(user)
dbsession.commit()
else:
logging.error("delete_user:user obj is not a User instance.")
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
def del_stock(uid, stock_code):
if uid is None or uid == '':
logging.error("uid 不合法:", uid)
raise Exception("Parameter error.")
if stock_code is None or stock_code == '':
logging.error("stock_code 不合法:", stock_code)
raise Exception("Parameter error.")
try:
stock = dbsession.query(UserFavoriteStocks).filter_by(
u_id=uid).filter_by(s_stock_code=stock_code).one()
# 找不到數據會抛出NoResultFound異常，補貨，不做處理,正常執行下面流程
except NoResultFound as e:
stock = None
if (stock is not None):
dbsession.delete(stock)
dbsession.commit()
else:
logging.error("stock:%s不存在" % (stock_code))
def close_contest(id):
"""
關閉比賽, 結束比薩不在這裏處理，在每天的收盤后會有定時任務處理！
:param id:
:return:
"""
test = None
try:
test = dbsession.query(Contest).filter(
and_(Contest.id == id, Contest.c_status != 3,
Contest.c_status != 2)).one()
if test:
test.c_status = 3 # 關閉
dbsession.merge(test)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def getArticleNewestHot(latest_day=7, page=0, count=20):
"""
獲取熱門的文章：依據latest day 進行排序，查看數量最多的
:param latest_day 時間區間，7表示最近一周的熱門話題
:return:
"""
res = None
try:
dt = datetime.today() - dttm.timedelta(latest_day)
query = dbsession.query(
Article, User.u_name,
User.u_headurl).filter(User.id == Article.u_id).filter(
Article.a_pub_time > dt).order_by(
desc(Article.a_interviews)).order_by(
desc(Article.a_pub_time)).limit(count).offset(
page * count)
res = query.all()
except Exception as e:
raise e
if not res:
return []
lst = []
for it in res:
recode = it[0].to_json() # Article recode
recode['uname'] = it[1] # user name
recode['uheadurl'] = it[2]
# 計算距當前時間多久
recode['time'] = timeutil.Datetime2HowLong(str(it[0].a_pub_time))
recode['type'] = 1
recode['type_desc'] = u"發表了帖子"
lst.append(recode)
return lst
def getNewestArticles(page=0, count=20):
"""
按最新排序獲取，分頁
:param page:
:param count:
:return:
"""
res = None
try:
query = dbsession.query(
Article, User.u_name,
User.u_headurl).filter(User.id == Article.u_id).order_by(
desc(Article.a_pub_time)).limit(count).offset(page * count)
res = query.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
recode = it[0].to_json()
recode['uname'] = it[1]
recode['uheadurl'] = it[2]
# 計算距當前時間多久
recode['time'] = timeutil.Datetime2HowLong(str(it[0].a_pub_time))
# 類型： 默認這裏讀出來的都是 文章， 文章默認是使用1 type
# 描述為：發表了帖子
recode['type'] = 1
recode['type_desc'] = u"發表了帖子"
lst.append(recode)
return lst
def get_user_contest_list(uid, all=False):
"""
獲取用戶參加的比賽
:param uid:
:param all: 是否是已完成的 False:未結束的， True 全部的
:return:
"""
res = None
try:
query = dbsession.query(Contest, ContestDetail, User.u_name)\
.join(ContestDetail, Contest.id==ContestDetail.c_id)\
.filter(ContestDetail.u_id==uid)\
.join(User, User.id==Contest.u_id)
# query = dbsession.query(ContestDetail, Contest, User.u_name).join(User, User.id==ContestDetail.u_id).filter(Contest.id==ContestDetail.c_id).filter(ContestDetail.u_id==uid)
if not all:
query = query.filter(
or_(Contest.c_status == 1, Contest.c_status == 0))
res = query.order_by(Contest.c_create_time).all()
# print(query)
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
contest_dct = it[0].to_json()
contest_detail_dct = it[1].to_json()
dct = contest_dct
dct.update(contest_detail_dct)
dct['createuser'] = it[2]
lst.append(dct)
return lst
def check_user_in_contest(uid, id):
"""
判斷用戶是否在比賽裏面
:param uid:
:param id:
:return: 0 可加入， 1已加入， 2：已退出
"""
contest_detail = None
try:
contest_detail = dbsession.query(ContestDetail).filter_by(
c_id=id).filter_by(u_id=uid).one()
except NoResultFound as e:
return False
except Exception as e:
raise e
if not contest_detail:
return 0
if contest_detail.c_status == 0: # 已加入
return 1
elif contest_detail.c_status == 1: # 已退出
return 2
