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