def check_rec(bids, rec): # 提取新记录成绩 conn = interMysql.Connect() sql = ''' SELECT bid, score, mods from recinfo where bid in (%s) ''' sql = sql % (','.join(map(lambda x: '%s', bids))) ret = conn.query(sql, bids) if not ret: # 成绩过滤 return rec newRec = [] ret_dict = {} # 最大成绩录入 max_dict = {} for r in ret: if r['bid'] in ret_dict: ret_dict[r['bid']][r['mods']] = r['score'] if int(r['score']) > max_dict[r['bid']]: max_dict[r['bid']] = int(r['score']) else: ret_dict[r['bid']] = {r['mods']: r['score']} max_dict[r['bid']] = int(r['score']) # 单mod成绩比较 for r in rec: if r['beatmap_id'] in ret_dict and int( r['enabled_mods']) in ret_dict[r['beatmap_id']]: if int(r['score']) > int(ret_dict[r['beatmap_id']][int( r['enabled_mods'])]) or int( r['score']) > max_dict[r['beatmap_id']]: newRec.append(r) else: newRec.append(r) return newRec
def msg_recollect(globValue, gV_Lock): try: conn = interMysql.Connect() sql = ''' SELECT content FROM chat_logs ''' csql = 'SELECT count(1) c FROM chat_logs' num = conn.query(csql)[0]['c'] if Config.DEBUG == 1: sql += ' limit %s,10000' % random.randint(0, int(num)-10000) res = conn.query(sql) if not res: return shuf_res = res random.shuffle(shuf_res) limit_cnt = Config.LIMIT_SEQ_NUM msglist = globValue.get('msglist', set([])) for i in range(len(msglist)): msglist.pop() for r in shuf_res: if len(msglist) > limit_cnt: break if len(r['content']) < 30: msglist.add(r['content']) gV_Lock.acquire() globValue['msglist'] = msglist gV_Lock.release() logging.info('词库自动更新,条数:%s' % len(msglist)) except: traceback.print_exc() return
def get_user_stats_today(self, uid): conn = interMysql.Connect() sql = 'SELECT * FROM user2 where osuid = %s order by time desc limit 1' ret = conn.query(sql, uid) if not ret: return None return ret
def get_user_from_db2(self, uname): conn = interMysql.Connect() sql = 'SELECT * FROM user where osuname = %s' ret = conn.query(sql, uname) if not ret: return None return ret[0]
def hid_mytops(uid, groupid, hid=1, mods=-1): # top1数量列表 conn = interMysql.Connect() sql = ''' SELECT bid FROM maprank where gid=%s and hid=%s and mods=%s and uid=%s ''' ret = conn.query(sql, [groupid, hid, mods, uid]) return ret
def get_user_from_db3(self, oids): conn = interMysql.Connect() sql = 'SELECT * FROM user where osuid in (%s)' sql = sql % (','.join(map(lambda x: '%s', oids))) ret = conn.query(sql, oids) if not ret: return None return ret
def get_usernames_by_uid(self, uids): conn = interMysql.Connect() sql = 'SELECT osuid,osuname FROM user where osuid in (%s)' sql = sql % (','.join(map(lambda x: '%s', uids))) ret = conn.query(sql, uids) if not ret: return None return ret
def maps_top(bids, groupid, hid=1): conn = interMysql.Connect() sql = ''' SELECT gid,hid,bid,mods,uid,type,lastdate,rankjson from maprank where gid = %s and hid = %s and bid in (%s) ''' sql = sql % ('%s', '%s', ','.join(map(lambda x: '%s', bids))) args = [groupid, hid] + bids ret = conn.query(sql, args) return ret
def searchExpect(self, expect, gid): conn = interMysql.Connect() sql = ''' SELECT * FROM chartexpect WHERE expect = %s and gid = %s ''' args = [expect, gid] ret = conn.query(sql, args) return ret
def get_ircbind(osuname): conn = interMysql.Connect() sql = ''' SELECT * from ircbind where osuname = %s ''' ret = conn.query(sql, osuname) if not ret: return -1 return ret[0]['groupid']
def tops_rank(groupid, hid=1): # top榜排行 conn = interMysql.Connect() sql = ''' SELECT uid,count(uid) num FROM maprank WHERE gid = %s and hid = %s and mods = %s GROUP BY uid ORDER BY num desc LIMIT 10 ''' ret = conn.query(sql, [groupid, hid, -1]) return ret
def hid_ranks(bid, groupid, hid=1, mods=-1): # 指定式查询 -- 未扩展 conn = interMysql.Connect() sql = ''' SELECT a.bid, a.mods, a.rankjson, b.title, b.artist, b.version, b.source, b.mapjson from maprank a INNER JOIN beatmap b ON a.bid=b.bid where a.gid = %s and a.hid = %s and a.bid = %s and a.mods = %s ''' ret = conn.query(sql, [groupid, hid, bid, mods]) return ret
def filter_beatmapid(bids): conn = interMysql.Connect() sql = ''' SELECT bid from beatmap where bid in (%s) ''' sql = sql % (','.join(map(lambda x: '%s', bids))) ret = conn.query(sql, bids) db_bids = set([r['bid'] for r in ret]) new_bids = set(bids) - db_bids return list(new_bids)
def get_alias(cname, rtype=1): conn = interMysql.Connect() sql = ''' SELECT * from alias where cname = %s ''' ret = conn.query(sql, cname) if not ret: return -1 if rtype == 1: return ret[0]['bid'] elif rtype == 2: return ret[0]['uid']
def chatlog(): try: conn = interMysql.Connect() sql = ''' SELECT * FROM chat_logs ''' res = conn.query(sql) if not res: return [] return res except: traceback.print_exc()
def get_user_from_db(self, qq, gid=None): conn = interMysql.Connect() sql = 'SELECT * FROM user where qq = %s' args = [qq] if gid: sql += ' and groupid=%s' args.append(str(gid)) ret = conn.query(sql, args) if not ret: return None self.userdbinfo = ret[0] return self.userdbinfo
def check_user(self, uid): '''pp估计计算''' try: userinfo = self.get_user_info(uid) if not userinfo: return 0, 0, 0 bpinfo = self.get_user_bp(uid) pp = userinfo['pp_raw'] count_num = 0 count_pp = 0 maxpp = 0 conn = interMysql.Connect() for r in bpinfo: maxcombo1 = int(r['maxcombo']) - 10 maxcombo2 = int(r['maxcombo']) + 10 c50 = float(r['count50']) c100 = float(r['count100']) c300 = float(r['count300']) cmiss = float(r['countmiss']) acc = round((c50 * 50 + c100 * 100 + c300 * 300) / (c50 + c100 + c300 + cmiss) / 300 * 100, 2) acc1 = acc - 0.2 acc2 = acc + 0.2 args = [ r['beatmap_id'], r['enabled_mods'], acc1, acc2, maxcombo1, maxcombo2 ] sql = ''' SELECT avg(u.pp_raw) a, count(1) b from osu_bp b INNER JOIN osu_user u on b.user_id=u.user_id where b.beatmap_id = %s and b.mods=%s and b.acc BETWEEN %s and %s and b.maxcombo BETWEEN %s and %s ''' res = conn.query(sql, args) res = res[0] if res['a'] is None: continue if res['a'] > maxpp: maxpp = res['a'] if res['b'] != 1: count_num += 1 count_pp += res['a'] if count_num == 0: yugu_pp = pp else: yugu_pp = round(count_pp / count_num) if maxpp == 0: maxpp = float(pp) return pp, yugu_pp, round(maxpp) except: traceback.print_exc() return 0, 0, 0
def delalias(cname): try: conn = interMysql.Connect() sql = ''' DELETE from alias where cname = %s ''' ret = conn.execute(sql, cname) conn.commit() logging.info('alias删除记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc() return -1
def alias2db(cname, bid='', uid=''): try: conn = interMysql.Connect() sql = ''' INSERT into alias(bid, uid, cname) VALUES(%s, %s, %s) ''' ret = conn.execute(sql, [bid, uid, cname]) conn.commit() logging.info('alias入库记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc() return -1
def talkFromDB(groupid, nums): try: conn = interMysql.Connect() sql = ''' SELECT qq, count(1) cnt FROM chat_logs WHERE group_number=%s GROUP BY qq ORDER BY cnt desc limit %s''' args = [groupid, nums] ret = conn.query(sql, args) if not ret: return [] return ret except: traceback.print_exc() return
def map2db(args): try: conn = interMysql.Connect() sql = ''' INSERT into beatmap(bid, source, artist, title, version, creator, stars, addtime, mapjson) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE source=%s, artist=%s, title=%s, version=%s, creator=%s, stars=%s, addtime=%s, mapjson=%s ''' ret = conn.executeMany(sql, args) conn.commit() logging.info('map入库记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc()
def rec2db(args): try: conn = interMysql.Connect() sql = ''' INSERT into recinfo(hid, bid, uid, score, maxcombo, mods, playdate, lastdate, rank, recjson) VALUES(%s, %s, %s, %s, %s, %s, %s, now(), %s, %s) ON DUPLICATE KEY UPDATE score=%s, maxcombo=%s, playdate=%s, lastdate=now(), rank=%s, recjson=%s ''' ret = conn.executeMany(sql, args) conn.commit() logging.info('rec入库记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc()
def Chat2DB(groupid, qq, content): try: conn = interMysql.Connect() now = datetime.datetime.now() createtime = now.strftime('%Y-%m-%d %H:%M:%S') # 插入数据 sql = ''' INSERT INTO chat_logs (group_number,qq,content,create_time) VALUES ( %s, %s, %s, now()) ''' args = [groupid, qq, content] conn.execute(sql, args) conn.commit() except: traceback.print_exc() conn.rollback() return
def cmdFromDB(groupid, nums): try: conn = interMysql.Connect() sql = ''' SELECT content,count(1) cnt FROM chat_logs WHERE group_number=%s and content like '!%%' GROUP BY content ORDER BY cnt desc LIMIT %s''' args = [groupid, nums] ret = conn.query(sql, args) if not ret: return [] return ret except: traceback.print_exc() return
def chartexpect2DB(self, expect, gid, hid, bidjson, starttime, endtime): try: conn = interMysql.Connect() sql = ''' insert into chartexpect (expect, gid, hid, bidjson, starttime, endtime) values (%s,%s,%s,%s,%s,%s) ''' args = [expect, gid, hid, bidjson, starttime, endtime] ret = conn.execute(sql, args) conn.commit() return ret except: traceback.print_exc() conn.rollback() return 0
def chart2DB(self, hid, bid, gid, admin, mod, starttime, endtime, intro, resultjson, pprange, expect): try: conn = interMysql.Connect() sql = ''' insert into chart (hid, bid, gid, admin, `mod`, starttime, endtime, intro, resultjson, pprange, modifytime, expect) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,now(),%s) ''' args = [hid, bid, gid, admin, mod, starttime, endtime, intro, resultjson, pprange, expect] ret = conn.execute(sql, args) conn.commit() return ret except: traceback.print_exc() conn.rollback() return 0
def bind_group_irc(osuname, groupid, qq): try: conn = interMysql.Connect() sql = ''' INSERT into ircbind(osuname, groupid, qq) VALUES(%s, %s, %s) ON DUPLICATE KEY UPDATE groupid=%s, qq=%s ''' ret = conn.execute(sql, [osuname, groupid, qq, groupid, qq]) conn.commit() logging.info('ircbind入库记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc() return -1
def rank2db(args): try: # print('入库参数:%s'%args) conn = interMysql.Connect() sql = ''' INSERT into maprank(gid, hid, bid, uid, type, mods, lastdate, rankjson) VALUES(%s, %s, %s, %s, %s, %s, now(), %s) ON DUPLICATE KEY UPDATE uid=%s, type=%s, lastdate=now(), rankjson=%s ''' ret = conn.executeMany(sql, args) conn.commit() logging.info('rank入库记录 %s' % ret) print('rank入库记录 %s' % ret) return ret except: conn.rollback() traceback.print_exc()
def choiceMap(self, uid): '''低端推荐pp图''' try: conn = interMysql.Connect() pp = self.get_user_pp(uid) if not pp: return 0, 0 pp = float(pp) sql = ''' SELECT beatmap_id,count(beatmap_id) num FROM osu_user ta INNER JOIN osu_bp tb on ta.user_id = tb.user_id where ta.pp_raw BETWEEN %s and %s GROUP BY beatmap_id ORDER BY num desc limit 0,20; ''' res = conn.query(sql, [pp, pp + 20]) if not res: return 0, 0 ret = random.choice(res) return ret['beatmap_id'], ret['num'] except: traceback.print_exc() return 0, 0
def insert2DB(self, qq, osuid, groupid, osuname, name=None): try: conn = interMysql.Connect() sql = ''' insert into user (qq, osuid, name, groupid, osuname) values (%s,%s,%s,%s,%s) on duplicate key update osuid = %s, osuname = %s, name = %s ''' args = [qq, osuid, name, groupid, osuname, osuid, osuname, name] ret = conn.execute(sql, args) conn.commit() return ret except: traceback.print_exc() conn.rollback() return 0