def query_echart_data(game_id): """ 查询比分的echart 数据 """ return DBUtils.execute( "select * from bifen_index_std where game_id=%s and win_type = 'main'" " union all " "select * from bifen_index_std where game_id=%s and win_type = 'next'", (game_id, game_id))
def query_record(game_id=729379): # 查询主胜 客胜,主胜 拼接 客胜 return DBUtils.execute( """ SELECT *,substr(insert_datetime,6,11) as dd from ( SELECT cid, concat(com_name,'即赔') as com_name,insert_datetime ,`m1_0`, `m2_0`, `m2_1`, `m3_0`, `m3_1`, `m3_2`, `m4_0`, `m4_1`, `m4_2`, `m4_3`, `m0_0`, `m1_1`, `m2_2`, `m3_3`, `m4_4` ,order_num ,next.* FROM bifen_index as main ,( SELECT cid as nid,`m1_0` as m0_1, `m2_0` as m0_2, `m2_1` as m1_2, `m3_0` as m0_3, `m3_1` as m1_3, `m3_2`as m2_3, `m4_0` as m0_4, `m4_1` as m1_4, `m4_2` as m2_4, `m4_3` as m3_4 FROM bifen_index WHERE game_id=%(game_id)s AND win_type='客胜' and insert_datetime = (select max(insert_datetime) FROM bifen_index WHERE game_id=%(game_id)s) ) as next WHERE game_id=%(game_id)s AND win_type='主胜' and main.cid=next.nid and insert_datetime = (select max(insert_datetime) FROM bifen_index WHERE game_id=%(game_id)s) union all SELECT cid,concat(com_name,'初赔') as com_name,insert_datetime ,`m1_0`, `m2_0`, `m2_1`, `m3_0`, `m3_1`, `m3_2`, `m4_0`, `m4_1`, `m4_2`, `m4_3`, `m0_0`, `m1_1`, `m2_2`, `m3_3`, `m4_4`,order_num, next.* FROM bifen_index as main ,( SELECT cid as nid,`m1_0` as m0_1, `m2_0` as m0_2, `m2_1` as m1_2, `m3_0` as m0_3, `m3_1` as m1_3, `m3_2`as m2_3, `m4_0` as m0_4, `m4_1` as m1_4, `m4_2` as m2_4, `m4_3` as m3_4 FROM bifen_index WHERE game_id=%(game_id)s AND win_type='客胜' and insert_datetime = (select min(insert_datetime) FROM bifen_index WHERE game_id=%(game_id)s) ) as next WHERE game_id=%(game_id)s AND win_type='主胜' and main.cid=next.nid and insert_datetime = (select min(insert_datetime) FROM bifen_index WHERE game_id=%(game_id)s) ) as all_data ORDER BY order_num,insert_datetime """, {"game_id": game_id})
def query_record2(game_id=729379): cids = query_had_comps(game_id) sql = "" for i in range(cids.__len__()): sql += """ select * from ( SELECT cid, concat(com_name,'初赔') as com_name,insert_datetime ,`m1_0`, `m2_0`, `m2_1`, `m3_0`, `m3_1`, `m3_2`, `m4_0`, `m4_1`, `m4_2`, `m4_3`, `m0_0`, `m1_1`, `m2_2`, `m3_3`, `m4_4` ,order_num ,next.* FROM bifen_index as main ,( SELECT cid as nid,`m1_0` as m0_1,`m2_0` as m0_2,`m2_1` as m1_2,`m3_0` as m0_3, `m3_1` as m1_3, `m3_2`as m2_3, `m4_0` as m0_4, `m4_1` as m1_4, `m4_2` as m2_4, `m4_3` as m3_4 FROM bifen_index WHERE game_id={game_id} and cid ={cid} AND win_type='客胜' order by insert_datetime limit 1 ) as next WHERE game_id={game_id} and main.cid={cid} AND win_type='主胜' order by insert_datetime limit 1) as a1 union all select * from ( SELECT cid, concat(com_name,'即赔') as com_name,insert_datetime ,`m1_0`, `m2_0`, `m2_1`, `m3_0`, `m3_1`, `m3_2`, `m4_0`, `m4_1`, `m4_2`, `m4_3`, `m0_0`, `m1_1`, `m2_2`, `m3_3`, `m4_4` ,order_num ,next.* FROM bifen_index as main ,( SELECT cid as nid,`m1_0` as m0_1, `m2_0` as m0_2, `m2_1` as m1_2, `m3_0` as m0_3, `m3_1` as m1_3, `m3_2`as m2_3, `m4_0` as m0_4, `m4_1` as m1_4, `m4_2` as m2_4, `m4_3` as m3_4 FROM bifen_index WHERE game_id={game_id} and cid ={cid} AND win_type='客胜' order by insert_datetime desc limit 1 ) as next WHERE game_id={game_id} and main.cid={cid} AND win_type='主胜' order by insert_datetime desc limit 1) as a1 """.format(game_id=game_id, cid=cids[i]['cid']) if i != cids.__len__() - 1: sql += " UNION ALL " return DBUtils.execute(sql)
def queryMatchCount(): """ 查询所有日期 :return: """ return DBUtils.execute( "SELECT left(start_datetime,10) dd FROM football_game_info " "GROUP BY left(start_datetime,10) ORDER BY dd desc")
def query_had_comps(game_id): """ 查询已经抓取的公司 :return: """ return DBUtils.execute( "SELECT cid,order_num FROM `bifen_index` WHERE game_id=%(game_id)s " "GROUP BY cid,order_num ORDER BY order_num", {"game_id": game_id})
def process_sub_page3( testElemNo=None, first_tmp_url="http://odds.500.com/fenxi/rangqiu-%s.shtml?lot=jczq", next_tmp_url="http://odds.500.com/fenxi1/rangqiu.php?id=%s&ctype=1&" "start=%d&r=1&style=0&guojia=0&chupan=0&lot=jczq", table_name="rangqiu_startvalue_info"): startTotalTime = time.time() if testElemNo is not None: elemNoList = testElemNo else: elemNoList = DBUtils.execute( "select game_no,start_datetime from football_game_info " "where start_datetime>now() order by start_datetime ") all_sub_data = [] str_sql = "insert into " + table_name + "(kelly_win_num, kelly_avg_num, " \ "kelly_lost_num,game_id) values(%s,%s,%s,%s) " # 设置解析列索引 params = {} if table_name != "pay_info": params = {"col_index": 2} if not isinstance(elemNoList, list): print("不是数组:", end="") print(elemNoList) return [] """ 解析让球指数页面 """ for elObj in elemNoList: startTime = time.time() time.sleep(ConstantVal.TIMER_INTERVAL) elNo = elObj["game_no"] url = first_tmp_url % elNo # 子页面第一页的数据,存在多页的情况 all_grade_data = analysis_all_sub_page3(cra_data_url(url, "utf-8"), **params) # 子页面其他页的数据 start = 30 while True: url = next_tmp_url % (elNo, start) htmlData = cra_data_url(url, "utf-8") if htmlData and len(htmlData.strip()) > 100: result = analysis_all_sub_page3(htmlData, 'tr', **params) all_grade_data = concat_array(all_grade_data, result) start += 30 else: break # 计算平方差 if len(all_grade_data) > 0: avg_data = [ str(float('%.3f' % cal_std2(bb))) for bb in all_grade_data ] avg_data.append(elNo) all_sub_data.append(avg_data) # count = DBUtils.executeOne(str_sql, avg_data) print( str(elNo) + " " + table_name + " 子页总共抓取条数:%s 耗时:%s" % (start, utils.float_num(time.time() - startTime))) count = DBUtils.executeMany(str_sql, all_sub_data) print(all_sub_data) print("子页面保存数量:%s 耗时:%s" % (count, utils.float_num(time.time() - startTotalTime))) return elemNoList