sports_scrapy/hg3535/pipelines.py

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, fuhao, one_post


# 足球 赔率 赛事---------------------------------------------------------------------------------------------------------
class Zuqiupipeline(object):
    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)


        if half_size_home:
            for index, value in enumerate(half_size_home):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='half_goal_size_big', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='half_goal_size_big', sort=index, p_id=p_id, odd=value)
                    odds_only = r_hash
                    f1 = fuhao(str(half_size_home_rule[index]))
                    payload_concede = {
                            "game_code": "bq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "half_goal_size_big",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": new_hash,
                                "sole": odds_only,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    # if redis_db.hexists("hg3535_zuqiu", r_hash):
                    #     pass
                    # else:
                    #     redis_db.hset("hg3535_zuqiu", r_hash, 0)
                    #     sql1 = "insert into st_zq_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,condition=%s;"
                    #     cursor.execute(sql1, (
                    #         int(data2['league_id']), data3, int(data2['game_id']), data2['utime'], data2['utime'],
                    #         float(value),
                    #         data2['p_id'], data2["p_code"], index, data2["source"], new_hash,
                    #         f1, data2['expire_time'], odds_only, data2['utime'], value, data2['expire_time'],
                    #         odds_only, f1))
                    #
                    #     # 更新主队st_zq_odds_record表
                    #     sql2 = "insert into st_zq_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);"
                    #     cursor.execute(sql2, (
                    #         int(data2['league_id']), data3, int(data2['game_id']), data2['utime'], data2['utime'],
                    #         float(value),
                    #         data2['p_id'], data2["p_code"], index, data2["source"], f1, odds_only))
        #     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)
        if half_size_guest:
            for index, value in enumerate(half_size_guest):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='half_goal_size_small', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='half_goal_size_small', sort=index, p_id=p_id, odd=value)
                    odds_only = r_hash
                    f1 = fuhao(str(half_size_guest_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "half_goal_size_small",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": new_hash,
                                "sole": odds_only,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        # 全场场大小
        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)
        if size_guest:
            for index, value in enumerate(size_guest):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='goal_size_small', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='goal_size_small', sort=index, p_id=p_id, odd=value)
                    odds_only = r_hash
                    f1 = fuhao(str(size_guest_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "goal_size_small",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        # 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)
        if size_home:
            for index, value in enumerate(size_home):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='goal_size_big', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='goal_size_big', sort=index, p_id=p_id, odd=value)
                    odds_only = r_hash
                    f1 = fuhao(str(size_home_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "goal_size_big",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": new_hash,
                                "sole": r_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        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)
        if half_concede_home:
            for index, value in enumerate(half_concede_home):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id, odd=value)
                    # odds_only = r_hash
                    f1 = fuhao(str(half_concede_home_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "half_concede_home",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": new_hash,
                                "sole": r_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        # 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)
        if half_concede_guest:
            for index, value in enumerate(half_concede_guest):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id, odd=value)
                    # odds_only = r_hash
                    f1 = fuhao(str(half_concede_guest_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "half_concede_guest",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        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)
        if half_concede_guest:
            for index, value in enumerate(half_concede_guest):
                if value:
                    new_hash = hash_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id)
                    r_hash = r_func(match_id=competition_id, odds_code='half_concede_home', sort=index, p_id=p_id, odd=value)
                    # odds_only = r_hash
                    f1 = fuhao(str(half_concede_guest_rule[index]))
                    payload_concede = {
                            "game_code": "zq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": "half_concede_guest",
                                "status": 0,
                                "sort": 0,
                                "p_code": p_code,
                                "odds": value,
                                "condition": f1,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        # 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)
        if half_capot_home:
            new_hash = hash_func(match_id=competition_id, odds_code='half_capot_home', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='half_capot_home', sort=0, p_id=p_id, odd=half_capot_home)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "half_capot_home",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": half_capot_home,
                    "condition": 1,
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
            # odds_only = r_hash
            # if redis_db.hexists("hg3535_zuqiu", r_hash):
            #     pass
            # else:
            #     redis_db.hset("hg3535_zuqiu", r_hash, 0)
            #     sql1 = "insert into st_zq_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,condition=%s;"
            #     cursor.execute(sql1, (
            #         int(data2['league_id']), data3, int(data2['game_id']), data2['utime'], data2['utime'], data1,
            #         data2['p_id'], data2["p_code"], data2['sort'], data2["source"], new_hash,
            #         data4, data2['expire_time'], odds_only, data2['utime'], data1, data2['expire_time'], odds_only,
            #         data4))
            #     # 更新主队st_zq_odds_record表
            #     sql2 = "insert into st_zq_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);"
            #     cursor.execute(sql2, (
            #         int(data2['league_id']), data3, int(data2['game_id']), data2['utime'], data2['utime'], data1,
            #         data2['p_id'], data2["p_code"], data2['sort'], data2["source"], data4, odds_only))
        # 上半场独赢 客队
        # 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)
        if half_capot_guest:
            new_hash = hash_func(match_id=competition_id, odds_code='half_capot_guest', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='half_capot_guest', sort=0, p_id=p_id, odd=half_capot_guest)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "half_capot_guest",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": half_capot_guest,
                    "condition": 2,
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
        # 上半场独赢 和
        # 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)
        #
        if half_capot_dogfall:
            new_hash = hash_func(match_id=competition_id, odds_code='half_capot_dogfall', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='half_capot_dogfall', sort=0, p_id=p_id, odd=half_capot_dogfall)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "half_capot_dogfall",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": half_capot_dogfall,
                    "condition": 'x',
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
        # 全场独赢 主队

        # 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)
        if capot_home:
            new_hash = hash_func(match_id=competition_id, odds_code='half_capot_dogfall', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='half_capot_dogfall', sort=0, p_id=p_id, odd=capot_home)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "capot_home",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": capot_home,
                    "condition": 1,
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
        # 全场独赢 客队
        # 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)
        if capot_guest:
            new_hash = hash_func(match_id=competition_id, odds_code='capot_guest', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='capot_guest', sort=0, p_id=p_id, odd=capot_guest)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "capot_guest",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": capot_guest,
                    "condition": 1,
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
        # 全场独赢 和
        # 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)
        if capot_dogfall:
            new_hash = hash_func(match_id=competition_id, odds_code='capot_dogfall', sort=0, p_id=p_id)
            r_hash = r_func(match_id=competition_id, odds_code='capot_dogfall', sort=0, p_id=p_id, odd=capot_dogfall)
            payload_concede = {
                "game_code": "zq",
                "title": "odds",
                "data": {
                    "match_id": competition_id,
                    "lg_id": league_id,
                    "odds_code": "capot_dogfall",
                    "status": 0,
                    "sort": 0,
                    "p_code": p_code,
                    "odds": capot_dogfall,
                    "condition": 1,
                    "odds_only": r_hash,
                    "sole": new_hash,
                    "source": "hg3535",
                    "type": 0,
                    "team": ""
                }
            }
        #
# 入球数单双-------------------------------------------------------------------------------------------------------------
        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)
        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='单')
        # 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)
        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='双')

        # 全场入球数 单双
        # 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)
        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='单')
        # 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)
        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='双')

# 总入球数 --------------------------------------------------------------------------------------------------------------
        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:
                s_hash = hash_func(match_id=competition_id, odds_code=key, sort=0, p_id=p_id)
                o_hash = r_func(match_id=competition_id, odds_code=key, sort=0, p_id=p_id, odd=value)
                payload_concede = {
                    "game_code": "zq",
                    "title": "odds",
                    "data": {
                        "match_id": competition_id,
                        "lg_id": league_id,
                        "odds_code": key,
                        "status": 0,
                        "sort": 0,
                        "p_code": p_code,
                        "odds": value,
                        "condition": 1,
                        "odds_only": o_hash,
                        "sole": s_hash,
                        "source": "hg3535",
                        "type": 0,
                        "team": ""
                    }
                }
                # 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, 'team_score')
        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 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}
        # 让球 数据插入数据库
        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)
                        r_hash = r_func(match_id=game_id, odds_code=key, sort=x, p_id=1, odd=y)
                        odds_only = r_hash
                        l_f = fuhao(str(concedes_dict_rule[key][x]))
                        payload_bodan = {
                            "game_code": "lq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "concede",
                                "odds": y,
                                "condition": l_f,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                            }
                        }
        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}
        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)
                        r_hash = r_func(match_id=game_id, odds_code=key, sort=x, p_id=2, odd=y)
                        odds_only = r_hash
                        l_f = fuhao(str(odd_evens_dict_rule[key][x]))
                        payload_bodan = {
                            "game_code": "lq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "two_sides",
                                "odds": y,
                                "condition": l_f,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                            }
                        }
        # 总分单双 数据插入数据库
        # 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}
        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)
                        r_hash = r_func(match_id=game_id, odds_code=key, sort=x, p_id=3, odd=y)
                        odds_only = r_hash
                        l_f = fuhao(str(total_sizes_dict_rule[key][x]))
                        payload_bodan = {
                            "game_code": "lq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "total_size",
                                "odds": y,
                                "condition": l_f,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                            }
                        }
        # 全场总分大小 数据插入数据库
        # 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}
        # 全场总分大小 数据插入数据库
        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)
                        r_hash = r_func(match_id=game_id, odds_code=key, sort=x, p_id=6, odd=y)
                        odds_only = r_hash
                        l_f = fuhao(str(team_scores_dict_rule[key][x]))
                        payload_bodan = {
                            "game_code": "lq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "team_score",
                                "odds": y,
                                "condition": l_f,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                            }
                        }
        # 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)
        if last_numbers_dict['last_home']:
            for key, value in last_numbers_dict['last_home'].items():
                if value:
                    new_hash = hash_func(match_id=game_id, odds_code="last_home" + key, sort=0, p_id=4)

                    r_hash = r_func(match_id=game_id, odds_code="last_home" + key, sort=0, p_id=4, odd=value)
                    # odds_only = r_hash
                    payload_bodan = {
                        "game_code": "lq",
                        "title": "odds",
                        "data": {
                            "match_id": game_id,
                            "lg_id": league_id,
                            "odds_code": "last_home" + key,
                            "status": 0,
                            "sort": 0,
                            "p_code": "team_score",
                            "odds": value,
                            "condition": key,
                            "odds_only": r_hash,
                            "sole": new_hash,
                            "source": "hg3535",
                            "type": 0,
                            "team": ""
                        }
                    }
        # 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)
        if last_numbers_dict['last_guest']:
            for key, value in last_numbers_dict['last_guest'].items():
                if value:
                    new_hash = hash_func(match_id=game_id, odds_code="last_home" + key, sort=0, p_id=4)

                    r_hash = r_func(match_id=game_id, odds_code="last_home" + key, sort=0, p_id=4, odd=value)
                    # odds_only = r_hash
                    payload_bodan = {
                        "game_code": "lq",
                        "title": "odds",
                        "data": {
                            "match_id": game_id,
                            "lg_id": league_id,
                            "odds_code": "last_home" + key,
                            "status": 0,
                            "sort": 0,
                            "p_code": "team_score",
                            "odds": value,
                            "condition": key,
                            "odds_only": r_hash,
                            "sole": new_hash,
                            "source": "hg3535",
                            "type": 0,
                            "team": ""
                        }
                    }
        # 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 == "篮球":
                # 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))
                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
                        }
                    }
                    # resp = requests.post(url, data={"data": json.dumps(payload)})
                    # print(json.dumps(payload))
                    # print(resp.text)
                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
                        }
                    }
                    # resp = requests.post(url, data={"data": json.dumps(payload)})
                    # print(json.dumps(payload))
                    # print(resp.text)
                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
                        }
                    }
        # 插入赛事表
    #     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 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():
                if value:
                    new_hash = hash_func(match_id=game_id, odds_code=key, sort=0, p_id=1)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=1, odd=value)
                    odds_only = r_hash
                    w_f = fuhao(str(concedes_dict_rule[key]))
                    payload_bodan = {
                            "game_code": "wq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "concede",
                                "odds": value,
                                "condition": 0,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
        # 让局
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=3, odd=value)
                    odds_only = r_hash
                    w_f = fuhao(str(bureaus_dict_rule[key]))
                    payload_bodan = {
                        "game_code": "wq",
                        "title": "odds",
                        "data": {
                            "match_id": game_id,
                            "lg_id": league_id,
                            "odds_code": key,
                            "status": 0,
                            "sort": 0,
                            "p_code": "bureau",
                            "odds": value,
                            "condition": w_f,
                            "odds_only": r_hash,
                            "sole": new_hash,
                            "source": "hg3535",
                            "type": 0,
                            "team": ""
                        }
                    }
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=4, odd=value)
                    odds_only = r_hash
                    w_f = fuhao(str(total_number_dict_rule[key]))
                    payload_bodan = {
                        "game_code": "wq",
                        "title": "odds",
                        "data": {
                            "match_id": game_id,
                            "lg_id": league_id,
                            "odds_code": key,
                            "status": 0,
                            "sort": 0,
                            "p_code": "total_number",
                            "odds": value,
                            "condition": w_f,
                            "odds_only": r_hash,
                            "sole": new_hash,
                            "source": "hg3535",
                            "type": 0,
                            "team": ""
                        }
                    }
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=5, odd=value)
                    odds_only = r_hash
                    w_f = fuhao(str(odd_evens_dict_rule[key]))
                    payload_bodan = {
                        "game_code": "wq",
                        "title": "odds",
                        "data": {
                            "match_id": game_id,
                            "lg_id": league_id,
                            "odds_code": key,
                            "status": 0,
                            "sort": 0,
                            "p_code": "two_game_sides",
                            "odds": value,
                            "condition": w_f,
                            "odds_only": r_hash,
                            "sole": new_hash,
                            "source": "hg3535",
                            "type": 0,
                            "team": ""
                        }
                    }
                    # if redis_db.hexists("hg3535_wangqiu", r_hash):
                    #     pass
                    # else:
                    #     redis_db.hset("hg3535_wangqiu", r_hash, 0)
                    #     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,condition=%s;"
                    #     cursor.execute(sql1, (
                    #         int(data2['league_id']), key, int(data2['game_id']), data2['utime'], data2['utime'],
                    #         value,
                    #         data2['p_id'], data2["p_code"], data2['sort'], data2["source"], new_hash,
                    #         w_f, data2['expire_time'], odds_only, data2['utime'], value, data2['expire_time'],
                    #         odds_only, w_f))
                    #     # 更新主队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);"
                    #     cursor.execute(sql2, (
                    #         int(data2['league_id']), key, int(data2['game_id']), data2['utime'], data2['utime'],
                    #         value,
                    #         data2['p_id'], data2["p_code"], data2['sort'], data2["source"], w_f, odds_only))
        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
                    payload_bodan = {
                            "game_code": "wq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "bodan",
                                "odds": value,
                                "condition": 0,
                                "odds_only": r_hash,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_bodan)})
                    print(json.dumps(payload_bodan))
                    print(resp.text, '网球独赢/冠军')
        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():
                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
                    payload_bodan = {
                            "game_code": "wq",
                            "title": "odds",
                            "data": {
                                "match_id": competition_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "bodan",
                                "odds": value,
                                "condition": bodan_dict[key],
                                "odds_only": odds_only,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_bodan)})
                    print(json.dumps(payload_bodan))
                    print(resp.text, '网球波胆')
        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)
        # 让球
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=1, odd=value)
                    odds_only = r_hash
                    b_f = fuhao(str(concedes_dict_rule[key]))
                    payload_concede = {
                            "game_code": "bq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "concede",
                                "odds": value,
                                "condition": b_f,
                                "odds_only": new_hash,
                                "sole": odds_only,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_concede)})
                    print(json.dumps(payload_concede))
                    print(resp.text, '棒球让球')
        # 总得分：大/小
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=2, odd=value)
                    odds_only = r_hash
                    b_f = fuhao(str(total_size_dict_rule[key]))
                    payload_total_size = {
                            "game_code": "bq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "total_size",
                                "odds": value,
                                "condition": b_f,
                                "odds_only": new_hash,
                                "sole": odds_only,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_total_size)})
                    print(json.dumps(payload_total_size))
                    print(resp.text, '-----total_size')
        # 单双
        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)
                    r_hash = r_func(match_id=game_id, odds_code=key, sort=0, p_id=3, odd=value)
                    odds_only = r_hash
                    b_f = fuhao(str(odd_evens_dict_rule[key]))
                    payload_odd_even = {
                            "game_code": "bq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "two_sides",
                                "odds": value,
                                "condition": b_f,
                                "odds_only": new_hash,
                                "sole": odds_only,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_odd_even)})
                    print(json.dumps(payload_odd_even))
                    print(resp.text, '-----odd_even')
        # 插入独赢
        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
                    payload_capots = {
                            "game_code": "bq",
                            "title": "odds",
                            "data": {
                                "match_id": game_id,
                                "lg_id": league_id,
                                "odds_code": key,
                                "status": 0,
                                "sort": 0,
                                "p_code": "capot",
                                "odds": value,
                                "condition": None,
                                "odds_only": odds_only,
                                "sole": new_hash,
                                "source": "hg3535",
                                "type": 0,
                                "team": ""
                                }
                            }
                    resp = requests.post(url, data={"data": json.dumps(payload_capots)})
                    print(json.dumps(payload_capots))
                    print(resp.text, '棒球独赢')
        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'
        if ball == "足球":
            n_gameid = int('1' + str(league_id))
            payload = {
                "game_code": "zq",
                "title": "competition",
                "data": {
                    "home_team": league_name,
                    "lg_id": league_id,
                    "status": 0,
                    "match_id": n_gameid,
                    "source": "hg3535",
                    "us_time": data_game
                    }
            }
            print(json.dumps(payload))
            resp = requests.post(url, data={"data": json.dumps(payload)})
            print(resp.text)
            return item
    #         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 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 ball == "足球":
            if area_name in area_dict:
                payload = {
                        "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 == "足球":
                    payload = {
                            "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:
                    payload = {
                            "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"
                            }}
            data = {"data": json.dumps(payload)}
            resp = requests.post(url, data=data)
            print(resp.text)
        elif ball == "篮球":
            if area_name in area_dict:
                payload = {
                    "game_code": "lq",
                    "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 == "篮球":
                    payload = {
                        "game_code": "lq",
                        "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:
                    payload = {
                        "game_code": "lq",
                        "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"
                        }}
            data = {"data": json.dumps(payload)}
            resp = requests.post(url, data=data)
            print(resp.text)
        elif ball == "网球":
            if area_name in area_dict:
                payload = {
                    "game_code": "wq",
                    "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 == "网球":
                    payload = {
                        "game_code": "wq",
                        "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:
                    payload = {
                        "game_code": "wq",
                        "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"
                        }}
            data = {"data": json.dumps(payload)}
            resp = requests.post(url, data=data)
            print(resp.text)
        elif ball == "棒球":
            if area_name in area_dict:
                payload = {
                    "game_code": "bq",
                    "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 == "棒球":
                    payload = {
                        "game_code": "bq",
                        "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:
                    payload = {
                        "game_code": "bq",
                        "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"
                        }}
            data = {"data": json.dumps(payload)}
            resp = requests.post(url, data=data)
            print(resp.text)
        return item

# ---------------------------------------------------------滚球---------------------------------------------------------
# 滚球足球 插入
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()