sports_scrapy/hg3535/pipelines.py

import json
import time
import logging
import requests
from .ball_func import get_pcode, new_time, out_time, hash_func, fuhao, one_post, \
    two_post, three_post, gs_post, go_post, guanl_post, liansai, new_times


# 足球 赔率 赛事---------------------------------------------------------------------------------------------------------
class Zuqiupipeline(object):
    def process_item(self, item, spider):
        # 使用twisted将mysql插入变成异步执行
        # print(item)
        zq_odds = []
        url = 'http://admin.5gogo.com/setSports'
        # zq_competitions = []
        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']
        two_post(data=half_size_home, match_id=competition_id, odds_code='half_goal_size_big', p_id=p_id, league_id=league_id, p_code=p_code, condition=half_size_home_rule, game_code='zq', zq_odds=zq_odds)
        # print(a)
        two_post(data=half_size_guest, match_id=competition_id, odds_code='half_goal_size_small', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=half_size_guest_rule, game_code='zq', zq_odds=zq_odds)
        # 全场场大小
        size_guest = item["size_guest"]
        size_guest_rule = item["size_guest_rule"]
        size_home = item["size_home"]
        size_home_rule = item["size_home_rule"]
        two_post(data=size_guest, match_id=competition_id, odds_code='goal_size_small', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=size_guest_rule, game_code='zq', zq_odds=zq_odds)
        two_post(data=size_home, match_id=competition_id, odds_code='goal_size_big', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=size_home_rule, game_code='zq', zq_odds=zq_odds)
        p_code, p_id = get_pcode(corner_ball, 'concede')
        # 上半场大小
        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"]
        # 上半场让球
        two_post(data=half_concede_home, match_id=competition_id, odds_code='half_concede_home', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=half_concede_home_rule, game_code='zq', zq_odds=zq_odds)
        two_post(data=half_concede_guest, match_id=competition_id, odds_code='half_concede_guest', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=half_concede_guest_rule, game_code='zq', zq_odds=zq_odds)
        concede_guest = item["concede_guest"]
        concede_guest_rule = item["concede_guest_rule"]
        concede_home = item["concede_home"]
        concede_home_rule = item["concede_home_rule"]
        # 全场让球
        two_post(data=concede_guest, match_id=competition_id, odds_code='concede_guest', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=concede_guest_rule, game_code='zq', zq_odds=zq_odds)
        two_post(data=concede_home, match_id=competition_id, odds_code='concede_home', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=concede_home_rule, game_code='zq', zq_odds=zq_odds)

# 独赢-------------------------------------------------------------------------------------------------------------------
        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"]
        # 上半场独赢 主队
        one_post(data=half_capot_home, match_id=competition_id, odds_code='half_capot_home', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=1, game_code='zq', zq_odds=zq_odds)
        # 上半场独赢 客队
        one_post(data=half_capot_guest, match_id=competition_id, odds_code='half_capot_guest', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=2, game_code='zq', zq_odds=zq_odds)
        # 上半场独赢 和
        one_post(data=half_capot_dogfall, match_id=competition_id, odds_code='half_capot_dogfall', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition='X', game_code='zq', zq_odds=zq_odds)
        # 全场独赢 主队
        one_post(data=capot_home, match_id=competition_id, odds_code='capot_home', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=1, game_code='zq', zq_odds=zq_odds)
        # 全场独赢 客队
        one_post(data=capot_guest, match_id=competition_id, odds_code='capot_guest', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition=2, game_code='zq', zq_odds=zq_odds)
        # 全场独赢 和
        one_post(data=capot_dogfall, match_id=competition_id, odds_code='capot_dogfall', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition='X', game_code='zq', zq_odds=zq_odds)
# 入球数单双-------------------------------------------------------------------------------------------------------------
        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"]
        # 全场入球数 单双

        # 上半场入球数 单双
        one_post(data=odd_even_odd, match_id=competition_id, odds_code='two_sides_single', p_id=p_id, league_id=league_id, p_code=p_code, condition='单', game_code='zq', zq_odds=zq_odds)
        one_post(data=odd_even_even, match_id=competition_id, odds_code='two_sides_double', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition='双', game_code='zq', zq_odds=zq_odds)

        # 全场入球数 单双
        one_post(data=half_odd_even_odd, match_id=competition_id, odds_code='half_two_sides_single', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition='单', game_code='zq', zq_odds=zq_odds)
        one_post(data=half_odd_even_even, match_id=competition_id, odds_code='half_two_sides_double', p_id=p_id,
                 league_id=league_id, p_code=p_code, condition='双', game_code='zq', zq_odds=zq_odds)

# 总入球数 --------------------------------------------------------------------------------------------------------------
        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或以上'}
        # # 全场入球数 单双

        # 上半场入球数 单双
        for key, value in total_goals.items():
            one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                     league_id=league_id, p_code=p_code, condition=total_dict[key], game_code='zq', zq_odds=zq_odds)


# 全场半场 --------------------------------------------------------------------------------------------------------------
        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": "客客"}
        if half_fulls:
            for key, value in half_fulls.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=full_dict[key], game_code='zq', zq_odds=zq_odds)

# 波胆------------------------------------------------------------------------------------------------------------------
        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": "其他"}
        if bodan_datas:
            for key, value in bodan_datas.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=bodan_dict[key], game_code='zq', zq_odds=zq_odds)
# 最先进球/最后进球 ------------------------------------------------------------------------------------------------------
        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": "没有进球"}
        if first_last_balls:
            for key, value in first_last_balls.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=first_last_dict[key], game_code='zq', zq_odds=zq_odds)
        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']
        if full_dicts:
            for key, value in full_dicts.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=full_dict_rules[key], game_code='zq', zq_odds=zq_odds)
        if half_dicts:
            for key, value in half_dicts.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=half_dict_rules[key], game_code='zq', zq_odds=zq_odds)
        match_date, match_time, time3 = new_time(ctime)
        n_time = out_time(time3, 1.5)
        # 插入st_zq_competition表
        if zuqiu == "足球":
            if pt == 1:
                payload = {
                    "game_code": "zq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": competition_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 1,
                        "is_morningplate": 0,
                        "is_stringscene": 0,
                        "us_time": ctime
                    }
                }
            if pt == 2:
                payload = {
                    "game_code": "zq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": competition_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 1,
                        "is_stringscene": 0,
                        "us_time": ctime
                    }
                }
            if pt == 3:
                payload = {
                    "game_code": "zq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": competition_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 0,
                        "is_stringscene": 1,
                        "us_time": ctime
                    }
                }
            zq_odds.append(payload)
        # b = json.dumps(zq_odds)
        # print(json.dumps(zq_odds))
        r = requests.post(url, data={"data": json.dumps(zq_odds)})
        print(r.text)
        # print(len(zq_odds))
        # print(zq_odds)
        # print(zq_competitions)
        return item


# 篮球 让球大小----------------------------------------------------------------------------------------------------------
class Lanqiupipeline(object):
    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']
        # 让球 数据插入数据库
        if concedes_dict:
            for key, value in concedes_dict.items():
                two_post(data=value, match_id=game_id, odds_code=key, p_id=1,
                         league_id=league_id, p_code='concede', condition=concedes_dict_rule[key], game_code='lq')
        if odd_evens_dict:
            for key, value in odd_evens_dict.items():
                two_post(data=value, match_id=game_id, odds_code=key, p_id=2,
                         league_id=league_id, p_code='two_sides', condition=odd_evens_dict_rule[key], game_code='lq')
        # 总分单双 数据插入数据库
        if total_sizes_dict:
            for key, value in total_sizes_dict.items():
                two_post(data=value, match_id=game_id, odds_code=key, p_id=3,
                         league_id=league_id, p_code='total_size', condition=total_sizes_dict_rule[key], game_code='lq')
        # 全场总分大小 数据插入数据库
        # 全场总分大小 数据插入数据库
        if team_scores_dict:
            for key, value in team_scores_dict.items():
                two_post(data=value, match_id=game_id, odds_code=key, p_id=6,
                         league_id=league_id, p_code='total_size', condition=team_scores_dict_rule[key], game_code='lq')
        if last_numbers_dict['last_home']:
            for key, value in last_numbers_dict['last_home'].items():
                one_post(data=value, match_id=game_id, odds_code="last_home" + key, p_id=4,
                         league_id=league_id, p_code='team_score', condition=key, game_code='lq')
        if last_numbers_dict['last_guest']:
            for key, value in last_numbers_dict['last_guest'].items():
                one_post(data=value, match_id=game_id, odds_code="last_guest" + key, p_id=4,
                         league_id=league_id, p_code='team_score', condition=key, game_code='lq')
        match_date, match_time, time3 = new_time(ctime)
        n_time = out_time(time3, 1.5)
        us_time = ctime
        if lanqiu == "篮球":
                if pt == 1:
                    payload = {
                        "game_code": "lq",
                        "title": "competition",
                        "data": {
                            "home_team": team_home,
                            "guest_team": team_guest,
                            "lg_id": league_id,
                            "status": 0,
                            "match_id": game_id,
                            "match_date": match_date,
                            "match_time": match_time,
                            "tag": number,
                            "source": "hg3535",
                            "is_rollball": 0,
                            "is_today": 1,
                            "is_morningplate": 0,
                            "is_stringscene": 0,
                            "us_time": us_time
                        }
                    }
                if pt == 2:
                    payload = {
                        "game_code": "lq",
                        "title": "competition",
                        "data": {
                            "home_team": team_home,
                            "guest_team": team_guest,
                            "lg_id": league_id,
                            "status": 0,
                            "match_id": game_id,
                            "match_date": match_date,
                            "match_time": match_time,
                            "tag": number,
                            "source": "hg3535",
                            "is_rollball": 0,
                            "is_today": 0,
                            "is_morningplate": 1,
                            "is_stringscene": 0,
                            "us_time": us_time
                        }
                    }
                if pt == 3:
                    payload = {
                        "game_code": "lq",
                        "title": "competition",
                        "data": {
                            "home_team": team_home,
                            "guest_team": team_guest,
                            "lg_id": league_id,
                            "status": 0,
                            "match_id": game_id,
                            "match_date": match_date,
                            "match_time": match_time,
                            "tag": number,
                            "source": "hg3535",
                            "is_rollball": 0,
                            "is_today": 0,
                            "is_morningplate": 0,
                            "is_stringscene": 1,
                            "us_time": us_time
                        }
                    }


# 网球 让球&大小盘--------------------------------------------------------------------------------------------------------
class Wangqiupipeline(object):
    def process_item(self, item, spider):
        logger = logging.getLogger(__name__)
        url = 'http://sports.5gogo.com/Sports/WriteSports/setSports'
        # 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']
        print(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']

        us_time = ctime
        match_date, match_time, time3 = new_time(ctime)
        n_time = out_time(time3, 3)
        # 插入赛事表
        if wangqiu == "网球":
            if pt == 1:
                payload = {
                    "game_code": "wq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 1,
                        "is_morningplate": 0,
                        "is_stringscene": 0,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
            if pt == 2:
                payload = {
                    "game_code": "wq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 1,
                        "is_stringscene": 0,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
            if pt == 3:
                payload = {
                    "game_code": "wq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 0,
                        "is_stringscene": 1,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
        # 让盘
        if concedes_dict:
            for key, value in concedes_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=1,
                         league_id=league_id, p_code='concede', condition=fuhao(str(concedes_dict_rule[key])), game_code='wq')
        # 让局
        if bureaus_dict:
            for key, value in bureaus_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=3,
                         league_id=league_id, p_code='bureau', condition=fuhao(str(bureaus_dict_rule[key])), game_code='wq')
        if total_number_dict:
            for key, value in total_number_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=4, league_id=league_id, p_code='total_number', condition=key, game_code='wq')
        if odd_evens_dict:
            for key, value in odd_evens_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=5, league_id=league_id,
                         p_code='two_game_sides', condition=key, game_code='wq')
        if kemps_dict:
            for key, value in kemps_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=2, league_id=league_id, p_code='kemp', condition=key, game_code='wq')
        return item


# 网球 波胆--------------------------------------------------------------------------------------------------------------
class Wqbodanpipeline(object):
    def process_item(self, item, spider):
        # 使用twisted将mysql插入变成异步执行
        url = 'http://sports.5gogo.com/Sports/WriteSports/setSports'
        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():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=2, league_id=league_id, p_code='bodan',
                         condition=bodan_dict[key], game_code='wq')
        return item


# 棒球 让球&大小盘--------------------------------------------------------------------------------------------------------
class Bangqiupipeline(object):
    def process_item(self, item, spider):
        # print(item)
        logger = logging.getLogger(__name__)
        url = 'http://sports.5gogo.com/Sports/WriteSports/setSports'
        # 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('data_game字段分割错误')
            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']
        match_date, match_time, time3 = new_time(ctime)
        us_time = ctime
        # 插入赛事表
        if bangqiu == '棒球':
            if pt == 1:
                payload = {
                    "game_code": "bq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 1,
                        "is_morningplate": 0,
                        "is_stringscene": 0,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
            if pt == 2:
                payload = {
                    "game_code": "bq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 1,
                        "is_stringscene": 0,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
            if pt == 3:
                payload = {
                    "game_code": "bq",
                    "title": "competition",
                    "data": {
                        "home_team": team_home,
                        "guest_team": team_guest,
                        "lg_id": league_id,
                        "status": 0,
                        "match_id": game_id,
                        "match_date": match_date,
                        "match_time": match_time,
                        "tag": number,
                        "source": "hg3535",
                        "is_rollball": 0,
                        "is_today": 0,
                        "is_morningplate": 0,
                        "is_stringscene": 1,
                        "us_time": us_time
                    }
                }
                resp = requests.post(url, data={"data": json.dumps(payload)})
                print(json.dumps(payload))
                print(resp.text)
        # 让球
        three_post(data=concedes_dict, match_id=game_id, p_id=1, league_id=league_id, p_code='concede',condition=concedes_dict_rule, game_code='bq')
        # 总得分：大/小
        three_post(data=total_size_dict, match_id=game_id, p_id=2, league_id=league_id, p_code='total_size',
                   condition=total_size_dict_rule, game_code='bq')
        # 单双
        three_post(data=odd_evens_dict, match_id=game_id, p_id=3, league_id=league_id, p_code='two_sides',
                   condition=odd_evens_dict_rule, game_code='bq')
        # 插入独赢
        if capots_dict:
            for key, value in capots_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=4, league_id=league_id, p_code='capot', condition=None, game_code='bq')
        return item

# 足球 篮球 网球 棒球 冠军------------------------------------------------------------------------------------------------
class Guanjunpipeline(object):
    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)
        # url = 'http://sports.5gogo.com/Sports/WriteSports/setSports'
        # 冠军赛事
        gs_post(ball=ball, league_id=league_id, league_name=league_name, data_game=data_game)
        # 冠军赔率
        go_post(ball=ball, league_id=league_id, new_champion=new_champion, new_league_name=new_league_name, value=champion_team, r_hash=r_hash, new_hash=new_hash, tema_home=tema_home)
        # 冠军联赛
        guanl_post(ball=ball, league_name=league_name, league_id=league_id, time3=time3)


# 足球 篮球 网球 棒球 联赛------------------------------------------------------------------------------------------------
class Liansaipipeline(object):
    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}
        if ball == "足球":
            liansai(area_name=area_name, name_chinese=name_chinese, st_league=st_league, area_dict=area_dict, game_code='zq', qiu='足球')
        elif ball == "篮球":
            liansai(area_name=area_name, name_chinese=name_chinese, st_league=st_league, area_dict=area_dict, game_code='lq', qiu='篮球')
        elif ball == "网球":
            liansai(area_name=area_name, name_chinese=name_chinese, st_league=st_league, area_dict=area_dict, game_code='wq', qiu='网球')
        elif ball == "棒球":
            liansai(area_name=area_name, name_chinese=name_chinese, st_league=st_league, area_dict=area_dict, game_code='bq', qiu='棒球')
        return item


# ---------------------------------------------------------滚球---------------------------------------------------------
# 滚球足球 插入
class Roll_Zuqiupipeline(object):
    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, '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"]
        try:
            # 让球 数据插入数据库
            two_post(data=half_size_home, match_id=competition_id, odds_code='half_goal_size_big', p_id=p_id, league_id=league_id, p_code=p_code, condition=half_size_home_rule, game_code='zq')
            # 让球 数据插入数据库
            two_post(data=half_size_guest, match_id=competition_id, odds_code='half_goal_size_small', p_id=p_id, league_id=league_id, p_code=p_code, condition=half_size_guest_rule, game_code='zq')

            # 全场场大小
            size_guest = item["size_guest"]
            size_guest_rule = item["size_guest_rule"]
            size_home = item["size_home"]
            size_home_rule = item["size_home_rule"]
            two_post(data=size_guest, match_id=competition_id, odds_code='goal_size_small', p_id=p_id, league_id=league_id, p_code=p_code, condition=size_guest_rule, game_code='zq')
            two_post(data=size_home, match_id=competition_id, odds_code='goal_size_big', p_id=p_id, league_id=league_id, p_code=p_code, condition=size_home_rule, game_code='zq')
            p_code, p_id = get_pcode(corner_ball, 'concede')
            # 上半场让球
            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"]
            # 上半场让球
            two_post(data=half_concede_home, match_id=competition_id, odds_code='half_concede_home', p_id=p_id, league_id=league_id, p_code=p_code, condition=half_concede_home_rule, game_code='zq')
            two_post(data=half_concede_guest, match_id=competition_id, odds_code='half_concede_guest', p_id=p_id, league_id=league_id, p_code=p_code, condition=half_concede_guest_rule, game_code='zq')
            concede_guest = item["concede_guest"]
            concede_guest_rule = item["concede_guest_rule"]
            concede_home = item["concede_home"]
            concede_home_rule = item["concede_home_rule"]
            # 全场让球
            two_post(data=concede_guest, match_id=competition_id, odds_code='concede_guest', p_id=p_id, league_id=league_id, p_code=p_code, condition=concede_guest_rule, game_code='zq')
            two_post(data=concede_home, match_id=competition_id, odds_code='concede_home', p_id=p_id, league_id=league_id, p_code=p_code, condition=concede_home_rule, game_code='zq')

            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"]
            # 上半场独赢 主队
            one_post(data=half_capot_home, match_id=competition_id, odds_code='half_capot_home', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition=1, game_code='zq')
            # 上半场独赢 客队
            one_post(data=half_capot_guest, match_id=competition_id, odds_code='half_capot_guest', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition=2, game_code='zq')
            # 上半场独赢 和
            one_post(data=half_capot_dogfall, match_id=competition_id, odds_code='half_capot_dogfall', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='X', game_code='zq')
            # 全场独赢 主队
            one_post(data=capot_home, match_id=competition_id, odds_code='capot_home', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition=1, game_code='zq')
            # 全场独赢 客队
            one_post(data=capot_guest, match_id=competition_id, odds_code='capot_guest', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition=2, game_code='zq')
            # 全场独赢 和
            one_post(data=capot_dogfall, match_id=competition_id, odds_code='capot_dogfall', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='X', game_code='zq')

# 入球数单双-------------------------------------------------------------------------------------------------------------
            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"]
            # 全场入球数 单双
            # 上半场入球数 单双
            one_post(data=odd_even_odd, match_id=competition_id, odds_code='two_sides_single', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='单', game_code='zq')
            one_post(data=odd_even_even, match_id=competition_id, odds_code='two_sides_double', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='双', game_code='zq')
            # 全场入球数 单双
            one_post(data=half_odd_even_odd, match_id=competition_id, odds_code='half_two_sides_single', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='双', game_code='zq')
            one_post(data=half_odd_even_even, match_id=competition_id, odds_code='half_two_sides_double', p_id=p_id,
                     league_id=league_id, p_code=p_code, condition='双', game_code='zq')
# 总入球数 --------------------------------------------------------------------------------------------------------------
            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或以上'}
            for key, value in total_goals.items():
                one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id,
                         league_id=league_id, p_code=p_code, condition=total_dict[key], game_code='zq')

# 全场半场 --------------------------------------------------------------------------------------------------------------
            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": "客客"}
            if half_fulls:
                for key, value in half_fulls.items():
                    # if value:
                    one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id, league_id=league_id, p_code=p_code, condition=full_dict[key], game_code='zq')
# 波胆------------------------------------------------------------------------------------------------------------------
            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": "其他"}
            if bodan_datas:
                for key, value in bodan_datas.items():
                    one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id, league_id=league_id, p_code=p_code, condition=bodan_dict[key], game_code='zq')

# 最先进球/最后进球 ------------------------------------------------------------------------------------------------------
            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": "没有进球"}
            if first_last_balls:
                for key, value in first_last_balls.items():
                    # 构建唯一索引
                    one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id, league_id=league_id, p_code=p_code, condition=first_last_dict[key], game_code='zq')

            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']
            if full_dicts:
                for key, value in full_dicts.items():
                    one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id, league_id=league_id, p_code=p_code, condition=full_dict_rules[key], game_code='zq')
            if half_dicts:
                for key, value in half_dicts.items():
                    one_post(data=value, match_id=competition_id, odds_code=key, p_id=p_id, league_id=league_id, p_code=p_code, condition=half_dict_rules[key], game_code='zq')
            zq_competition = {
                "game_code": "zq",
                "title": "competition",
                "data": {
                    "home_team": team_home,
                    "guest_team": team_guest,
                    "lg_id": league_id,
                    "status": 1,
                    "match_id": competition_id,
                    "match_date": r_ctime,
                    "match_time": None,
                    "tag": number,
                    "source": "hg3535",
                    "is_rollball": 1,
                    "is_today": 0,
                    "is_morningplate": 0,
                    "is_stringscene": 0,
                    "us_time": None
                }
            }
            zq_match_result = {
                "game_code": "zq",
                "title": "match_result",
                "data": {
                    "home_team": team_home,
                    "guest_team": team_home,
                    "lg_id": league_id,
                    "home_rate": None,
                    "guest_rate": None,
                    "home_score": score_home,
                    "guest_score": score_guest,
                    "all_goal": None,
                    "status": 1,
                    "first_score": None,
                    "last_score": None,
                    "match_score": match_score,
                    "match_winer": None,
                    "match_time": time_game,
                    "match_process": half_way,
                    "tag": number,
                    "match_id": competition_id,
                    "u_home_score": None,
                    "u_guest_score": None,
                    "p_code": p_code,
                    "source": "hg3535"
                }
            }
        except Exception as e:
            logger.warning(e)
        return item


# 滚球篮球 插入
class Roll_Lanqiupipeline(object):
    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():
                    two_post(data=value, match_id=game_id, odds_code=key, p_id=1, league_id=league_id, p_code='concede', condition=concedes_dict_rule[key], game_code='lq')

            if odd_evens_dict:
                for key, value in odd_evens_dict.items():
                    two_post(data=value, match_id=game_id, odds_code=key, p_id=2, league_id=league_id, p_code='two_sides',
                             condition=odd_evens_dict_rule[key], game_code='lq')

            if total_sizes_dict:
                for key, value in total_sizes_dict.items():
                    two_post(data=value, match_id=game_id, odds_code=key, p_id=3, league_id=league_id, p_code='total_size',
                             condition=total_sizes_dict_rule[key], game_code='lq')

            last_home = last_numbers_dict['last_home']
            if last_home:
                for key, value in last_home.items():
                    one_post(data=value, match_id=game_id, odds_code="last_home" + key, p_id=4, league_id=league_id, p_code='last_home', condition=key, game_code='lq')

            last_guest = last_numbers_dict['last_guest']
            if last_guest:
                for key, value in last_guest.items():
                    one_post(data=value, match_id=game_id, odds_code="last_guest" + key, p_id=4, league_id=league_id,
                             p_code='last_guest', condition=key, game_code='lq')

            if capots_dict:
                for key, value in capots_dict.items():
                    one_post(data=value, match_id=game_id, odds_code=key, p_id=5, league_id=league_id,
                             p_code='capot', condition=None, game_code='lq')

            if team_scores_dict:
                for key, value in team_scores_dict.items():
                    one_post(data=value, match_id=game_id, odds_code=key, p_id=6, league_id=league_id,
                             p_code='team_score', condition=team_scores_dict_rule[key], game_code='lq')
            lq_competition = {
                "game_code": "lq",
                "title": "competition",
                "data": {
                    "home_team": team_home,
                    "guest_team": team_guest,
                    "lg_id": league_id,
                    "status": 1,
                    "match_id": game_id,
                    "match_date": r_ctime,
                    "match_time": None,
                    "tag": number,
                    "source": 'hg3535',
                    "is_rollball": 1,
                    "is_today": 0,
                    "is_morningplate": 0,
                    "is_stringscene": 0,
                    "us_time": None
                }
            }
            lq_match_result = {
                "game_code": "lq",
                "title": "match_result",
                "data": {
                    "home_team": team_home,
                    "guest_team": team_home,
                    "lg_id": league_id,
                    "home_rate": None,
                    "guest_rate": None,
                    "home_score": score_home,
                    "guest_score": score_guest,
                    "all_goal": None,
                    "status": 1,
                    "first_score": None,
                    "last_score": None,
                    "match_score": match_score,
                    "match_winer": None,
                    "match_time": time_game,
                    "match_process": jijie,
                    "tag": number,
                    "match_id": game_id,
                    "u_home_score": None,
                    "u_guest_score": None,
                    "p_code": None,
                    "source": "hg3535"
                }
            }
        except:
            pass
        return item


# 滚球网球 插入
class Roll_Wangqiupipeline(object):
    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():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=6, league_id=league_id,
                         p_code='dishes', condition=concedes_dict_rule[key], game_code='wq')
        if kemps_dict:
            for key, value in kemps_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=2, league_id=league_id,
                         p_code='kemp', condition=None, game_code='wq')
        if bureaus_dict:
            for key, value in bureaus_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=3, league_id=league_id,
                         p_code='bureau', condition=None, game_code='wq')

        if total_number_dict:
            for key, value in total_number_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=3, league_id=league_id,
                         p_code='total_number', condition=total_number_dict_rule[key], game_code='wq')
        if odd_evens_dict:
            for key, value in odd_evens_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=5, league_id=league_id,
                         p_code='two_sides', condition=odd_evens_dict_rule[key], game_code='wq')
        wq_competition = {
            "game_code": "wq",
            "title": "competition",
            "data": {
                "home_team": team_home,
                "guest_team": team_guest,
                "lg_id": league_id,
                "status": 1,
                "match_id": game_id,
                "match_date": r_ctime,
                "match_time": None,
                "tag": number,
                "source": 'hg3535',
                "is_rollball": 1,
                "is_today": 0,
                "is_morningplate": 0,
                "is_stringscene": 0,
                "us_time": None
            }
        }
        wq_match_result = {
            "game_code": "wq",
            "title": "match_result",
            "data": {
                "home_team": team_home,
                "guest_team": team_home,
                "lg_id": league_id,
                "home_rate": None,
                "guest_rate": None,
                "home_score": score_home,
                "guest_score": score_guest,
                "all_goal": None,
                "status": 1,
                "first_score": None,
                "last_score": None,
                "match_score": None,
                "match_winer": None,
                "match_time": time_game,
                "match_process": jijie,
                "tag": number,
                "match_id": game_id,
                "u_home_score": None,
                "u_guest_score": None,
                "p_code": None,
                "source": "hg3535"
            }
        }
        return item
# 滚球棒球 插入
class Roll_Banqiupipeline(object):
    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}

        # 赛事失效时间
        # n_time = out_time(ctime, 3.5)
        # 插入独赢
        if capots_dict:
            for key, value in capots_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=2, league_id=league_id,
                         p_code='capot', condition=None, game_code='bq')
        #让球
        if concedes_dict:
            for key, value in concedes_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=1, league_id=league_id,
                         p_code='concede', condition=concedes_dict_rule[key], game_code='bq')

        if total_size_dict:
            for key, value in total_size_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=1, league_id=league_id,
                         p_code='total_size', condition=total_size_dict_rule[key], game_code='bq')
        if odd_evens_dict:
            for key, value in odd_evens_dict.items():
                one_post(data=value, match_id=game_id, odds_code=key, p_id=1, league_id=league_id,
                         p_code='two_sides', condition=odd_evens_dict_rule[key], game_code='bq')
        bq_competition = {
            "game_code": "bq",
            "title": "competition",
            "data": {
                "home_team": team_home,
                "guest_team": team_guest,
                "lg_id": league_id,
                "status": 1,
                "match_id": game_id,
                "match_date": None,
                "match_time": None,
                "tag": number,
                "source": 'hg3535',
                "is_rollball": 1,
                "is_today": 0,
                "is_morningplate": 0,
                "is_stringscene": 0,
                "us_time": None
            }
        }
        bq_match_result = {
            "game_code": "bq",
            "title": "match_result",
            "data": {
                "home_team": team_home,
                "guest_team": team_home,
                "lg_id": league_id,
                "home_rate": None,
                "guest_rate": None,
                "home_score": score_home,
                "guest_score": score_guest,
                "all_goal": None,
                "status": 1,
                "first_score": None,
                "last_score": None,
                "match_score": None,
                "match_winer": None,
                "match_time": r_ctime,
                "match_process": jijie,
                "tag": number,
                "match_id": game_id,
                "u_home_score": None,
                "u_guest_score": None,
                "p_code": None,
                "source": "hg3535",
                'match_score_t': match_score_dict
            }
        }
        return item


# 足球 赛事结束 结果插入--------------------------------------------------------------------------------------------------
# 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))
#         except Exception as e:
#             logger.warning('足球 赛事结束 结果插入错误')
#             logger.warning(e)
#         return item

# # 篮球 赛事结束 结果插入--------------------------------------------------------------------------------------------------
# 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()