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 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 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 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 add_contest(uid, title, desc, stime, etime, money, logo): """ 創建一個比賽: :param uid: :param title: 比賽標題 :param desc: 描述 :param stime: 開始時間:不能是當天或以前 :param etime: 結束時間,不能小於開始時間 :param money: 初始資金 :param logo: logo 圖片地址 :return: """ contest = Contest() contest.u_id = uid contest.c_title = title contest.c_explain = desc contest.c_start_date = stime contest.c_end_date = etime contest.c_logo_url = logo contest.c_default_capital = money * 10000 try: dbsession.add(contest) dbsession.commit() except Exception as e: dbsession.rollback() raise e return contest
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 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 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_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_stock_list(uid, cid, page=1, lmt=100): """ 列出用戶的持股信息,默認100條 return: a list, :param uid: :param cid: :param page: :param lmt: """ if not all((uid, cid)): logging.error("uid err", uid) raise Exception("parameter err") sql = "select * from tb_user_contest_positions where user_id = %s and stock_count > 0 and contest_id=%d" \ " order by updated_at desc" \ " limit %d, %d" % (uid, cid, (page-1)*lmt, lmt) result = [] try: query_res = dbsession.execute(sql) dbsession.commit() query_res = query_res.fetchall() except Exception as e: dbsession.rollback() logging.error(e) raise # table column headers = ("id", "created_at", "updated_at", "deleted_at", "uid", "name", "code", "count", "price", "freeze", "cid") for item in query_res: dct = dict(zip(headers, item)) tm = dct['created_at'] dct['created_at'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None tm = dct['updated_at'] dct['updated_at'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None tm = dct['deleted_at'] dct['deleted_at'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None dct['price'] = float(dct['price']) dct['freeze'] = float(dct['freeze']) result.append(dct) return result
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 addReplyReply(uid, aid, rid, content): """ 回復別人的回復消息 :param uid: :param aid: :param rid: :return: """ reply = Reply() reply.r_reply_id = rid reply.u_id = uid reply.a_id = aid reply.r_content = content try: dbsession.add(reply) dbsession.commit() except Exception as e: dbsession.rollback() raise e
def addReply(uid, aid, content): """ 添加一個回復 :param uid: :param aid: :return: """ reply = Reply() reply.u_id = uid reply.a_id = aid reply.r_content = content try: dbsession.add(reply) dbsession.commit() except Exception as e: dbsession.rollback() raise e return reply.id
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 addArticle(uid, title, content): """ 添加一個話題 :param uid: :param title: :param content: :return: """ article = Article() article.u_id = uid article.a_title = title article.a_content = content try: dbsession.add(article) dbsession.commit() except Exception as e: dbsession.rollback() raise e return article.id
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 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 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 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 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 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 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 list_orders(uid, cid, type=None, page=1, count=40, finished=None): """ 获取订单列表. :param uid: 用户id :param cid: 比赛id (0为非比赛委托单) :param page: :param count: :param type: 交易类型 TRADE_TYPE_NONE = iota TRADE_TYPE_BUY # 买入 TRADE_TYPE_SALE # 卖出 :return: """ uid = (int)(uid) cid = (int)(cid) page = (int)(page) count = (int)(count) sql = 'select * from tb_orders where ' where_cond = [ 'user_id=%d' % uid, 'contest_id=%d' % cid, ] order_by = ' order by updated_at desc ' limit_sql = ' limit %d, %d ' % ((page - 1) * count, count) if type is not None: # 交易类型过滤 where_cond.append('trade_type=%d' % type) if finished == 'finished': where_cond.append('order_status!=0') elif finished == 'unfinished': where_cond.append('order_status=0') # 执行sql sql = sql + ' and '.join(where_cond) + \ order_by + limit_sql logging.debug('sql: %s' % sql) raws = dbsession.execute(sql).fetchall() dbsession.commit() if not raws: dbsession.rollback() return [] # table colume colums = ('id', 'submit_time', 'updated_at', 'deleted_at', 'uid', 'name', 'code', 'price', 'amount', 'transfer_fee', 'brokerage', 'volume', 'trade_type', 'type', 'status', 'cid') result = [] for item in raws: dct = dict(zip(colums, item)) tm = dct['submit_time'] dct['submit_time'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None tm = dct['updated_at'] dct['updated_at'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None tm = dct['deleted_at'] dct['deleted_at'] = tm.strftime( "%Y-%m-%d %H:%M:%S") if tm else None dct['price'] = float(dct['price']) dct['brokerage'] = float(dct['brokerage']) dct['transfer_fee'] = float(dct['transfer_fee']) dct['volume'] = float(dct['volume']) result.append(dct) return result