import json import time import redis import logging import requests # from twisted.enterprise import adbapi from scrapy.conf import settings import psycopg2 import psycopg2.extras from .ball_func import zqone_intodb, get_pcode, zqtwo_intodb, new_time, out_time, zqone_competition, zqtwo_competition, \ zqthree_competition, two_intodb, one_intodb, three_intodb, wqone_intodb, hash_func, r_func, bqone_intodb, gjz_into, \ gjl_into, gjw_into, gjb_into, new_times, zqone_intodbs, zqtwo_intodbs, zqone_competitions # 足球 赔率 赛事--------------------------------------------------------------------------------------------------------- class Zuqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # 使用twisted将mysql插入变成异步执行 logger = logging.getLogger(__name__) # logger.warning(query.addErrback(self.handle_error, item, spider)) # 比赛日期 try: data_game = item['data_game'].split("/") print(data_game) month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + " " + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 比赛id competition_id = item['game_id'] # 联赛id league_id = item['league_id'] # 联赛name league_name = item['league_name'] # # 主队 team_home = item['team_home'] # 客队 team_guest = item['team_guest'] # number number = item['number'] pt = item['pt'] corner_ball = item['corner_ball'] p_code, p_id = get_pcode(corner_ball, 'goal_size') # 构建唯一索引 half_size_guest = item["half_size_guest"] half_size_guest_rule = item["half_size_guest_rule"] half_size_home = item["half_size_home"] half_size_home_rule = item["half_size_home_rule"] zuqiu = item['zuqiu'] size_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'pt': pt} # 让球 数据插入数据库 try: zqone_intodb(data1=half_size_home, data2=size_data, data3="half_goal_size_home", data4=half_size_home_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_size_home插入错误') logger.warning(e) # print(item) # # 让球 数据插入数据库 try: zqone_intodb(data1=half_size_guest, data2=size_data, data3="half_goal_size_guest", data4=half_size_guest_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_size_guest插入错误') logger.warning(e) # 全场场大小 size_guest = item["size_guest"] size_guest_rule = item["size_guest_rule"] size_home = item["size_home"] size_home_rule = item["size_home_rule"] try: zqone_intodb(data1=size_guest, data2=size_data, data3="goal_size_guest", data4=size_guest_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球size_guest插入错误') logger.warning(e) try: zqone_intodb(data1=size_home, data2=size_data, data3="goal_size_home", data4=size_home_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球size_home插入错误') logger.warning(e) p_code, p_id = get_pcode(corner_ball, 'concede') concede_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'pt': pt} # 上半场大小 half_concede_home_rule = item["half_concede_home_rule"] half_concede_home = item["half_concede_home"] half_concede_guest_rule = item["half_concede_guest_rule"] half_concede_guest = item["half_concede_guest"] # 上半场让球 try: zqone_intodb(data1=half_concede_home, data2=concede_data, data3="half_concede_home", data4=half_concede_home_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_concede_home插入错误') logger.warning(e) try: zqone_intodb(data1=half_concede_guest, data2=concede_data, data3="half_concede_guest", data4=half_concede_guest_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_concede_guest插入错误') logger.warning(e) concede_guest = item["concede_guest"] concede_guest_rule = item["concede_guest_rule"] concede_home = item["concede_home"] concede_home_rule = item["concede_home_rule"] # 全场让球 try: zqone_intodb(data1=concede_guest, data2=concede_data, data3="concede_guest", data4=concede_guest_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球concede_guest插入错误') logger.warning(e) try: zqone_intodb(data1=concede_home, data2=concede_data, data3="concede_home", data4=concede_home_rule, cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球concede_home插入错误') logger.warning(e) p_code, p_id = get_pcode(corner_ball, 'capot') # 独赢---------------------------------------------------------------------------------------------------------- half_capot_home = item["half_capot_home"] half_capot_guest = item["half_capot_guest"] half_capot_dogfall = item["half_capot_dogfall"] capot_home = item["capot_home"] capot_guest = item["capot_guest"] capot_dogfall = item["capot_dogfall"] capot_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # 上半场独赢 主队 try: zqtwo_intodb(data1=half_capot_home, data2=capot_data, data3="half_capot_home", data4='1', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球half_capot_home插入错误') logger.warning(e) # 上半场独赢 客队 try: zqtwo_intodb(data1=half_capot_guest, data2=capot_data, data3="half_capot_guest", data4='2', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球half_capot_guest插入错误') logger.warning(e) # 上半场独赢 和 try: zqtwo_intodb(data1=half_capot_dogfall, data2=capot_data, data3="half_capot_dogfall", data4='x', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球half_capot_dogfall插入错误') logger.warning(e) # # 全场独赢 主队 try: zqtwo_intodb(data1=capot_home, data2=capot_data, data3="capot_home", data4='1', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球capot_home插入错误') logger.warning(e) # 全场独赢 客队 try: zqtwo_intodb(data1=capot_guest, data2=capot_data, data3="capot_guest", data4='2', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球capot_guest插入错误') logger.warning(e) # 全场独赢 和 try: zqtwo_intodb(data1=capot_dogfall, data2=capot_data, data3="capot_dogfall", data4='x', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球capot_dogfall插入错误') logger.warning(e) # # 入球数单双------------------------------------------------------------------------------------------------------------- p_code, p_id = get_pcode(corner_ball, 'two_sides') odd_even_odd = item["odd_even_odd"] odd_even_even = item["odd_even_even"] half_odd_even_odd = item["half_odd_even_odd"] half_odd_even_even = item["half_odd_even_even"] # 全场入球数 单双 two_sides_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # 上半场入球数 单双 try: zqtwo_intodb(data1=odd_even_odd, data2=two_sides_data, data3="two_sides_single", data4='单', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球odd_even_odd插入错误') logger.warning(e) # url = 'http://sports.5gogo.com/Sports/WriteSports/setSports' # data = { # "game_code": "zq", # "title": "odds", # "data": { # "match_id": competition_id, # "lg_id": 1123, # "odds_code": "concede_guest", # "status": 0, # "sort": 0, # "p_code": "concede_size", # "odds": 1.2333, # "condition": "+1.5", # "odds_only": "b15bad3fc8f09924874fef0e19f63d001", # "sole": "5e2225944d8cdc44cd7943330986978d2", # "source": "c22123", # "type": 0, # "team": "" # } # } # data = json.dumps(data) # print(data) # r = requests.post(url, data=data) # print(r.content) try: zqtwo_intodb(data1=odd_even_even, data2=two_sides_data, data3="two_sides_double", data4='双', cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('足球odd_even_even插入错误') logger.warning(e) # 全场入球数 单双 try: zqtwo_intodb(data1=half_odd_even_odd, data2=two_sides_data, data3="half_two_sides_single", data4='单', cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_odd_even_odd插入错误') logger.warning(e) try: zqtwo_intodb(data1=half_odd_even_even, data2=two_sides_data, data3="half_two_sides_double", data4='双', cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_odd_even_even插入错误') logger.warning(e) # 总入球数 -------------------------------------------------------------------------------------------------------------- p_code, p_id = get_pcode(corner_ball, 'total_goal') total_goals = item['total_goal'] total_dict = {'total_goal_zero': '0-1', 'total_goal_two': '2-3', 'total_goal_four': '4-6', 'total_goal_seven': '7或以上', 'half_total_goal_zero': '0', "half_total_goal_one": '1', "half_total_goal_two": '2', "half_total_goal_three": '3或以上'} # 全场入球数 单双 total_goal_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # 上半场入球数 单双 for key, value in total_goals.items(): if value: try: zqtwo_intodb(data1=value, data2=total_goal_data, data3=key, data4=total_dict[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球total_dict插入错误') logger.warning(e) # 全场半场 -------------------------------------------------------------------------------------------------------------- half_fulls = item['half_full'] p_code, p_id = get_pcode(corner_ball, 'half_full') full_dict = {"half_full_home_home": "主主", "half_full_home_dogfall": "主和", "half_full_home_guest": "主客", "half_full_dogfall_home": "和主", "half_full_dogfall_dogfall": "和和", "half_full_dogfall_guest": "和客", "half_full_guest_home": "客主", "half_full_guest_dogfall": "客和", "half_full_guest_guest": "客客"} half_full_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} if half_fulls: for key, value in half_fulls.items(): if value: try: zqtwo_intodb(data1=value, data2=half_full_data, data3=key, data4=full_dict[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球full_dict插入错误') logger.warning(e) # 波胆------------------------------------------------------------------------------------------------------------------ bodan_datas = item['bodan_data'] p_code, p_id = get_pcode(corner_ball, 'bodan') bodan_dict = {"bodanhome_one_zero": "1-0", "bodanhome_two_zero": "2-0", "bodanhome_two_one": "2-1", "bodanhome_three_zero": "3-0", "bodanhome_three_one": "3-1", "bodanhome_three_two": "3-2", "bodanhome_four_zero": "4-0", "bodanhome_four_one": "4-1", "bodanhome_four_two": "4-2", "bodanhome_four_three": "4-3", "bodanguest_one_zero": "0-1", "bodanguest_two_zero": "0-2", "bodanguest_two_one": "1-2", "bodanguest_three_zero": "0-3", "bodanguest_three_one": "1-3", "bodanguest_three_two": "2-3", "bodanguest_four_zero": "0-4", "bodanguest_four_one": "1-4", "bodanguest_four_two": "2-4", "bodanguest_four_three": "3-4", "bodandogfall_zero_zero": "0-0", "bodandogfall_one_one": "1-1", "bodandogfall_two_two": "2-2", "bodandogfall_three_three": "3-3", "bodandogfall_four_four": "4-4", "bodanother": "其他", "halfbodanhome_one_zero": "1-0", "halfbodanhome_two_zero": "2-0", "halfbodanhome_two_one": "2-1", "halfbodanhome_three_zero": "3-0", "halfbodanhome_three_one": "3-1", "halfbodanhome_three_two": "3-2", "halfbodanguest_one_zero": "0-1", "halfbodanguest_two_zero": "0-2", "halfbodanguest_two_one": "1-2", "halfbodanguest_three_zero": "0-3", "halfbodanguest_three_one": "1-3", "halfbodanguest_three_two": "2-3", "halfbodandogfall_zero_zero": "0-0", "halfbodandogfall_one_one": "1-1", "halfbodandogfall_two_two": "2-2", "halfbodandogfall_three_three": "3-3", "halfbodanother": "其他"} bodan_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} if bodan_datas: for key, value in bodan_datas.items(): try: zqtwo_intodb(data1=value, data2=bodan_data, data3=key, data4=bodan_dict[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球bodan_datas插入错误') logger.warning(e) # 最先进球/最后进球 ------------------------------------------------------------------------------------------------------ first_last_balls = item['first_last_ball'] p_code, p_id = get_pcode(corner_ball, 'first_last_ball') first_last_dict = {"first_last_ball": "最先进球", "first_ball_home": "最先进球", "first_ball_guest": "最先进球", "last_ball_home": "最后进球", "last_ball_guest": "最后进球", "not_ball": "没有进球"} first_last_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} if first_last_balls: for key, value in first_last_balls.items(): # 构建唯一索引 try: zqtwo_intodb(data1=value, data2=first_last_data, data3=key, data4=first_last_dict[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球first_last_balls插入错误') logger.warning(e) p_code, p_id = get_pcode(corner_ball, 'tema_ball') full_dicts = item['full_data'] half_dicts = item['half_data'] full_dict_rules = item['full_data_rule'] half_dict_rules = item['half_data_rule'] data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} if full_dicts: for key, value in full_dicts.items(): try: zqtwo_intodb(data1=value, data2=data, data3=key, data4=full_dict_rules[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球full_dicts插入错误') logger.warning(e) if half_dicts: for key, value in half_dicts.items(): try: zqtwo_intodb(data1=value, data2=data, data3=key, data4=half_dict_rules[key], cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('足球half_dicts插入错误') logger.warning(e) match_date, match_time, time3 = new_time(ctime) n_time = out_time(time3, 1.5) # 插入st_zq_competition表 if zuqiu == "足球": try: if pt == 1: data_competition = {'team_home': team_home, 'team_guest': team_guest, 'league_id': league_id, 'game_id': competition_id, 'match_date': match_date, 'match_time': match_time, 'utime': utime, 'number': number, 'source': "hg3535", "expire_time": n_time, 'is_today': 1, "us_time": ctime} zqone_competition(data=data_competition, cursor=self.cursor) if pt == 2: data_competition = {'team_home': team_home, 'team_guest': team_guest, 'league_id': league_id, 'game_id': competition_id, 'match_date': match_date, 'match_time': match_time, 'utime': utime, 'number': number, 'source': "hg3535", "expire_time": n_time, 'is_morningplate': 1, "us_time": ctime} zqtwo_competition(data=data_competition, cursor=self.cursor) if pt == 3: data_competition = {'team_home': team_home, 'team_guest': team_guest, 'league_id': league_id, 'game_id': competition_id, 'match_date': match_date, 'match_time': match_time, 'utime': utime, 'number': number, 'source': "hg3535", "expire_time": n_time, "is_stringscene": 1, "us_time": ctime} zqthree_competition(data=data_competition, cursor=self.cursor) except Exception as e: logger.warning('足球st_zq_competition插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 篮球 让球大小---------------------------------------------------------------------------------------------------------- class Lanqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # 使用twisted将mysql插入变成异步执行 logger = logging.getLogger(__name__) # # logger.warning(query.addErrback(self.handle_error, item, spider)) # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 team_home = item['team_home'] # 球队2 team_guest = item['team_guest'] # 数量(97>) number = item['number'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + " " + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] concedes_dict = item['concede'] concedes_dict_rule = item['concede_rule'] odd_evens_dict = item['odd_even'] odd_evens_dict_rule = item['odd_even_rule'] total_sizes_dict = item['total_size'] total_sizes_dict_rule = item['total_size_rule'] last_numbers_dict = item['last_number'] capots_dict = item['capot'] team_scores_dict = item['team_score'] team_scores_dict_rule = item['team_score_rule'] # 让球 lanqiu = item['lanqiu'] concede = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 1, 'p_code': "concede", 'source': "hg3535", 'expire_time': expire_time} # 让球 数据插入数据库 try: one_intodb(data1=concedes_dict, data2=concede, data3=concedes_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球concedes_dict插入错误') logger.warning(e) two_sides = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 2, 'p_code': "two_sides", 'source': "hg3535", 'expire_time': expire_time} # 总分单双 数据插入数据库 try: one_intodb(data1=odd_evens_dict, data2=two_sides, data3=odd_evens_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球odd_evens_dict插入错误') logger.warning(e) total_size = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 3, 'p_code': "total_size", 'source': "hg3535", 'expire_time': expire_time} # 全场总分大小 数据插入数据库 try: one_intodb(data1=total_sizes_dict, data2=total_size, data3=total_sizes_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球total_sizes_dict插入错误') logger.warning(e) data4 = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 6, 'p_code': "team_score", 'source': "hg3535", 'expire_time': expire_time} # 全场总分大小 数据插入数据库 try: one_intodb(data1=team_scores_dict, data2=data4, data3=team_scores_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球team_scores_dict插入错误') logger.warning(e) # 主队进球最后一位数 last_number_home = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 4, 'p_code': "last_number", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} try: two_intodb(data1=last_numbers_dict['last_home'], data2=last_number_home, data3="last_home", cursor=self.cursor,redis_db=self.redis_db) except Exception as e: logger.warning('篮球last_numbers_dict主队插入错误') logger.warning(e) # print(last_numbers_dict['last_home']) # 客队进球最后一位数 last_number_guest = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 4, 'p_code': "last_number", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} try: two_intodb(data1=last_numbers_dict['last_guest'], data2=last_number_guest, data3="last_guest", cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球last_numbers_dict客队插入错误') logger.warning(e) # print(last_numbers_dict['last_guest']) # 独赢 capot_data = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 5, 'p_code': "capot", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} try: three_intodb(data1=capots_dict, data2=capot_data, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('篮球capots_dict插入错误') logger.warning(e) match_date, match_time, time3 = new_time(ctime) n_time = out_time(time3, 1.5) us_time = ctime # 插入赛事表 if lanqiu == "篮球": try: if pt == 1: Competition_sql = "insert into st_lq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_today,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_today=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) if pt == 2: Competition_sql = "insert into st_lq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_morningplate,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_morningplate=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) if pt == 3: Competition_sql = "insert into st_lq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_stringscene,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_stringscene=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) except Exception as e: logger.warning('篮球st_lq_competition插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 网球 让球&大小盘-------------------------------------------------------------------------------------------------------- class Wangqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) # logger.warning(query.addErrback(self.handle_error, item, spider)) # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 team_home = item['team_home'] # 球队2 team_guest = item['team_guest'] # 数量(97>) number = item['number'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + " " + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] # 让盘 concedes_dict = item['concedes_dict'] concedes_dict_rule = item['concedes_dict_rule'] # 冠军 kemps_dict = item['kemps_dict'] # 让局 bureaus_dict = item['bureaus_dict'] bureaus_dict_rule = item['bureaus_dict_rule'] # 总局数大小 total_number_dict = item['total_number_dict'] total_number_dict_rule = item['total_number_dict_rule'] # 总局数单双 odd_evens_dict = item['odd_evens_dict'] odd_evens_dict_rule = item['odd_evens_dict_rule'] wangqiu = item['wangqiu'] # 让盘 concede = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 1, 'p_code': "dishes", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: wqone_intodb(data1=concedes_dict, data2=concede, data3=concedes_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('网球concedes_dict插入错误') logger.warning(e) # 让局 bureaus = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 3, 'p_code': "bureau", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: wqone_intodb(data1=bureaus_dict, data2=bureaus, data3=bureaus_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('网球bureaus_dict插入错误') logger.warning(e) total_number = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 4, 'p_code': "total_number", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: wqone_intodb(data1=total_number_dict, data2=total_number, data3=total_number_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('网球total_number_dict插入错误') logger.warning(e) odd_evens = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 5, 'p_code': "two_game_sides", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: wqone_intodb(data1=odd_evens_dict, data2=odd_evens, data3=odd_evens_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('网球odd_evens_dict插入错误') logger.warning(e) match_date, match_time, time3 = new_time(ctime) n_time = out_time(time3, 3) if kemps_dict: for key, value in kemps_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=2) r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=2, odd=value) odds_only = r_hash if self.redis_db.hexists("hg3535_wangqiu", r_hash): pass else: self.redis_db.hset("hg3535_wangqiu", r_hash, 0) try: sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,odds_only,expire_time) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime, value, 2, "kemp", 0, "hg3535", new_hash, odds_only,expire_time, utime, value, odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source,odds_only) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 2, "kemp", 0, "hg3535", odds_only)) except Exception as e: logger.warning('网球kemps_dict插入错误') logger.warning(e) us_time = ctime # 插入赛事表 if wangqiu == "网球": try: if pt is 1: Competition_sql = "insert into st_wq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_today,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_today=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) if pt is 2: Competition_sql = "insert into st_wq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_morningplate,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_morningplate=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) if pt is 3: Competition_sql = "insert into st_wq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_stringscene,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_stringscene=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) except Exception as e: logger.warning('网球st_wq_competition插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 网球 波胆-------------------------------------------------------------------------------------------------------------- class Wqbodanpipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # 使用twisted将mysql插入变成异步执行 logger = logging.getLogger(__name__) # logger.warning(query.addErrback(self.handle_error, item, spider)) # 比赛日期 try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + " " + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 比赛id competition_id = item['game_id'] # 联赛id league_id = item['league_id'] # 联赛name league_name = item['league_name'] # 主队 team_home = item['team_home'] # 客队 team_guest = item['team_guest'] # 主队得分 score_home = item['score_home'] # 客队得分 score_guest = item['score_guest'] # number number = item['number'] corner_ball = item['corner_ball'] half_way = item['half_way'] # 类型早盘，今日，滚球，串场 pt = item['pt'] bodan_datas = item['bodan_data'] p_code = "bodan" p_id = 7 bodan_dict = {"bodanhome_two_zero": "2-0", "bodanhome_two_one": "2-1", "bodanhome_three_zero": "3-0", "bodanhome_three_one": "3-1", "bodanhome_three_two": "3-2", "bodanhome_four_zero": "4-0", "bodanhome_four_one": "4-1", "bodanhome_four_two": "4-2", "bodanhome_four_three": "4-3", "bodanguest_two_zero": "0-2", "bodanguest_two_one": "1-2", "bodanguest_three_zero": "0-3", "bodanguest_three_one": "1-3", "bodanguest_three_two": "2-3", "bodanguest_four_zero": "0-4", "bodanguest_four_one": "1-4", "bodanguest_four_two": "2-4", "bodanguest_four_three": "3-4"} if bodan_datas: for key, value in bodan_datas.items(): if value: new_hash = hash_func(match_id=competition_id, odds_code=key, sort=0, p_id=7) r_hash = r_func(match_id=competition_id, odds_code=key, sort=0, p_id=7, odd=value) odds_only = r_hash if self.redis_db.hexists("hg3535_wangqiu", r_hash): pass else: self.redis_db.hset("hg3535_wangqiu", r_hash, 0) try: sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole, condition, expire_time,odds_only) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(sole) do update set utime=%s,odds=%s,expire_time=%s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(competition_id), utime, utime, value, p_id, p_code, 0, "hg3535", new_hash, bodan_dict[key], expire_time, odds_only,utime, value, expire_time, odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s);" self.cursor.execute(sql2, ( int(league_id), key, int(competition_id), utime, utime, value, p_id, p_code, 0, "hg3535", key,odds_only)) except Exception as e: logger.warning('网球波胆插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 棒球 让球&大小盘-------------------------------------------------------------------------------------------------------- class Bangqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # print(item) logger = logging.getLogger(__name__) # logger.warning(query.addErrback(self.handle_error, item, spider)) # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 team_home = item['team_home'] # 球队2 team_guest = item['team_guest'] # 数量(97>) number = item['number'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + " " + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] # 让球 concedes_dict = item['concedes_dict'] concedes_dict_rule = item['concedes_dict_rule'] # 独赢 capots_dict = item['capots_dict'] # 总得分大小 total_size_dict = item['total_size_dict'] total_size_dict_rule = item['total_size_dict_rule'] # 总得分单双 odd_evens_dict = item['odd_evens_dict'] odd_evens_dict_rule = item['odd_evens_dict_rule'] bangqiu = item['bangqiu'] # 让球 concede = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 1, 'p_code': "concede", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: bqone_intodb(data1=concedes_dict, data2=concede, data3=concedes_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('棒球concede插入错误') logger.warning(e) # 总得分：大/小 total_size = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 2, 'p_code': "total_size", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: bqone_intodb(data1=total_size_dict, data2=total_size, data3=total_size_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('棒球total_size插入错误') logger.warning(e) odd_even = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 3, 'p_code': "two_sides", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt} try: bqone_intodb(data1=odd_evens_dict, data2=odd_even, data3=odd_evens_dict_rule, cursor=self.cursor, redis_db=self.redis_db) except Exception as e: logger.warning('棒球odd_even插入错误') logger.warning(e) # 赛事失效时间 match_date, match_time, time3 = new_time(ctime) n_time = out_time(time3, 3.5) # 插入独赢 if capots_dict: for key, value in capots_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=4) r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=4, odd=value) odds_only = r_hash if self.redis_db.hexists("hg3535_bangqiu", r_hash): pass else: self.redis_db.hset("hg3535_bangqiu", r_hash, 0) try: sql1 = "insert into st_bq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,odds_only,expire_time) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s,expire_time=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime, value, 4, "capot", 0, "hg3535", new_hash, odds_only, expire_time,utime, value, odds_only, expire_time)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_bq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source,odds_only) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 4, "capot", 0, "hg3535", odds_only)) except Exception as e: logger.warning('棒球独赢插入错误') logger.warning(e) us_time = ctime # 插入赛事表 if bangqiu == '棒球': try: if pt == 1: Competition_sql = "insert into st_bq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_today,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_today=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time, 1, us_time, number, n_time, 1)) if pt == 2: Competition_sql = "insert into st_bq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_morningplate,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_morningplate=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) if pt == 3: Competition_sql = "insert into st_bq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,source,expire_time,is_stringscene,us_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s) on conflict(match_id) do update set tag = %s,expire_time=%s,is_stringscene=%s;" self.cursor.execute(Competition_sql, ( team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, "hg3535", n_time,1, us_time, number, n_time, 1)) except Exception as e: logger.warning('棒球st_bq_competition插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 足球 篮球 网球 棒球 冠军------------------------------------------------------------------------------------------------ class Guanjunpipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) data_game = item['data_game'].replace('年', "-").replace('月', "-").replace('日', "") time_game = item['time_game'] ctime = data_game + " " + time_game + ":00" utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) league_name = item['league_name'] tema_home = item['tema_home'] league_id = item['league_id'] game_id = item['game_id'] new_league_name = item['new_league_name'] # 冠军赔率 champion_team = item['champion_team'] new_champion = item['new_champion'] # 构建唯一哈希索引 time1 = time.mktime(time.strptime(ctime, '%Y-%m-%d %H:%M:%S')) + 43200 time2 = time.localtime(time1) time3 = time.strftime('%Y-%m-%d %H:%M:%S', time2) # data_time = str(time3).split(" ") # match_date,match_time = new_time(ctime) pt = item['pt'] new_hash = hash_func(match_id=champion_team, odds_code=new_champion, sort=new_league_name, p_id=tema_home) ball = item['ball'] # 插入st_zq_odds表 r_hash = hash_func(league_id, new_champion, tema_home, champion_team) odds_only = r_hash s_hash = hash_func(league_id, league_name, ball, 0) if ball == "足球": n_gameid = int('1' + str(league_id)) data_dict = {'league_id': league_id, 'new_champion': new_champion, 'utime': utime, 'champion_team': champion_team, "p_id": 8, 'new_league_name': new_league_name, 'tema_home': tema_home, 'new_hash': new_hash, 'expire_time': expire_time, "odds_only": odds_only, "league_name": league_name, 'time3': time3, 'game_id': n_gameid} try: gjz_into(data_dict, self.cursor, r_hash, s_hash, self.redis_db) Competition_sql = "insert into st_zq_competition(home_team, lg_id, match_id, ctime, utime, source, us_time) values (%s, %s, %s, %s, %s, %s, %s) on conflict(match_id) do update set utime=%s;" self.cursor.execute(Competition_sql, (league_name, league_id, n_gameid, utime, utime, 'hg3535', data_game, utime)) except Exception as e: logger.warning('足球冠军插入错误') logger.warning(e) elif ball == "篮球": n_gameid = int('2' + str(league_id)) data_dict = {'league_id': league_id, 'new_champion': new_champion, 'utime': utime, 'champion_team': champion_team, "p_id": 7, 'new_league_name': new_league_name, 'tema_home': tema_home, 'new_hash': new_hash, 'expire_time': expire_time, "odds_only": odds_only, "league_name": league_name, 'time3': time3, 'game_id': n_gameid} try: gjl_into(data_dict, self.cursor, r_hash, s_hash, self.redis_db) Competition_sql = "insert into st_lq_competition(home_team, lg_id, match_id, ctime, utime, source, us_time) values (%s, %s, %s, %s, %s, %s, %s) on conflict(match_id) do update set utime=%s;" self.cursor.execute(Competition_sql, (league_name, league_id, n_gameid, utime, utime, 'hg3535', data_game, utime)) except Exception as e: logger.warning('篮球冠军插入错误') logger.warning(e) elif ball == "网球": n_gameid = int('3' + str(league_id)) data_dict = {'league_id': league_id, 'new_champion': new_champion, 'utime': utime, 'champion_team': champion_team, "p_id": 6, 'new_league_name': new_league_name, 'tema_home': tema_home, 'new_hash': new_hash, 'expire_time': expire_time, "odds_only": odds_only, "league_name": league_name, 'time3': time3, 'game_id': n_gameid} try: gjw_into(data_dict, self.cursor, r_hash, s_hash, self.redis_db) Competition_sql = "insert into st_wq_competition(home_team, lg_id, match_id, ctime, utime, source, us_time) values (%s, %s, %s, %s, %s, %s, %s) on conflict(match_id) do update set utime=%s;" self.cursor.execute(Competition_sql, (league_name, league_id, n_gameid, utime, utime, 'hg3535', data_game, utime)) except Exception as e: logger.warning('网球冠军插入错误') logger.warning(e) elif ball == "棒球": n_gameid = int('4' + str(league_id)) data_dict = {'league_id': league_id, 'new_champion': new_champion, 'utime': utime, 'champion_team': champion_team, "p_id": 5, 'new_league_name': new_league_name, 'tema_home': tema_home, 'new_hash': new_hash, 'expire_time': expire_time, "odds_only": odds_only, "league_name": league_name, 'time3': time3, 'game_id': n_gameid} try: gjb_into(data_dict, self.cursor, r_hash, s_hash, self.redis_db) Competition_sql = "insert into st_bq_competition(home_team, lg_id, match_id, ctime, utime, source, us_time) values (%s, %s, %s, %s, %s, %s, %s) on conflict(match_id) do update set utime=%s;" self.cursor.execute(Competition_sql, (league_name, league_id, n_gameid, utime, utime, 'hg3535', data_game, utime)) except Exception as e: logger.warning('棒球冠军插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 足球 篮球 网球 棒球 联赛------------------------------------------------------------------------------------------------ class Liansaipipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) area_id = item["area_id"] area_name = item["area_name"] st_league = item["st_league"] name_chinese = item["name_chinese"] ball = item['ball'] utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) area_dict = {"南美洲": 3, "北美洲": 4, "欧洲": 5, "大洋洲": 6, "南极洲": 7, "非洲": 1, "世界": 8, "亚洲": 2} redis_data_dict = "hg3535_liansai" r_hash = hash_func(st_league, name_chinese, ball, 0) # url = 'http://sports.5gogo.com/Sports/WriteSports/setSports' # if area_name in area_dict: # data = { # "game_code": "zq", # "title": "league", # "data": { # "name_chinese": name_chinese, # "kind": "1", # "match_mode": "1", # "if_stop": "0", # "belong": area_name, # "last_time": "2019-01-01 00:00:00", # "lg_id": st_league, # "source": "hg3535" # } # } # else: # if area_name == "足球": # data = { # "game_code": "zq", # "title": "league", # "data": { # "name_chinese": name_chinese, # "kind": "1", # "match_mode": "1", # "if_stop": "0", # "belong": "中国", # "last_time": "2019-01-01 00:00:00", # "lg_id": st_league, # "source": "hg3535" # } # } # else: # data = { # "game_code": "zq", # "title": "league", # "data": { # "name_chinese": name_chinese, # "kind": "1", # "match_mode": "1", # "if_stop": "0", # "belong": 'area_name', # "last_time": "2019-01-01 00:00:00", # "lg_id": st_league, # "source": "hg3535" # } # } # Technology = {"Technology": "Django", "Count": "50"} # resp = requests.post('http://sports.5gogo.com/Sports/WriteSports/setSports', json=data) # print(resp) # print(resp.status_code) # print(resp.content) # data = json.dumps(data) # print(data) # r = requests.post(url, data) # print(r.content) # return item if self.redis_db.hexists(redis_data_dict, r_hash): pass else: self.redis_db.hset(redis_data_dict, r_hash, 0) if ball == "足球": try: if area_name in area_dict: sql1 = "insert into st_zq_league(name_chinese, lg_id, area_id,source,utime) values (%s, %s, %s, %s, %s) on conflict(lg_id) do update set area_id = %s,source=%s;" self.cursor.execute(sql1, (name_chinese, st_league, area_dict[area_name], "hg3535",utime, area_dict[area_name], "hg3535")) else: if area_name == "足球": sql2 = "insert into st_zq_league(name_chinese, lg_id, country_id,source) values (%s, %s, %s, %s) on conflict(lg_id) do update set source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, 0, "hg3535", "hg3535")) else: sql2 = "insert into st_zq_league(name_chinese, lg_id, country_id,source,utime) values (%s, %s, %s, %s, %s) on conflict(lg_id) do update set country_id = %s,source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, area_id, "hg3535",utime, area_id, "hg3535")) sql3 = "insert into st_country(country_id, name_chinese, country_area, country_order,recommend_order, update_time,source) values (%s, %s, %s,%s, %s, %s,%s) on conflict(country_id) do update set update_time =%s,source=%s;" self.cursor.execute(sql3, (area_id, area_name, 0, 0, 0, utime, "hg3535", utime, "hg3535")) except Exception as e: logger.warning("足球联赛插入错误") logger.warning(e) elif ball == "篮球": try: if area_name in area_dict: sql1 = "insert into st_lq_league(name_chinese, lg_id, area_id,source,utime) values (%s, %s, %s,%s,%s) on conflict(lg_id) do update set area_id = %s,source=%s;" self.cursor.execute(sql1, (name_chinese, st_league, area_dict[area_name], "hg3535",utime, area_dict[area_name],"hg3535")) else: if area_name == "篮球": sql2 = "insert into st_lq_league(name_chinese, lg_id, country_id,source) values (%s, %s, %s, %s) on conflict(lg_id) do update set source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, 0, "hg3535", "hg3535")) else: sql2 = "insert into st_lq_league(name_chinese, lg_id, country_id,source,utime) values (%s, %s, %s, %s, %s) on conflict(lg_id) do update set country_id = %s,source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, area_id, "hg3535",utime, area_id, "hg3535")) sql3 = "insert into st_country(country_id, name_chinese, country_area, country_order,recommend_order, update_time,source) values (%s, %s, %s,%s, %s, %s,%s) on conflict(country_id) do update set update_time =%s,source=%s;" self.cursor.execute(sql3, (area_id, area_name, 0, 0, 0, utime, "hg3535", utime, "hg3535")) except Exception as e: logger.warning("篮球联赛插入错误") logger.warning(e) elif ball == "网球": try: if area_name in area_dict: sql1 = "insert into st_wq_league(name_chinese, lg_id, area_id,source,utime) values (%s, %s, %s,%s,%s) on conflict(lg_id) do update set area_id = %s,source=%s;" self.cursor.execute(sql1, (name_chinese, st_league, area_dict[area_name], "hg3535",utime, area_dict[area_name],"hg3535")) else: if area_name == "网球": sql2 = "insert into st_wq_league(name_chinese, lg_id, country_id,source) values (%s, %s, %s,%s) on conflict(lg_id) do update set source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, 0, "hg3535", "hg3535")) else: sql2 = "insert into st_wq_league(name_chinese, lg_id, country_id,source,utime) values (%s, %s, %s,%s,%s) on conflict(lg_id) do update set country_id = %s,source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, area_id, "hg3535",utime, area_id, "hg3535")) sql3 = "insert into st_country(country_id, name_chinese, country_area, country_order,recommend_order, update_time,source) values (%s, %s, %s,%s, %s, %s,%s) on conflict(country_id) do update set update_time =%s,source=%s;" self.cursor.execute(sql3, (area_id, area_name, 0, 0, 0, utime, "hg3535", utime, "hg3535")) except Exception as e: logger.warning("网球联赛插入错误") logger.warning(e) elif ball == "棒球": try: if area_name in area_dict: sql1 = "insert into st_bq_league(name_chinese, lg_id, area_id,source,utime) values (%s, %s, %s,%s,%s) on conflict(lg_id) do update set area_id = %s,source=%s;" self.cursor.execute(sql1,(name_chinese, st_league, area_dict[area_name], "hg3535",utime, area_dict[area_name],"hg3535")) else: if area_name == "棒球": sql2 = "insert into st_bq_league(name_chinese, lg_id, country_id,source) values (%s, %s, %s,%s) on conflict(lg_id) do update set source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, 0, "hg3535", "hg3535")) else: sql2 = "insert into st_bq_league(name_chinese, lg_id, country_id,source,utime) values (%s, %s, %s,%s,%s) on conflict(lg_id) do update set country_id = %s,source=%s;" self.cursor.execute(sql2, (name_chinese, st_league, area_id, "hg3535",utime, area_id, "hg3535")) sql3 = "insert into st_country(country_id, name_chinese, country_area, country_order,recommend_order, update_time,source) values (%s, %s, %s,%s, %s, %s,%s) on conflict(country_id) do update set update_time =%s,source=%s;" self.cursor.execute(sql3, (area_id, area_name, 0, 0, 0, utime, "hg3535", utime, "hg3535")) except Exception as e: logger.warning("棒球联赛插入错误") logger.warning(e) def close_spider(self, spider): self.conn.commit() self.conn.close() # ---------------------------------------------------------滚球--------------------------------------------------------- # 滚球足球 插入 class Roll_Zuqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) # 比赛日期 try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) print(time_game) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + "" + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 比赛id competition_id = item['game_id'] # 联赛id league_id = item['league_id'] # 联赛name league_name = item['league_name'] # 主队 team_home = item['team_home'] # 客队 team_guest = item['team_guest'] # number number = item['number'] score_home = item['score_home'] score_guest = item['score_guest'] half_way = item['half_way'] pt = item['pt'] match_score = "{}:{}".format(score_home,score_guest) corner_ball = item['corner_ball'] p_code, p_id = get_pcode(corner_ball, 'concede_size') # 构建唯一索引 half_size_guest = item["half_size_guest"] half_size_guest_rule = item["half_size_guest_rule"] half_size_home = item["half_size_home"] half_size_home_rule = item["half_size_home_rule"] try: size_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} # 让球 数据插入数据库 zqone_intodbs(data1=half_size_home, data2=size_data, data3="half_size_home", data4=half_size_home_rule, cursor=self.cursor, redis_db=self.redis_db) # 让球 数据插入数据库 zqone_intodbs(data1=half_size_guest, data2=size_data, data3="half_size_guest", data4=half_size_guest_rule, cursor=self.cursor, redis_db=self.redis_db) # 全场场大小 size_guest = item["size_guest"] size_guest_rule = item["size_guest_rule"] size_home = item["size_home"] size_home_rule = item["size_home_rule"] zqone_intodbs(data1=size_guest, data2=size_data, data3="size_guest", data4=size_guest_rule, cursor=self.cursor, redis_db=self.redis_db) zqone_intodbs(data1=size_home, data2=size_data, data3="size_home", data4=size_home_rule, cursor=self.cursor, redis_db=self.redis_db) # 上半场大小 half_concede_home_rule = item["half_concede_home_rule"] half_concede_home = item["half_concede_home"] half_concede_guest_rule = item["half_concede_guest_rule"] half_concede_guest = item["half_concede_guest"] # 上半场让球 zqone_intodbs(data1=half_concede_home, data2=size_data, data3="half_concede_home", data4=half_concede_home_rule, cursor=self.cursor, redis_db=self.redis_db) zqone_intodbs(data1=half_concede_guest, data2=size_data, data3="half_concede_guest", data4=half_concede_guest_rule, cursor=self.cursor, redis_db=self.redis_db) concede_guest = item["concede_guest"] concede_guest_rule = item["concede_guest_rule"] concede_home = item["concede_home"] concede_home_rule = item["concede_home_rule"] # 全场让球 zqone_intodbs(data1=concede_guest, data2=size_data, data3="concede_guest", data4=concede_guest_rule, cursor=self.cursor, redis_db=self.redis_db) zqone_intodbs(data1=concede_home, data2=size_data, data3="concede_home", data4=concede_home_rule, cursor=self.cursor,redis_db=self.redis_db) # p_code, p_id = get_pcode(corner_ball, 'capot') # 独赢------------------------------------------------------------------------------------------------------------------- half_capot_home = item["half_capot_home"] half_capot_guest = item["half_capot_guest"] half_capot_dogfall = item["half_capot_dogfall"] capot_home = item["capot_home"] capot_guest = item["capot_guest"] capot_dogfall = item["capot_dogfall"] capot_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} # 上半场独赢 主队 zqtwo_intodbs(data1=half_capot_home, data2=capot_data, data3="half_capot_home", data4='1', cursor=self.cursor,redis_db=self.redis_db) # 上半场独赢 客队 zqtwo_intodbs(data1=half_capot_guest, data2=capot_data, data3="half_capot_guest", data4='2', cursor=self.cursor,redis_db=self.redis_db) # 上半场独赢 和 zqtwo_intodbs(data1=half_capot_dogfall, data2=capot_data, data3="half_capot_dogfall", data4='x', cursor=self.cursor,redis_db=self.redis_db) # # 全场独赢 主队 zqtwo_intodbs(data1=capot_home, data2=capot_data, data3="capot_home", data4='1', cursor=self.cursor,redis_db=self.redis_db) # 全场独赢 客队 zqtwo_intodbs(data1=capot_guest, data2=capot_data, data3="capot_guest", data4='2', cursor=self.cursor,redis_db=self.redis_db) # 全场独赢 和 zqtwo_intodbs(data1=capot_dogfall, data2=capot_data, data3="capot_dogfall", data4='x', cursor=self.cursor,redis_db=self.redis_db) # # 入球数单双------------------------------------------------------------------------------------------------------------- p_code, p_id = get_pcode(corner_ball, 'two_sides') odd_even_odd = item["odd_even_odd"] odd_even_even = item["odd_even_even"] half_odd_even_odd = item["half_odd_even_odd"] half_odd_even_even = item["half_odd_even_even"] # 全场入球数 单双 two_sides_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} # 上半场入球数 单双 zqtwo_intodbs(data1=odd_even_odd, data2=two_sides_data, data3="two_sides_single", data4='单', cursor=self.cursor,redis_db=self.redis_db) zqtwo_intodbs(data1=odd_even_even, data2=two_sides_data, data3="two_sides_double", data4='双', cursor=self.cursor,redis_db=self.redis_db) # 全场入球数 单双 zqtwo_intodbs(data1=half_odd_even_odd, data2=two_sides_data, data3="half_two_sides_single", data4='单', cursor=self.cursor, redis_db=self.redis_db) zqtwo_intodbs(data1=half_odd_even_even, data2=two_sides_data, data3="half_two_sides_double", data4='双', cursor=self.cursor, redis_db=self.redis_db) # 总入球数 -------------------------------------------------------------------------------------------------------------- p_code, p_id = get_pcode(corner_ball, 'total_goal') total_goals = item['total_goal'] total_dict = {'total_goal_zero': '0-1', 'total_goal_two': '2-3', 'total_goal_four': '4-6', 'total_goal_seven': '7或以上', 'half_total_goal_zero': '0', "half_total_goal_one": '1', "half_total_goal_two": '2', "half_total_goal_three": '3或以上'} # 全场入球数 单双 total_goal_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} # 上半场入球数 单双 for key, value in total_goals.items(): if value: zqtwo_intodbs(data1=value, data2=total_goal_data, data3=key, data4=total_dict[key], cursor=self.cursor, redis_db=self.redis_db) # 全场半场 -------------------------------------------------------------------------------------------------------------- half_fulls = item['half_full'] p_code, p_id = get_pcode(corner_ball, 'half_full') full_dict = {"half_full_home_home": "主主", "half_full_home_dogfall": "主和", "half_full_home_guest": "主客", "half_full_dogfall_home": "和主", "half_full_dogfall_dogfall": "和和", "half_full_dogfall_guest": "和客", "half_full_guest_home": "客主", "half_full_guest_dogfall": "客和", "half_full_guest_guest": "客客"} half_full_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} if half_fulls: for key, value in half_fulls.items(): if value: zqtwo_intodbs(data1=value, data2=half_full_data, data3=key, data4=full_dict[key], cursor=self.cursor, redis_db=self.redis_db) # 波胆------------------------------------------------------------------------------------------------------------------ bodan_datas = item['bodan_data'] p_code, p_id = get_pcode(corner_ball, 'bodan') bodan_dict = {"bodanhome_one_zero": "1-0", "bodanhome_two_zero": "2-0", "bodanhome_two_one": "2-1", "bodanhome_three_zero": "3-0", "bodanhome_three_one": "3-1", "bodanhome_three_two": "3-2", "bodanhome_four_zero": "4-0", "bodanhome_four_one": "4-1", "bodanhome_four_two": "4-2", "bodanhome_four_three": "4-3", "bodanguest_one_zero": "0-1", "bodanguest_two_zero": "0-2", "bodanguest_two_one": "1-2", "bodanguest_three_zero": "0-3", "bodanguest_three_one": "1-3", "bodanguest_three_two": "2-3", "bodanguest_four_zero": "0-4", "bodanguest_four_one": "1-4", "bodanguest_four_two": "2-4", "bodanguest_four_three": "3-4", "bodandogfall_zero_zero": "0-0", "bodandogfall_one_one": "1-1", "bodandogfall_two_two": "2-2", "bodandogfall_three_three": "3-3", "bodandogfall_four_four": "4-4", "bodanother": "其他", "halfbodanhome_one_zero": "1-0", "halfbodanhome_two_zero": "2-0", "halfbodanhome_two_one": "2-1", "halfbodanhome_three_zero": "3-0", "halfbodanhome_three_one": "3-1", "halfbodanhome_three_two": "3-2", "halfbodanguest_one_zero": "0-1", "halfbodanguest_two_zero": "0-2", "halfbodanguest_two_one": "1-2", "halfbodanguest_three_zero": "0-3", "halfbodanguest_three_one": "1-3", "halfbodanguest_three_two": "2-3", "halfbodandogfall_zero_zero": "0-0", "halfbodandogfall_one_one": "1-1", "halfbodandogfall_two_two": "2-2", "halfbodandogfall_three_three": "3-3", "halfbodanother": "其他"} bodan_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} if bodan_datas: for key, value in bodan_datas.items(): zqtwo_intodbs(data1=value, data2=bodan_data, data3=key, data4=bodan_dict[key], cursor=self.cursor, redis_db=self.redis_db) # 最先进球/最后进球 ------------------------------------------------------------------------------------------------------ first_last_balls = item['first_last_ball'] p_code, p_id = get_pcode(corner_ball, 'first_last_ball') first_last_dict = {"first_last_ball": "最先进球", "first_ball_home": "最先进球", "first_ball_guest": "最先进球", "last_ball_home": "最后进球", "last_ball_guest": "最后进球", "not_ball": "没有进球"} first_last_data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} if first_last_balls: for key, value in first_last_balls.items(): # 构建唯一索引 zqtwo_intodbs(data1=value, data2=first_last_data, data3=key, data4=first_last_dict[key], cursor=self.cursor, redis_db=self.redis_db) p_code, p_id = get_pcode(corner_ball, 'temaball') full_dicts = item['full_data'] half_dicts = item['half_data'] full_dict_rules = item['full_data_rule'] half_dict_rules = item['half_data_rule'] data = {'league_id': league_id, 'game_id': competition_id, 'utime': utime, 'p_id': p_id, 'p_code': p_code, 'source': "hg3535", 'expire_time': expire_time, 'sort': 0, 'pt': pt,'match_score':match_score,'team_home':team_home,'team_guest':team_guest,'score_home':score_home,'score_guest':score_guest,'number':number,'time_game':time_game,'half_way':half_way} if full_dicts: for key, value in full_dicts.items(): zqtwo_intodbs(data1=value, data2=data, data3=key, data4=full_dict_rules[key], cursor=self.cursor, redis_db=self.redis_db) if half_dicts: for key, value in half_dicts.items(): zqtwo_intodbs(data1=value, data2=data, data3=key, data4=half_dict_rules[key], cursor=self.cursor, redis_db=self.redis_db) #这日期在这里有问题 先行注释 # match_date, match_time, time3 = news_time(ctime) # n_time = out_time(time3, 1.5) # 插入st_zq_competition表 # if pt == 0: data_competition = {'team_home': team_home, 'team_guest': team_guest, 'league_id': league_id, 'game_id': competition_id, 'match_date': r_ctime, 'match_time': '00:00:00', 'utime': utime, 'number': number, 'source': "hg3535", "expire_time": expire_time, 'is_rollball': 1, "us_time":'2019-01-01 00:00:00','time_game':time_game} zqone_competitions(data=data_competition, cursor=self.cursor) except Exception as e: logger.warning(e) return item def close_spider(self, spider): # self.conn.close() self.conn.commit() self.conn.close() # 滚球篮球 插入 class Roll_Lanqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # 使用twisted将mysql插入变成异步执行 logger = logging.getLogger(__name__) # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 #home_team team_home = item['team_home'] # 球队2 # guest_team team_guest = item['team_guest'] # 数量(97>) number = item['number'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + "" + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60))#过期时间 # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] match_score = "{}:{}".format(score_home,score_guest) concedes_dict = item['concede'] concedes_dict_rule = item['concede_rule'] odd_evens_dict = item['odd_even'] odd_evens_dict_rule = item['odd_even_rule'] total_sizes_dict = item['total_size'] total_sizes_dict_rule = item['total_size_rule'] last_numbers_dict = item['last_number'] capots_dict = item['capot'] team_scores_dict = item['team_score'] team_scores_dict_rule = item['team_score_rule'] try: if concedes_dict: for key, value in concedes_dict.items(): if value: for x, y in enumerate(value): new_hash = hash_func(match_id=game_id, odds_code=key, sort=x, p_id=1) odds_only = r_func(match_id=game_id, odds_code=key, sort=x, p_id=1,odd=y) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only =%s;" self.cursor.execute(sql1, (int(league_id), key, int(game_id), utime, utime, y, 1, "concede",x, "hg3535", new_hash, concedes_dict_rule[key][x],odds_only,1,utime, y,odds_only)) # res = cursor.fetchone() # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), key, int(game_id), utime, utime,y, 1, "concede", x, "hg3535", concedes_dict_rule[key][x],odds_only,1)) #插入结果表 sql3 = "insert into st_lq_result(lg_id, home_team, guest_team, home_score, guest_score, all_goal, first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,u_home_score,u_guest_score,source,status) values (%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_score = %s,match_time =%s,match_process =%s,home_score =%s,guest_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,score_home, score_guest, number, '', '',match_score,'',utime,time_game,jijie,number,int(game_id),0,0,'hg3535',1,utime,match_score,time_game,jijie,score_home,score_guest)) #插入记录表 sql4 = "insert into st_lq_result_record(lg_id, home_team, guest_team, home_score, guest_score, first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,source,status) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s);" self.cursor.execute(sql4, (int(league_id), team_home, team_guest,score_home, score_guest, '', '',match_score,'',utime,time_game,jijie,number,int(game_id),'hg3535',1)) if odd_evens_dict: for key, value in odd_evens_dict.items(): if value: for x, y in enumerate(value): new_hash = hash_func(match_id=game_id, odds_code=key, sort=x, p_id=2) odds_only = r_func(match_id=game_id, odds_code=key, sort=x, p_id=2,odd=y) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only =%s;" self.cursor.execute(sql1, (int(league_id), key, int(game_id), utime, utime,y, 2, "two_sides",x, "hg3535", new_hash, odd_evens_dict_rule[key][x],odds_only,1,utime, y,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s);" self.cursor.execute(sql2, (int(league_id), key, int(game_id), utime, utime,y, 2, "two_sides", x, "hg3535", odd_evens_dict_rule[key][x],odds_only,1)) if total_sizes_dict: for key, value in total_sizes_dict.items(): if value: for x, y in enumerate(value): new_hash = hash_func(match_id=game_id, odds_code=key, sort=x, p_id=3) odds_only = r_func(match_id=game_id, odds_code=key, sort=x, p_id=3,odd=y) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only =%s;" self.cursor.execute(sql1, (int(league_id), key, int(game_id), utime, utime,y, 3, "total_size",x, "hg3535", new_hash, total_sizes_dict_rule[key][x],odds_only,1,utime, y,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), key, int(game_id), utime, utime, y, 3, "total_size", x, "hg3535", total_sizes_dict_rule[key][x],odds_only,1)) last_home = last_numbers_dict['last_home'] if last_home: for key, value in last_home.items(): if value: new_hash = hash_func(match_id=game_id, odds_code="last_home" + key, sort=0, p_id=4) odds_only = r_func(match_id=game_id, odds_code="last_home", sort=0, p_id=4,odd=value) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, (int(league_id), "last_home", int(game_id), utime, utime, value, 4, "last_number",0, "hg3535", new_hash, key,odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), "last_home", int(game_id), utime, utime,value, 4, "last_number", 0, "hg3535", key,odds_only,1)) last_guest = last_numbers_dict['last_guest'] if last_guest: for key, value in last_guest.items(): if value: new_hash = hash_func(match_id=game_id, odds_code="last_guest" + key, sort=0, p_id=4) odds_only = r_func(match_id=game_id, odds_code="last_guest", sort=0, p_id=4,odd=value) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, (int(league_id), "last_guest", int(game_id), utime, utime, value, 4, "last_number",0, "hg3535", new_hash, key,odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), "last_guest", int(game_id), utime, utime,value, 4, "last_number", 0, "hg3535", key,odds_only,1)) if capots_dict: for key, value in capots_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=5) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=5,odd=value) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,odds_only,is_rollball) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only =%s;" self.cursor.execute(sql1, (int(league_id), key, int(game_id), utime, utime, value, 5, "capot",0, "hg3535", new_hash, odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source,odds_only,is_rollball) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), key, int(game_id), utime, utime, value, 5, "capot", 0, "hg3535",odds_only,1)) if team_scores_dict: for key, value in team_scores_dict.items(): if value: for x, y in enumerate(value): new_hash = hash_func(match_id=game_id, odds_code=key, sort=x, p_id=6) odds_only = r_func(match_id=game_id, odds_code=key, sort=x, p_id=6,odd=y) sql1 = "insert into st_lq_odds(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, (int(league_id), key, int(game_id), utime, utime, y, 6, "team_score", x, "hg3535", new_hash,team_scores_dict_rule[key][x], odds_only,1,utime, y,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_lq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, (int(league_id), key, int(game_id), utime, utime,y, 6, "team_score", x, "hg3535",team_scores_dict_rule[key][x],odds_only,1)) # 插入赛事表 Competition_sql = "insert into st_lq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,type,source,expire_time,is_rollball,status) values (%s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s,%s,%s) on conflict(match_id) do update set tag = %s,expire_time = %s;" print(Competition_sql) self.cursor.execute(Competition_sql, (team_home, team_guest, league_id, game_id, r_ctime, time_game, utime, utime, number, pt, "hg3535",expire_time,1,1,number,expire_time)) except Exception as e: logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 滚球网球 插入 class Roll_Wangqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 team_home = item['team_home'] # 球队2 team_guest = item['team_guest'] # 数量(97>) number = item['number'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] try: data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) except Exception as e: logger.warning(e) data_game = item['data_game'].split(" ") months = str(data_game[1].strip()) month_dict = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'} month = month_dict[months] day = str(data_game[0]) # 比赛时间 time_game = str(item['time_game']) # 比赛时间，时间戳 ctime = "2019" + "-" + month + "-" + day + "" + time_game + ":00" r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] #取不到 暂时注掉 match_date, match_time = new_times(ctime) # 让盘 concedes_dict = item['concedes_dict'] concedes_dict_rule = item['concedes_dict_rule'] # 冠军 独赢 kemps_dict = item['kemps_dict'] # 让局 bureaus_dict = item['bureaus_dict'] bureaus_dict_rule = item['bureaus_dict_rule'] # 总局数大小 total_number_dict = item['total_number_dict'] total_number_dict_rule = item['total_number_dict_rule'] # 总局数单双 odd_evens_dict = item['odd_evens_dict'] odd_evens_dict_rule = item['odd_evens_dict_rule'] if concedes_dict: for key, value in concedes_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=1) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=1,odd=value) sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 1, "dishes_home", 0, "hg3535", new_hash, concedes_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 1, "dishes_home", 0, "hg3535", concedes_dict_rule[key],odds_only,1)) #插入结果表 sql3 = "insert into st_wq_result(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_process =%s,home_player_score =%s,guest_player_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00',utime,jijie,score_home,score_guest)) # 插入记录表 sql4 = "insert into st_wq_result_record(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" self.cursor.execute(sql4, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00')) if kemps_dict: for key, value in kemps_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=2) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=2,odd=value) sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime, value, 2, "kemp", 0, "hg3535", new_hash,odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime,value, 2, "kemp", 0, "hg3535",odds_only,1)) sql3 = "insert into st_wq_result(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_process =%s,home_player_score =%s,guest_player_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00',utime,jijie,score_home,score_guest)) if bureaus_dict: for key, value in bureaus_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=3) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=3,odd=value) sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only =%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime, value, 3, "bureau", 0, "hg3535", new_hash, bureaus_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime,value, 3, "bureau", 0, "hg3535", bureaus_dict_rule[key],odds_only,1)) sql3 = "insert into st_wq_result(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_process =%s,home_player_score =%s,guest_player_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00',utime,jijie,score_home,score_guest)) if total_number_dict: for key, value in total_number_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=4) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=4,odd=value) sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 4, "total_number", 0, "hg3535", new_hash, total_number_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 4, "total_number", 0, "hg3535", total_number_dict_rule[key],odds_only,1)) sql3 = "insert into st_wq_result(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_process =%s,home_player_score =%s,guest_player_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00',utime,jijie,score_home,score_guest)) if odd_evens_dict: for key, value in odd_evens_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=5) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=5,odd=value) sql1 = "insert into st_wq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 5, "two_game_sides", 0, "hg3535", new_hash, odd_evens_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_wq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 5, "two_game_sides", 0, "hg3535",odd_evens_dict_rule[key],odds_only,1)) sql3 = "insert into st_wq_result(lg_id, home_player_name, guest_player_name, home_player_let_plate, guest_player_let_plate, all_inning,home_player_score,guest_player_score,status,first_score_player,last_score_player,first_inning_score,second_inning_score,third_inning_score,match_winer_player,update_time,match_process,tag,match_id,source,match_time) values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_process =%s,home_player_score =%s,guest_player_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,int(score_home), int(score_guest), 0, score_home,score_guest,1,'','','','','','',utime,jijie,number,game_id,'hg3535','00:00',utime,jijie,score_home,score_guest)) # 插入赛事表 Competition_sql = "insert into st_wq_competition(home_team, guest_team, lg_id, match_id, match_date, match_time,ctime, utime, tag,type,source,is_rollball,status) values (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s,%s) on conflict(match_id) do update set tag = %s;" self.cursor.execute(Competition_sql, (team_home, team_guest, league_id, game_id, match_date, match_time, utime, utime, number, pt, "hg3535", 1, 1, number)) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 滚球棒球 插入 class Roll_Banqiupipeline(object): def open_spider(self, spider): self.pool = redis.ConnectionPool(host=settings["R_HOST"], port=settings["R_POST"], password=settings["R_PASSWORD"]) self.redis_db = redis.StrictRedis(connection_pool=self.pool) self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): # 使用twisted将mysql插入变成异步执行 # 联赛id league_id = item['league_id'] # 联赛名 league_name = item['league_name'] # result = item['result'] # 比赛id game_id = item['game_id'] # 球队1 team_home = item['team_home'] # 球队2 team_guest = item['team_guest'] # 数量(97>) number = item['number'] #新增 match_score_dict match_score_dict = item['match_score_dict'] # 比赛状态 zhuangtai = item['zhuangtai'] # 日期 # data_game = item['data_game'] data_game = item['data_game'].split("/") month = str(data_game[1].strip()) day = str(data_game[0]) # 比赛时间 # time_game = str(item['time_game']) # 比赛时间，时间戳 # ctime = "2019" + "-" + month + "-" + day + "" + time_game + ":00".strip() r_ctime = "2019" + "-" + month + "-" + day # 现在时间，时间戳 utime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) expire_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time() + 60)) # 队1分数 score_home = item['score_home'] # 队2分数 score_guest = item['score_guest'] # 第几节 jijie = item['jijie'] # 球队得分 qiudui = item['qiudui'] pt = item['pt'] #滚球这个位置获取不到这些字段 # match_date, match_time = new_time(ctime) # 让球 concedes_dict = item['concedes_dict'] concedes_dict_rule = item['concedes_dict_rule'] # 独赢 capots_dict = item['capots_dict'] # 总得分大小 total_size_dict = item['total_size_dict'] total_size_dict_rule = item['total_size_dict_rule'] # 总得分单双 odd_evens_dict = item['odd_evens_dict'] odd_evens_dict_rule = item['odd_evens_dict_rule'] # 让球 concede = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 1, 'p_code': "concede", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # bqone_intodb(data1=concedes_dict, data2=concede, data3=concedes_dict_rule, cursor=self.cursor) # 总得分：大/小 total_size = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 2, 'p_code': "total_size", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # bqone_intodb(data1=total_size_dict, data2=total_size, data3=total_size_dict_rule, cursor=self.cursor) odd_even = {'league_id': league_id, 'game_id': game_id, 'utime': utime, 'p_id': 3, 'p_code': "two_sides", 'source': "hg3535", 'expire_time': expire_time, 'sort': 0} # bqone_intodb(data1=odd_evens_dict, data2=odd_even, data3=odd_evens_dict_rule, cursor=self.cursor) # 赛事失效时间 # n_time = out_time(ctime, 3.5) # 插入独赢 if capots_dict: for key, value in capots_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=2) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=2,odd=value) sql1 = "insert into st_bq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,odds_only,expire_time,is_rollball) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s,expire_time=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime, value, 4, "capot", 0, "hg3535", new_hash, odds_only, expire_time,1,utime, value, odds_only,expire_time)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_bq_odds_record(lg_id, odds_code, match_id, ctime, utime,odds, p_id, p_code, sort, source,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 4, "capot", 0, "hg3535", odds_only,1)) res = json.dumps(match_score_dict) sql3 = "insert into st_bq_result(lg_id, home_team, guest_team, home_score, guest_score, all_goal, status,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,u_home_score,u_guest_score,source,match_score_t) values (%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_score = %s,match_time =%s,match_process =%s,home_score =%s,guest_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,score_home, score_guest, number, 1,'','',qiudui,'',utime,r_ctime,jijie,number,int(game_id),0,0,'hg3535',res,utime,qiudui,r_ctime,jijie,score_home,score_guest)) # 插入赛事表 Competition_sql = "insert into st_bq_competition(home_team,guest_team,lg_id,status,match_id,ctime,utime,tag,source,type,is_rollball) values(%s, %s, %s, %s, %s, %s, %s,%s, %s,%s,%s) on conflict(match_id) do update set tag=%s,is_rollball=%s,utime=%s;" self.cursor.execute(Competition_sql, (team_home, team_guest, league_id, 1,game_id,utime, utime, number,"hg3535",1,1,number,1,utime)) #让球 if concedes_dict: for key, value in concedes_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=1) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=1,odd=value) sql1 = "insert into st_bq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 1, "concede", 0, "hg3535", new_hash, concedes_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_bq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 1, "concede", 0, "hg3535", concedes_dict_rule[key],odds_only,1)) #插入结果表 res = json.dumps(match_score_dict) # r = pymysql.escape_string(res) sql3 = "insert into st_bq_result(lg_id, home_team, guest_team, home_score, guest_score, all_goal, status,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,u_home_score,u_guest_score,source,match_score_t) values (%s,%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_score_t = %s,match_time =%s,match_process =%s,home_score =%s,guest_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,score_home, score_guest, number, 1,'','','','',utime,r_ctime,jijie,number,int(game_id),0,0,'hg3535',res,utime,res,r_ctime,jijie,score_home,score_guest)) # 插入记录表 sql4 = "insert into st_bq_result_record(lg_id, home_team, guest_team, home_score, guest_score,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,source,status) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s);" self.cursor.execute(sql4, (int(league_id), team_home, team_guest,score_home, score_guest, '', '','','',utime,r_ctime,jijie,number,int(game_id),'hg3535',0)) if total_size_dict: for key, value in total_size_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=2) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=2,odd=value) sql1 = "insert into st_bq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 2, "total_size", 0, "hg3535", new_hash, total_size_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_bq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 2, "total_size", 0, "hg3535", total_size_dict_rule[key],odds_only,1)) #插入结果表 res = json.dumps(match_score_dict) # r = pymysql.escape_string(res) sql3 = "insert into st_bq_result(lg_id, home_team, guest_team, home_score, guest_score, all_goal, status,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,u_home_score,u_guest_score,source,match_score_t) values (%s,%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_score_t = %s,match_time =%s,match_process =%s,home_score =%s,guest_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,score_home, score_guest, number, 1,'','','','',utime,r_ctime,jijie,number,int(game_id),0,0,'hg3535',res,utime,res,r_ctime,jijie,score_home,score_guest)) # 插入记录表 sql4 = "insert into st_bq_result_record(lg_id, home_team, guest_team, home_score, guest_score,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,source,status) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s);" self.cursor.execute(sql4, (int(league_id), team_home, team_guest,score_home, score_guest, '', '','','',utime,r_ctime,jijie,number,int(game_id),'hg3535',1)) if odd_evens_dict: for key, value in odd_evens_dict.items(): if value: new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=3) odds_only = r_func(match_id=game_id, odds_code=key, sort=0, p_id=3,odd=value) sql1 = "insert into st_bq_odds(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, sole,condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s) on conflict(sole) do update set utime = %s,odds = %s,odds_only=%s;" self.cursor.execute(sql1, ( int(league_id), key, int(game_id), utime, utime,value, 3, "two_sides", 0, "hg3535", new_hash, odd_evens_dict_rule[key],odds_only,1,utime, value,odds_only)) # 更新主队st_zq_odds_record表 sql2 = "insert into st_bq_odds_record(lg_id, odds_code, match_id, ctime, utime, odds, p_id, p_code, sort, source, condition,odds_only,is_rollball) values (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s,%s);" self.cursor.execute(sql2, ( int(league_id), key, int(game_id), utime, utime, value, 3, "two_sides", 0, "hg3535", odd_evens_dict_rule[key],odds_only,1)) #插入结果表 res = json.dumps(match_score_dict) # r = pymysql.escape_string(res) sql3 = "insert into st_bq_result(lg_id, home_team, guest_team, home_score, guest_score, all_goal, status,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,u_home_score,u_guest_score,source,match_score_t) values (%s,%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s) on conflict(match_id) do update set update_time = %s,match_score_t = %s,match_time =%s,match_process =%s,home_score =%s,guest_score =%s;" self.cursor.execute(sql3, (int(league_id), team_home, team_guest,score_home, score_guest, number, 1,'','','','',utime,r_ctime,jijie,number,int(game_id),0,0,'hg3535',res,utime,res,r_ctime,jijie,score_home,score_guest)) # 插入记录表 sql4 = "insert into st_bq_result_record(lg_id, home_team, guest_team, home_score, guest_score,first_score, last_score,match_score,match_winer,update_time,match_time,match_process,tag,match_id,source,status) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s,%s,%s);" self.cursor.execute(sql4, (int(league_id), team_home, team_guest,score_home, score_guest, '', '','','',utime,r_ctime,jijie,number,int(game_id),'hg3535',1)) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 足球 赛事结束 结果插入-------------------------------------------------------------------------------------------------- class Zujieshuqiupipeline(object): def open_spider(self, spider): self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) ctime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 赛事id match_id = item['id_score'][0] # 全场比分 score = item['id_score'][1] status = 0 game_code = 'zq' try: sql = "insert into comendnotice(status, game_code, match_id, ctime) values (%s, %s, %s, %s) on conflict(match_id) do update set ctime = %s" self.cursor.execute(sql, (status, game_code, match_id, ctime, ctime)) self.cursor.execute("update st_zq_result set status=2 where match_id={}".format(match_id)) # self.cursor.execute("update st_zq_result_record set status=2 where match_id={}".format(match_id)) self.cursor.execute("update st_zq_competition set status=2 where match_id={}".format(match_id)) # url = 'http://sports.5gogo.com/Sports/WriteSports/setSports' # pass # data = { # "game_code": "zq", # "title": "match_result", # "data": { # "lg_id": 27686, # "home_team": " ", # "guest_team": " ", # "home_rate": " ", # "guest_rate": " ", # "home_score": " ", # "guest_score": " ", # "all_goal": " ", # "status": "2", # "first_score": " ", # "last_score": " ", # "match_score": " ", # "match_winer": " ", # "match_time": " ", # "match_process": " ", # "tag": "111", # "match_id": match_id, # "u_home_score": "111", # "u_guest_score": "11", # "update_time": ctime, # "p_code": "111", # "source": "hg3535", # } # } # data = json.dumps(data) # print(data) # r = requests.post(url, data=data) # print(r.content) except Exception as e: logger.warning('足球 赛事结束 结果插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 篮球 赛事结束 结果插入-------------------------------------------------------------------------------------------------- class Lanjieshuqiupipeline(object): def open_spider(self, spider): self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) ctime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 赛事id match_id = item['id_score'][0] # 全场比分 score = item['id_score'][1] status = 0 game_code = 'lq' try: sql = "insert into comendnotice(status, game_code, match_id, ctime) values (%s, %s, %s, %s) on conflict(match_id) do update set ctime = %s" self.cursor.execute(sql, (status, game_code, match_id, ctime, ctime)) self.cursor.execute("update st_lq_result set status=2 where match_id={}".format(match_id)) # self.cursor.execute("update st_zq_result_record set status=2 where match_id={}".format(match_id)) self.cursor.execute("update st_lq_competition set status=2 where match_id={}".format(match_id)) except Exception as e: logger.warning('篮球 赛事结束 结果插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 棒球 赛事结束 结果插入-------------------------------------------------------------------------------------------------- class Bangjieshuqiupipeline(object): def open_spider(self, spider): self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) ctime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 赛事id match_id = item['id_score'][0] # 全场比分 score = item['id_score'][1] status = 0 game_code = 'lq' try: sql = "insert into comendnotice(status, game_code, match_id, ctime) values (%s, %s, %s, %s) on conflict(match_id) do update set ctime = %s" self.cursor.execute(sql, (status, game_code, match_id, ctime, ctime)) self.cursor.execute("update st_bq_result set status=2 where match_id={}".format(match_id)) # self.cursor.execute("update st_zq_result_record set status=2 where match_id={}".format(match_id)) self.cursor.execute("update st_bq_competition set status=2 where match_id={}".format(match_id)) except Exception as e: logger.warning('棒球 赛事结束 结果插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close() # 网球 赛事结束 结果插入-------------------------------------------------------------------------------------------------- class Wangjieshuqiupipeline(object): def open_spider(self, spider): self.conn = psycopg2.connect(host=settings["POST_HOST"], port=settings['POST_PORT'], user=settings["POST_USER"], password=settings["POST_PASSWORD"], database=settings["POST_DATABASE"]) self.cursor = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) def process_item(self, item, spider): logger = logging.getLogger(__name__) ctime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 赛事id match_id = item['id_score'][0] # 全场比分 score = item['id_score'][1] status = 0 game_code = 'lq' try: sql = "insert into comendnotice(status, game_code, match_id, ctime) values (%s, %s, %s, %s) on conflict(match_id) do update set ctime = %s" self.cursor.execute(sql, (status, game_code, match_id, ctime, ctime)) self.cursor.execute("update st_wq_result set status=2 where match_id={}".format(match_id)) # self.cursor.execute("update st_zq_result_record set status=2 where match_id={}".format(match_id)) self.cursor.execute("update st_wq_competition set status=2 where match_id={}".format(match_id)) except Exception as e: logger.warning('网球 赛事结束 结果插入错误') logger.warning(e) return item def close_spider(self, spider): self.conn.commit() self.conn.close()