Beispiel #1
0
class CStock(TickerHandlerBase):
    def __init__(self, dbinfo, code):
        self.code = code
        self.dbname = self.get_dbname(code)
        self.redis = create_redis_obj()
        self.name = self.get('name')
        self.data_type_dict = {9: "day"}
        self.influx_client = CInflux(ct.IN_DB_INFO, self.dbname)
        self.mysql_client = CMySQL(dbinfo, self.dbname)
        if not self.create():
            raise Exception("create stock %s table failed" % self.code)

    @staticmethod
    def get_dbname(code):
        return "s%s" % code

    @staticmethod
    def get_redis_name(code):
        return "realtime_%s" % code

    def on_recv_rsp(self, rsp_pb):
        '''获取逐笔 get_rt_ticker 和 TickerHandlerBase'''
        ret, data = super(CStock, self).on_recv_rsp(rsp_pb)
        return ret, data

    def has_on_market(self, cdate):
        time2Market = self.get('timeToMarket')
        if str(time2Market) == '0': return False
        t = time.strptime(str(time2Market), "%Y%m%d")
        y, m, d = t[0:3]
        time2Market = datetime(y, m, d)

        t = time.strptime(cdate, "%Y-%m-%d")
        y, m, d = t[0:3]
        time4Date = datetime(y, m, d)
        return True if (time4Date - time2Market).days > 0 else False

    def is_subnew(self, time2Market=None, timeLimit=365):
        if time2Market == '0': return False  #for stock has not been in market
        if time2Market == None: time2Market = self.get('timeToMarket')
        t = time.strptime(time2Market, "%Y%m%d")
        y, m, d = t[0:3]
        time2Market = datetime(y, m, d)
        return True if (datetime.today() -
                        time2Market).days < timeLimit else False

    def create(self):
        self.create_influx_db()
        return self.create_mysql_table()

    def create_influx_db(self):
        self.influx_client.create()

    def create_mysql_table(self):
        for _, table_name in self.data_type_dict.items():
            if table_name not in self.mysql_client.get_all_tables():
                sql = 'create table if not exists %s(cdate varchar(10) not null, open float, high float, close float, low float, volume float, amount float, PRIMARY KEY(cdate))' % table_name
                if not self.mysql_client.create(sql, table_name): return False
        return True

    def create_ticket_table(self, table):
        sql = 'create table if not exists %s(date varchar(10) not null, ctime varchar(8) not null, price float(5,2), cchange varchar(10) not null, volume int not null, amount int not null, ctype varchar(9) not null, PRIMARY KEY (date, ctime, cchange, volume, amount, ctype))' % table
        return True if table in self.mysql_client.get_all_tables(
        ) else self.mysql_client.create(sql, table)

    def get(self, attribute):
        df_byte = self.redis.get(ct.STOCK_INFO)
        if df_byte is None: return None
        df = _pickle.loads(df_byte)
        if len(df.loc[df.code == self.code][attribute].values) == 0:
            return None
        return df.loc[df.code == self.code][attribute].values[0]

    def run(self, data):
        self.redis.set(self.get_redis_name(self.code),
                       _pickle.dumps(data.tail(1), 2))
        self.influx_client.set(data)

    def merge_ticket(self, df):
        ex = df[df.duplicated(
            subset=['ctime', 'cchange', 'volume', 'amount', 'ctype'],
            keep=False)]
        dlist = list(ex.index)
        while len(dlist) > 0:
            snum = 1
            sindex = dlist[0]
            for _index in range(1, len(dlist)):
                if sindex + 1 == dlist[_index]:
                    snum += 1
                    if _index == len(dlist) - 1:
                        df.drop_duplicates(keep='first', inplace=True)
                        df.at[sindex,
                              'volume'] = snum * df.loc[sindex]['volume']
                        df.at[sindex,
                              'amount'] = snum * df.loc[sindex]['amount']
                else:
                    df.drop_duplicates(keep='first', inplace=True)
                    df.at[sindex, 'volume'] = snum * df.loc[sindex]['volume']
                    df.at[sindex, 'amount'] = snum * df.loc[sindex]['amount']
                    sindex = dlist[_index]
                    snum = 1
            df = df.reset_index(drop=True)
            ex = df[df.duplicated(
                subset=['ctime', 'cchange', 'volume', 'amount', 'ctype'],
                keep=False)]
            dlist = list(ex.index)
        return df

    def get_market(self):
        if (self.code.startswith("6") or self.code.startswith("500")
                or self.code.startswith("550")
                or self.code.startswith("510")) or self.code.startswith("7"):
            return ct.MARKET_SH
        elif (self.code.startswith("00") or self.code.startswith("30")
              or self.code.startswith("150") or self.code.startswith("159")):
            return ct.MARKET_SZ
        else:
            return ct.MARKET_OTHER

    def get_redis_tick_table(self, cdate):
        cdates = cdate.split('-')
        return "tick_%s_%s_%s" % (self.code, cdates[0], cdates[1])

    def is_tick_table_exists(self, tick_table):
        if self.redis.exists(self.dbname):
            return tick_table in set(
                str(table, encoding="utf8")
                for table in self.redis.smembers(self.dbname))
        return False

    def is_date_exists(self, tick_table, cdate):
        if self.redis.exists(tick_table):
            return cdate in set(
                str(tdate, encoding="utf8")
                for tdate in self.redis.smembers(tick_table))
        return False

    def set_k_data(self):
        prestr = "1" if self.get_market() == ct.MARKET_SH else "0"
        filename = "%s%s.csv" % (prestr, self.code)
        df = pd.read_csv("/data/tdx/history/days/%s" % filename, sep=',')
        df = df[['date', 'open', 'high', 'close', 'low', 'amount', 'volume']]
        df['date'] = df['date'].astype(str)
        df['date'] = pd.to_datetime(df.date).dt.strftime("%Y-%m-%d")
        df = df.rename(columns={'date': 'cdate'})
        df = df.reset_index(drop=True)
        return self.mysql_client.set(df, 'day', method=ct.REPLACE)

    def set_ticket(self, cdate=None):
        cdate = datetime.now().strftime('%Y-%m-%d') if cdate is None else cdate
        if not self.has_on_market(cdate):
            logger.debug("not on market code:%s, date:%s" % (self.code, cdate))
            return
        tick_table = self.get_redis_tick_table(cdate)
        if not self.is_tick_table_exists(tick_table):
            if not self.create_ticket_table(tick_table):
                logger.error("create tick table failed")
                return
        if self.is_date_exists(tick_table, cdate):
            logger.debug("existed code:%s, date:%s" % (self.code, cdate))
            return
        df = ts.get_tick_data(self.code, date=cdate)
        df_tdx = read_tick(
            os.path.join(
                ct.TIC_DIR, '%s.tic' %
                datetime.strptime(cdate, "%Y-%m-%d").strftime("%Y%m%d")),
            self.code)
        if not df_tdx.empty:
            if df is not None and not df.empty and df.loc[0]['time'].find(
                    "当天没有数据") == -1:
                net_volume = df.volume.sum()
                tdx_volume = df_tdx.volume.sum()
                if net_volume != tdx_volume:
                    raise Exception(
                        "code:%s, date:%s, net volume:%s, tdx volume:%s not equal"
                        % (self.code, cdate, net_volume, tdx_volume))
            df = df_tdx
        else:
            if df is None:
                logger.debug("nonedata code:%s, date:%s" % (self.code, cdate))
                return
            if df.empty:
                logger.debug("emptydata code:%s, date:%s" % (self.code, cdate))
                return
            if df.loc[0]['time'].find("当天没有数据") != -1:
                logger.debug("nodata code:%s, date:%s" % (self.code, cdate))
                return
        df.columns = ['ctime', 'price', 'cchange', 'volume', 'amount', 'ctype']
        logger.debug("code:%s date:%s" % (self.code, cdate))
        df = self.merge_ticket(df)
        df['date'] = cdate
        logger.debug("write data code:%s, date:%s, table:%s" %
                     (self.code, cdate, tick_table))
        if self.mysql_client.set(df, tick_table):
            logger.info("start record:%s. table:%s" % (self.code, tick_table))
            self.redis.sadd(tick_table, cdate)

    def get_k_data(self, date=None, dtype=9):
        table_name = self.data_type_dict[dtype]
        if date is not None:
            sql = "select * from %s where date=\"%s\"" % (table_name, date)
        else:
            sql = "select * from %s" % table_name
        return self.mysql_client.get(sql)

    def is_after_release(self, code_id, _date):
        time2Market = self.get('timeToMarket')
        t = time.strptime(str(time2Market), "%Y%m%d")
        y, m, d = t[0:3]
        time2Market = datetime(y, m, d)
        return (datetime.strptime(_date, "%Y-%m-%d") - time2Market).days > 0
Beispiel #2
0
class CIndex(CMysqlObj):
    ZZ_URL_HEAD        = 'http://www.csindex.com.cn/uploads/file/autofile/cons/%scons.xls'
    SZ_URL_HEAD        = 'http://www.szse.cn/api/report/ShowReport?SHOWTYPE=xlsx&CATALOGID=1747_zs&TABKEY=tab1&ZSDM=%s&random=%s'
    ZZ_URL_WEIGHT_HEAD = 'http://www.csindex.com.cn/uploads/file/autofile/closeweight/%scloseweight.xls'
    INDEX_URLS = {
        '000001': (ZZ_URL_HEAD % '000001', [0, 4, 5], ['date', 'code', 'name']),
        '000016': (ZZ_URL_HEAD % '000016', [0, 4, 5], ['date', 'code', 'name']),
        '000300': (ZZ_URL_WEIGHT_HEAD % '000300', [0, 4, 5, 8], ['date', 'code', 'name', 'weight']),
        '000905': (ZZ_URL_WEIGHT_HEAD % '000905', [0, 4, 5, 8], ['date', 'code', 'name', 'weight']),
        '399001': (SZ_URL_HEAD % ('399001', float_random()), [0, 1, 5], ['code', 'name', 'weight']),
        '399005': (SZ_URL_HEAD % ('399005', float_random()), [0, 1, 5], ['code', 'name', 'weight']),
        '399006': (SZ_URL_HEAD % ('399006', float_random()), [0, 1, 5], ['code', 'name', 'weight']),
        '399673': (SZ_URL_HEAD % ('399673', float_random()), [0, 1, 5], ['code', 'name', 'weight'])
    }
    def __init__(self, code, dbinfo = ct.DB_INFO, redis_host = None, should_create_influxdb = False, should_create_mysqldb = False):
        super(CIndex, self).__init__(code, self.get_dbname(code), dbinfo, redis_host)
        self.code = code
        self.influx_client = CInflux(ct.IN_DB_INFO, self.dbname, iredis = self.redis)
        #self.mysql_client.delete_db(self.dbname)
        if not self.create(should_create_influxdb, should_create_mysqldb):
            raise Exception("create index %s table failed" % self.code)

    @staticmethod
    def get_dbname(code):
        return "i%s" % code

    @staticmethod
    def get_redis_name(code):
        return "realtime_i%s" % code

    @staticmethod
    def get_market(code):
        if code.startswith("000") or code.startswith("880"):
            return ct.MARKET_SH
        elif code.startswith("399"):
            return ct.MARKET_SZ
        else:
            return ct.MARKET_OTHER

    def run(self, data):
        if not data.empty:
            self.redis.set(self.get_redis_name(self.dbname), _pickle.dumps(data.tail(1), 2))
            self.influx_client.set(data)

    def get_day_table(self):
        return "%s_day" % self.dbname

    def create_influx_db(self):
        return self.influx_client.create()

    def create(self, should_create_influxdb, should_create_mysqldb):
        influxdb_flag = self.create_influx_db() if should_create_influxdb else True
        mysql_flag = self.create_db(self.dbname) and self.create_mysql_table() if should_create_mysqldb else True
        return influxdb_flag and mysql_flag

    def create_mysql_table(self):
        table_name = self.get_day_table()
        if table_name not in self.mysql_client.get_all_tables():
            sql = 'create table if not exists %s(date varchar(10),\
                                                 open float,\
                                                 high float,\
                                                 close float,\
                                                 preclose float,\
                                                 low float,\
                                                 volume bigint,\
                                                 amount float,\
                                                 preamount float,\
                                                 pchange float,\
                                                 mchange float,\
                                                 PRIMARY KEY(date))' % table_name
            if not self.mysql_client.create(sql, table_name): return False
        return True

    def get_components_table_name(self, cdate):
        cdates = cdate.split('-')
        return "%s_components_%s_%s" % (self.dbname, cdates[0], (int(cdates[1])-1)//3 + 1)

    def get_stock_day_table(self):
        return "%s_day" % self.dbname

    def get_k_data_in_range(self, start_date, end_date):
        table_name = self.get_stock_day_table()
        sql = "select * from %s where date between \"%s\" and \"%s\"" %(table_name, start_date, end_date)
        return self.mysql_client.get(sql)

    def get_k_data(self, date = None):
        table_name = self.get_stock_day_table()
        if date is not None:
            sql = "select * from %s where date=\"%s\"" % (table_name, date)
        else:
            sql = "select * from %s" % table_name
        return self.mysql_client.get(sql)

    def create_components_table(self, table_name):
        sql = 'create table if not exists %s(date varchar(10) not null,\
                                             code varchar(20) not null,\
                                             name varchar(20),\
                                             weight float,\
                                             flag int,\
                                             PRIMARY KEY (date, code))' % table_name
        return True if table_name in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, table_name)

    def get_components_data(self, cdate = datetime.now().strftime('%Y-%m-%d')):
        table_name = self.get_components_table_name(cdate)
        if not self.is_table_exists(table_name): return pd.DataFrame()
        sql = "select * from %s where date=\"%s\"" % (table_name, cdate)
        return self.mysql_client.get(sql)

    def set_components_data_from_joinquant(self, basic_dict, cdate):
        table_name = self.get_components_table_name(cdate)
        if not self.is_table_exists(table_name):
            if not self.create_components_table(table_name):
                logger.error("create components table failed")
                return False
        
        if self.is_date_exists(table_name, cdate): 
            logger.debug("existed table:%s, date:%s" % (table_name, cdate))
            return True

        code = "{}.XSHG".format(self.code) if self.code.startswith("6") else "{}.XSHE".format(self.code)
        stocks = get_index_stocks(code, date = cdate)
        df = pd.DataFrame(stocks, columns =['code'])
        df['code'] = df['code'].str.split('.', expand=True)[0]
        if not df[~df.code.isin(basic_dict)].empty:
            logger.error("code from juquant is not in basic in data {}".format(df[~df.code.isin(basic_dict)]))
            return False
        df['name'] = df['code'].apply(lambda x: basic_dict[x])
        df['date'] = cdate
        if 'wieight' not in df.columns:
            df['weight'] = 1/len(df)
        if 'flag' not in df.columns:
            df['flag'] = 1
        df = df.reset_index(drop = True)
        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clear" % self.code)
            return False
        if self.mysql_client.set(df, table_name):
            if self.redis.sadd(table_name, cdate): return True
        return False

    def set_components_data(self, cdate = datetime.now().strftime('%Y-%m-%d')):
        table_name = self.get_components_table_name(cdate)
        if not self.is_table_exists(table_name):
            if not self.create_components_table(table_name):
                logger.error("create components table failed")
                return False

        if self.is_date_exists(table_name, cdate): 
            logger.debug("existed table:%s, date:%s" % (table_name, cdate))
            return True

        url          = self.INDEX_URLS[self.code][0]
        columns      = self.INDEX_URLS[self.code][1]
        column_names = self.INDEX_URLS[self.code][2]

        df = smart_get(pd.read_excel, url, usecols = columns)
        if df is None:
            logger.error("data for %s is empty" % self.code)
            return False
        df.columns   = column_names
        df.code      = df.code.astype('str').str.zfill(6)
        df['date']   = cdate
        if 'wieight' not in df.columns:
            df['weight'] = 1/len(df)
        if 'flag' not in df.columns:
            df['flag']   = 1
        if self.code == "000001": df = df[df.code.str.startswith('6')]
        df = df.reset_index(drop = True)

        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clear" % self.code)
            return False

        if self.mysql_client.set(df, table_name):
            if self.redis.sadd(table_name, cdate): return True
        return False

    def handle_unexpected_data(self, df):
        if self.code == '399005':
            df.loc[df.date == '2007-06-20', 'amount'] = 7.285928e+09 
        return df

    def read(self, fpath):
        prestr = "1" if self.get_market(self.code) == ct.MARKET_SH else "0"
        filename = "%s%s.csv" % (prestr, self.code)
        dheaders = ['date', 'open', 'high', 'close', 'low', 'amount', 'volume']
        df = pd.read_csv(fpath % filename, sep = ',', usecols = dheaders)
        df['date'] = df['date'].astype(str)
        df['date'] = pd.to_datetime(df.date).dt.strftime("%Y-%m-%d")
        df = self.handle_unexpected_data(df)
        df = df.reset_index(drop = True)
        df = df.sort_values(by = 'date', ascending= True)
        return df

    def set_k_data(self, cdate = None, fpath = "/data/tdx/history/days/%s"):
        if cdate is None:
            return self.set_all_data(fpath)
        else:
            return self.set_day_data(cdate, fpath)

    def set_all_data(self, fpath):
        df = self.read(fpath)
        if 0 == len(df):
            logger.error("no data for %s" % self.code)
            return False
        else:
            if 1 == len(df):
                df.at[0, 'preclose']  = df.loc[0, 'open']
                df.at[0, 'pchange']   = 100 * (df.at[0, 'close'] - df.at[0, 'preclose']) / df.at[0, 'preclose']
                df.at[0, 'preamount'] = df.loc[0, 'amount']
                df.at[0, 'mchange']   = 0
            else:
                df['preclose'] = df['close'].shift(1)
                df.at[0, 'preclose'] = df.loc[0, 'open']
                df['pchange'] = 100 * (df['close'] - df['preclose']) / df['preclose']
                df['preamount'] = df['amount'].shift(1)
                df.at[0, 'preamount'] = df.loc[0, 'amount']
                df['mchange'] = 100 * (df['amount'] - df['preamount']) / df['preamount']

        day_table = self.get_day_table()
        existed_date_list = self.get_existed_keys_list(day_table)
        df = df[~df.date.isin(existed_date_list)]
        df = df.reset_index(drop = True)
        if df.empty: return True

        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clear" % self.code)
            return False

        if not self.mysql_client.set(df, day_table):
            logger.error("set data %s %s failed" % (self.code, day_table))
            return False

        if 0 == self.redis.sadd(day_table, *set(df.date.tolist())):
            logger.error("sadd %s for %s failed" % (self.code, day_table))
            return False
        return True

    def set_day_data(self, cdate, fpath):
        day_table = self.get_day_table()
        if self.is_date_exists(day_table, cdate): 
            logger.debug("existed data for code:%s, date:%s" % (self.code, cdate))
            return True

        df = self.read(fpath)
        if 0 == len(df):
            logger.error("no data for %s:%s" % (cdate, self.code))
            return False
        elif 1 == len(df):
            if len(df.loc[df.date == cdate]) == 0:
                logger.error("no data:%s for %s" % (cdate, self.code))
                return False
            df.at[0, 'preclose']  = df.at[0, 'open']
            df.at[0, 'pchange']   = 100 * (df.at[0, 'close'] - df.at[0, 'preclose']) / df.at[0, 'preclose']
            df.at[0, 'preamount'] = df.at[0, 'amount']
            df.at[0, 'mchange']   = 0
        else:
            index_list = df.loc[df.date == cdate].index.values
            if len(index_list) == 0: 
                logger.error("no data:%s for %s" % (cdate, self.code))
                return False
            preday_index = index_list[0] - 1
            preday_df = df.loc[preday_index]
            df = df.loc[df.date == cdate]
            df['preclose'] = preday_df['close']
            df['pchange'] = 100 * (df['close'] - df['preclose']) / df['preclose']
            df['preamount'] = preday_df['amount']
            df['mchange'] = 100 * (df['amount'] - df['preamount']) / df['preamount']

        df = df.reset_index(drop = True)
        day_table = self.get_day_table()
        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clear" % self.code)
            return False

        if self.mysql_client.set(df, day_table):
            if self.redis.sadd(day_table, cdate):
                return True
        return False

    def get_val_filename(self):
        return "%s_val.csv" % self.dbname

    def get_val_data(self, mdate = None):
        index_val_path = os.path.join("/data/valuation/indexs", self.get_val_filename())
        if not os.path.exists(index_val_path): return None
        df = pd.read_csv(index_val_path)
        if mdate is None:
            return df
        else:
            return df.loc[df.date == mdate].reset_index(drop = True)

    def set_val_data(self, df, mdate = '', fpath = "/data/valuation/indexs"):
        index_val_path = os.path.join(fpath, self.get_val_filename())
        if mdate == '':
            df.to_csv(index_val_path, index=False, header=True, mode='w', encoding='utf8')
        else:
            if not os.path.exists(index_val_path):
                df.to_csv(index_val_path, index=False, header=True, mode='w', encoding='utf8')
            else:
                vdf = self.get_val_data(mdate)
                if vdf.empty:
                    df.to_csv(index_val_path, index=False, header=False, mode='a+', encoding='utf8')
        return True
Beispiel #3
0
class Combination:
    def __init__(self, dbinfo, code):
        self.code = code
        self.dbname = self.get_dbname(code)
        self.redis = create_redis_obj()
        self.data_type_dict = {9: "day"}
        self.influx_client = CInflux(ct.IN_DB_INFO, self.dbname)
        self.mysql_client = cmysql.CMySQL(dbinfo, self.dbname)
        if not self.create():
            raise Exception("create combination table failed")

    @staticmethod
    def get_dbname(code):
        return "c%s" % code

    @staticmethod
    def get_redis_name(code):
        return "realtime_%s" % code

    def create_mysql_table(self):
        for _, table_name in self.data_type_dict.items():
            if table_name not in self.mysql_client.get_all_tables():
                sql = 'create table if not exists %s(date varchar(10), code varchar(10), open float, high float, close float, low float, volume float)' % table_name
                if not self.mysql_client.create(sql, table_name): return False
        return True

    #def create_realtime(self):
    #    sql = 'create table if not exists %s(name varchar(20), code varchar(10), price float, pre_close float, date varchar(25), time varchar(20), amount float, volume float)' % self.realtime_table
    #    return True if self.realtime_table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, self.realtime_table)

    def create_influx_db(self):
        self.influx_client.create()

    def create(self):
        self.create_influx_db()
        return self.create_mysql_table()

    def get_code_list(self):
        contentStr = self.get('content')
        return contentStr.split(',')

    def compute(self):
        code_list = self.get_code_list()
        df = pd.DataFrame()
        for code in code_list:
            df_byte = self.redis.get(CStock.get_redis_name(code))
            if df_byte is None: continue
            df = df.append(_pickle.loads(df_byte))
        num = len(df)
        if 0 == num: return pd.DataFrame()
        _price = df.price.astype(float).sum() / num
        _volume = df.volume.astype(float).sum() / num
        _amount = df.turnover.astype(float).sum() / num
        ctime = datetime.fromtimestamp(
            time.time()).strftime('%Y-%m-%d %H:%M:%S')
        data = {
            'code': [self.code],
            'name': [self.get('name')],
            'time': [ctime],
            'price': [_price],
            'amount': [_amount],
            'volume': [_volume]
        }
        df = pd.DataFrame(data)
        df.time = pd.to_datetime(df.time)
        df = df.set_index('time')
        return df

    def run(self):
        _new_data = self.compute()
        if not _new_data.empty:
            self.redis.set(self.get_redis_name(self.code),
                           _pickle.dumps(_new_data, 2))
            self.influx_client.set(_new_data)

    def get(self, attribute):
        df_byte = self.redis.get(ct.COMBINATION_INFO)
        if df_byte is None: return None
        df = _pickle.loads(df_byte)
        return df.loc[df.code == self.code][attribute].values[0]
class Combination(CMysqlObj):
    def __init__(self,
                 code,
                 should_create_db=False,
                 dbinfo=ct.DB_INFO,
                 redis_host=None):
        super(Combination, self).__init__(code, self.get_dbname(code), dbinfo,
                                          redis_host)
        self.code = code
        self.influx_client = CInflux(ct.IN_DB_INFO,
                                     self.dbname,
                                     iredis=self.redis)
        if should_create_db:
            if not self.create():
                raise Exception("%s create combination table failed" % code)

    @staticmethod
    def get_dbname(code):
        return "c%s" % code

    @staticmethod
    def get_redis_name(code):
        return "realtime_c%s" % code

    def create_influx_db(self):
        return self.influx_client.create()

    def create(self):
        return self.create_influx_db()

    def get_code_list(self):
        contentStr = self.get('content')
        return contentStr.split(',')

    def compute(self):
        code_list = self.get_code_list()
        df = pd.DataFrame()
        for code in code_list:
            df_byte = self.redis.get(CStock.get_redis_name(code))
            if df_byte is None: continue
            df = df.append(_pickle.loads(df_byte))
        num = len(df)
        if 0 == num: return pd.DataFrame()
        _price = df.price.astype(float).sum() / num
        _volume = df.volume.astype(float).sum() / num
        _amount = df.turnover.astype(float).sum() / num
        ctime = datetime.fromtimestamp(
            time.time()).strftime('%Y-%m-%d %H:%M:%S')
        data = {
            'code': [self.code],
            'name': [self.get('name')],
            'time': [ctime],
            'price': [_price],
            'amount': [_amount],
            'volume': [_volume]
        }
        df = pd.DataFrame(data)
        df.time = pd.to_datetime(df.time)
        df = df.set_index('time')
        return df

    def is_table_exists(self, table_name):
        if self.redis.exists(self.dbname):
            return table_name in set(
                table.decode() for table in self.redis.smembers(self.dbname))
        return False

    def is_date_exists(self, table_name, cdate):
        if self.redis.exists(table_name):
            return cdate in set(tdate.decode()
                                for tdate in self.redis.smembers(table_name))
        return False

    def get_existed_keys_list(self, table_name):
        if self.redis.exists(table_name):
            return list(tdate.decode()
                        for tdate in self.redis.smembers(table_name))
        return list()

    def run(self):
        _new_data = self.compute()
        if not _new_data.empty:
            self.redis.set(self.get_redis_name(self.code),
                           _pickle.dumps(_new_data, 2))
            self.influx_client.set(_new_data)

    def get(self, attribute):
        df_byte = self.redis.get(ct.COMBINATION_INFO)
        if df_byte is None: return None
        df = _pickle.loads(df_byte)
        return df.loc[df.code == self.code][attribute].values[0]
Beispiel #5
0
class CStock(CMysqlObj):
    def __init__(self,
                 code,
                 dbinfo=ct.DB_INFO,
                 should_create_influxdb=False,
                 should_create_mysqldb=False,
                 redis_host=None):
        super(CStock, self).__init__(code, self.get_dbname(code), dbinfo,
                                     redis_host)
        self.influx_client = CInflux(ct.IN_DB_INFO,
                                     dbname=self.dbname,
                                     iredis=self.redis)
        if not self.create(should_create_influxdb, should_create_mysqldb):
            raise Exception("create stock %s table failed" % self.code)

    def __del__(self):
        self.influx_client = None

    @staticmethod
    def get_dbname(code):
        return "s%s" % code

    @staticmethod
    def get_redis_name(code):
        return "realtime_s%s" % code

    def adjust_share(self, data, info):
        data['outstanding'] = 0
        data['totals'] = 0
        if 0 == len(info): return pd.DataFrame()
        cur_totals = 0
        cur_outstanding = 0
        next_totals = 0
        next_outstanding = 0
        start_index = 0
        for info_index, end_date in info.date.iteritems():
            cur_outstanding = int(info.at[info_index, 'money'])  #当前流通盘
            cur_totals = int(info.at[info_index, 'price'])  #当前总股本
            next_outstanding = int(info.at[info_index, 'count'])  #后流通盘
            next_totals = int(info.at[info_index, 'rate'])  #后总股本

            dates = data.loc[data.date < end_date].index.tolist()
            if len(dates) == 0:
                if info_index == len(info) - 1:
                    data.at[start_index:, 'outstanding'] = next_outstanding
                    data.at[start_index:, 'totals'] = next_totals
                continue

            end_index = dates[len(dates) - 1]
            #if cur_outstanding != last_pre_outstanding:
            #   logger.debug("%s 日期:%s 前流通盘:%s 不等于 预期前流通盘:%s" % (self.code, start_date, cur_outstanding, last_pre_outstanding))
            #elif cur_totals != last_pre_totals:
            #   logger.debug("%s 日期:%s 后流通盘:%s 不等于 预期后流通盘:%s" % (self.code, start_date, cur_totals, last_pre_totals))
            data.at[start_index:end_index - 1, 'outstanding'] = cur_outstanding
            data.at[start_index:end_index - 1, 'totals'] = cur_totals
            cur_outstanding = next_outstanding
            cur_totals = next_totals
            start_index = end_index

            #finish the last date
            if info_index == len(info) - 1:
                data.at[start_index:, 'outstanding'] = cur_outstanding
                data.at[start_index:, 'totals'] = cur_totals

        data['totals'] = data['totals'].astype(int)
        data['totals'] = data['totals'] * 10000
        data['outstanding'] = data['outstanding'].astype(int)
        data['outstanding'] = data['outstanding'] * 10000
        data = data[data.volume < data.outstanding]
        data = data.reset_index(drop=True)
        return data

    def qfq(self, data, info):
        data['adj'] = 1.0
        data['preclose'] = data['close'].shift(1)
        data.at[0, 'preclose'] = data.loc[0, 'open']
        #if 0 == len(info): return data
        for info_index, start_date in info.date.iteritems():
            dates = data.loc[data.date <= start_date].index.tolist()
            if len(dates) == 0: continue
            rate = info.loc[info_index, 'rate']  #配k股
            price = info.loc[info_index, 'price']  #配股价格
            money = info.loc[info_index, 'money']  #分红
            count = info.loc[info_index, 'count']  #转送股数量
            start_index = dates[len(dates) - 1]
            adj = (data.loc[start_index, 'preclose'] * 10 - money +
                   rate * price) / (
                       (10 + rate + count) * data.loc[start_index, 'preclose'])
            data.at[:start_index - 1,
                    'adj'] = data.loc[:start_index - 1, 'adj'] * adj
        return data

    def has_on_market(self, cdate):
        time2Market = self.get('timeToMarket')
        if str(time2Market) == '0': return False
        t = time.strptime(str(time2Market), "%Y%m%d")
        y, m, d = t[0:3]
        time2Market = datetime(y, m, d)
        return (datetime.strptime(cdate, "%Y-%m-%d") - time2Market).days > 0

    @property
    def is_subnew(self, time2Market=None, timeLimit=365):
        if time2Market == '0': return False  #for stock has not been in market
        if time2Market == None: time2Market = self.get('timeToMarket')
        t = time.strptime(time2Market, "%Y%m%d")
        y, m, d = t[0:3]
        time2Market = datetime(y, m, d)
        return True if (datetime.today() -
                        time2Market).days < timeLimit else False

    def create(self, should_create_influxdb, should_create_mysqldb):
        influxdb_flag = self.create_influx_db(
        ) if should_create_influxdb else True
        mysql_flag = self.create_db(self.dbname) and self.create_mysql_table(
        ) if should_create_mysqldb else True
        return influxdb_flag and mysql_flag

    def create_influx_db(self):
        return self.influx_client.create()

    def create_mysql_table(self):
        table_name = self.get_day_table()
        if table_name not in self.mysql_client.get_all_tables():
            sql = 'create table if not exists %s(date varchar(10) not null,\
                                                 open float,\
                                                 high float,\
                                                 close float,\
                                                 preclose float,\
                                                 low float,\
                                                 volume bigint,\
                                                 amount float,\
                                                 outstanding bigint,\
                                                 totals bigint,\
                                                 adj float,\
                                                 aprice float,\
                                                 pchange float,\
                                                 turnover float,\
                                                 sai float,\
                                                 sri float,\
                                                 uprice float,\
                                                 sprice float,\
                                                 mprice float,\
                                                 lprice float,\
                                                 ppercent float,\
                                                 npercent float,\
                                                 base float,\
                                                 ibase bigint,\
                                                 breakup int,\
                                                 ibreakup bigint,\
                                                 pday int,\
                                                 profit float,\
                                                 gamekline float,\
                                                 PRIMARY KEY(date))' % table_name
            if not self.mysql_client.create(sql, table_name): return False
        return True

    def get(self, attribute):
        df_byte = self.redis.get(ct.STOCK_INFO)
        if df_byte is None: return None
        df = _pickle.loads(df_byte)
        if len(df.loc[df.code == self.code][attribute].values) == 0:
            return None
        return df.loc[df.code == self.code][attribute].values[0]

    def run(self, data):
        self.redis.set(self.get_redis_name(self.code),
                       _pickle.dumps(data.tail(1), 2))
        self.influx_client.set(data)

    def merge_ticket(self, df):
        ex = df[df.duplicated(
            subset=['time', 'cchange', 'volume', 'amount', 'ctype'],
            keep=False)]
        dlist = list(ex.index)
        while len(dlist) > 0:
            snum = 1
            sindex = dlist[0]
            for _index in range(1, len(dlist)):
                if sindex + 1 == dlist[_index]:
                    snum += 1
                    if _index == len(dlist) - 1:
                        df.drop_duplicates(keep='first', inplace=True)
                        df.at[sindex,
                              'volume'] = snum * df.loc[sindex]['volume']
                        df.at[sindex,
                              'amount'] = snum * df.loc[sindex]['amount']
                else:
                    df.drop_duplicates(keep='first', inplace=True)
                    df.at[sindex, 'volume'] = snum * df.loc[sindex]['volume']
                    df.at[sindex, 'amount'] = snum * df.loc[sindex]['amount']
                    sindex = dlist[_index]
                    snum = 1
            df = df.reset_index(drop=True)
            ex = df[df.duplicated(
                subset=['time', 'cchange', 'volume', 'amount', 'ctype'],
                keep=False)]
            dlist = list(ex.index)
        return df

    def get_market(self):
        if self.code.startswith("6") or self.code.startswith(
                "500") or self.code.startswith("550") or self.code.startswith(
                    "510") or self.code.startswith("7"):
            return ct.MARKET_SH
        elif self.code.startswith("00") or self.code.startswith(
                "30") or self.code.startswith("150") or self.code.startswith(
                    "159"):
            return ct.MARKET_SZ
        else:
            return ct.MARKET_OTHER

    def get_chip_distribution_table(self, cdate):
        cdates = cdate.split('-')
        return "chip_%s_%s" % (self.dbname, cdates[0])

    def get_redis_tick_table(self, cdate):
        cdates = cdate.split('-')
        return "tick_%s_%s_%s" % (self.dbname, cdates[0], cdates[1])

    def get_day_table(self):
        return "%s_day" % self.dbname

    def create_chip_table(self, table):
        sql = 'create table if not exists %s(pos int not null,\
                                             sdate varchar(10) not null,\
                                             date varchar(10) not null,\
                                             price decimal(8,2) not null,\
                                             volume bigint not null,\
                                             outstanding bigint not null,\
                                             PRIMARY KEY (pos, sdate, date, price, volume, outstanding))' % table
        return True if table in self.mysql_client.get_all_tables(
        ) else self.mysql_client.create(sql, table)

    def get_pre_str(self):
        return "1" if self.get_market() == ct.MARKET_SH else "0"

    def read(self, cdate=None, fpath="/data/tdx/history/days/%s%s.csv"):
        prestr = self.get_pre_str()
        filename = fpath % (prestr, self.code)
        if not os.path.exists(filename): return pd.DataFrame(), None
        dheaders = ['date', 'open', 'high', 'close', 'low', 'amount', 'volume']
        df = pd.read_csv(filename, sep=',', usecols=dheaders)
        df = df[(df['volume'] > 0) & (df['amount'] > 0)]
        df = df.drop_duplicates(subset=['date'], keep='first')
        df = df.sort_values(by='date', ascending=True)
        df = df.reset_index(drop=True)
        if cdate is not None:
            index_list = df.loc[df.date == transfer_date_string_to_int(
                cdate)].index.values
            if len(index_list) == 0: return pd.DataFrame(), None
            preday_index = index_list[0] - 1
            if preday_index < 0:
                return pd.DataFrame(), None
            else:
                pre_day = df.at[preday_index, 'date']
                return df.loc[df.date == transfer_date_string_to_int(
                    cdate)], transfer_int_to_date_string(pre_day)
        return df, None

    def collect_right_info(self, info, cdate=None):
        info = info[(info.code == self.code)
                    & (info.date <= int(datetime.now().strftime('%Y%m%d')))]
        info = info.sort_values(by='date', ascending=True)
        info = info.reset_index(drop=True)

        #collect stock amount change info
        #6:增发新股(如: 600887 2002-08-20), no change for stock or price
        total_stock_change_type_list = [
            '2', '3', '4', '5', '7', '8', '9', '10', '11'
        ]
        quantity_change_info = info[info.type.isin(
            total_stock_change_type_list)]
        quantity_change_info = quantity_change_info[[
            'date', 'type', 'money', 'price', 'count', 'rate'
        ]]
        quantity_change_info = quantity_change_info.sort_index(ascending=True)
        quantity_change_info = quantity_change_info.reset_index(drop=True)
        quantity_change_info['money'] = quantity_change_info['money'].astype(
            int)
        quantity_change_info['price'] = quantity_change_info['price'].astype(
            int)

        #collect stock price change info
        price_change_info = info[info.type == 1]
        price_change_info = price_change_info[[
            'money', 'price', 'count', 'rate', 'date'
        ]]
        price_change_info = price_change_info.sort_index(ascending=True)
        price_change_info = price_change_info.reset_index(drop=True)
        return quantity_change_info, price_change_info

    def transfer2adjusted(self, df):
        df = df[[
            'date', 'open', 'high', 'close', 'preclose', 'low', 'volume',
            'amount', 'outstanding', 'totals', 'adj'
        ]]
        df['date'] = df['date'].astype(str)
        df['date'] = pd.to_datetime(df.date).dt.strftime("%Y-%m-%d")
        df['low'] = df['adj'] * df['low']
        df['open'] = df['adj'] * df['open']
        df['high'] = df['adj'] * df['high']
        df['close'] = df['adj'] * df['close']
        df['preclose'] = df['adj'] * df['preclose']
        df['volume'] = df['volume'].astype(int)
        df['aprice'] = df['adj'] * df['amount'] / df['volume']
        df['pchange'] = 100 * (df['close'] - df['preclose']) / df['preclose']
        df['turnover'] = 100 * df['volume'] / df['outstanding']
        return df

    def is_need_reright(self, cdate, price_change_info):
        if len(price_change_info) == 0: return False
        now_date = transfer_date_string_to_int(cdate)
        p_index = price_change_info.date.index[-1]
        p_date = price_change_info.date[p_index]
        return now_date == p_date

    def relative_index_strength(self, df, index_df, cdate=None):
        index_df = index_df.loc[index_df.date.isin(df.date.tolist())]
        if len(df) != len(index_df):
            logger.debug("length of code %s is not equal to index." %
                         self.code)
        index_df.index = df.loc[df.date.isin(index_df.date.tolist())].index
        if cdate is None:
            df['sai'] = 0
            df['sri'] = 0
            s_pchange = (df['close'] - df['preclose']) / df['preclose']
            i_pchange = (index_df['close'] -
                         index_df['preclose']) / index_df['preclose']
            df['sri'] = 100 * (s_pchange - i_pchange)
            df['sri'] = df['sri'].fillna(0)
            df.at[(i_pchange < 0) & (s_pchange > 0),
                  'sai'] = df.loc[(i_pchange < 0) & (s_pchange > 0), 'sri']
        else:
            s_pchange = (df.loc[df.date == cdate, 'close'] -
                         df.loc[df.date == cdate, 'preclose']
                         ) / df.loc[df.date == cdate, 'preclose']
            s_pchange = s_pchange.values[0]
            i_pchange = (index_df.loc[index_df.date == cdate, 'close'] -
                         index_df.loc[index_df.date == cdate, 'preclose']
                         ) / index_df.loc[index_df.date == cdate, 'preclose']
            i_pchange = i_pchange.values[0]
            df['sai'] = 100 * (s_pchange - i_pchange
                               ) if s_pchange > 0 and i_pchange < 0 else 0
            df['sri'] = 100 * (s_pchange - i_pchange)
        return df

    def set_today_data(self, df, index_df, pre_date, cdate):
        day_table = self.get_day_table()
        if self.is_date_exists(day_table, cdate):
            logger.debug("existed data for code:%s, date:%s" %
                         (self.code, cdate))
            return True

        index_df = index_df.loc[index_df.date == cdate]

        preday_df = self.get_k_data(date=pre_date)

        if preday_df is None:
            logger.error("%s get pre date data failed." % self.code)
            return False

        if preday_df.empty:
            logger.error("%s get pre date data empty." % self.code)
            return False

        df['adj'] = 1.0
        df['preclose'] = preday_df['close'][0]
        df['totals'] = preday_df['totals'][0]
        df['outstanding'] = preday_df['outstanding'][0]

        #transfer data to split-adjusted share prices
        df = self.transfer2adjusted(df)

        df = self.relative_index_strength(df, index_df, cdate)
        if df is None: return False

        #set chip distribution
        dist_df = df.append(preday_df, sort=False)
        dist_df = dist_df.sort_values(by='date', ascending=True)

        dist_data = self.compute_distribution(dist_df, cdate)
        if dist_data.empty:
            logger.error("%s chip distribution compute failed." % self.code)
            return False
        if self.set_chip_distribution(dist_data, zdate=cdate):
            df['uprice'] = mac(dist_data, 0)
            df['sprice'] = mac(dist_data, 5)
            df['mprice'] = mac(dist_data, 13)
            df['lprice'] = mac(dist_data, 37)
            df = pro_nei_chip(df, dist_data, preday_df, cdate)
            if is_df_has_unexpected_data(df):
                logger.error("data for %s is not clean." % self.code)
                return False
            if self.mysql_client.set(df, self.get_day_table()):
                return self.redis.sadd(day_table, cdate)
        return False

    def set_all_data(self, quantity_change_info, price_change_info,
                     index_info):
        df, _ = self.read()
        if df.empty:
            logger.error("read empty file for:%s" % self.code)
            return False

        #modify price and quanity for all split-adjusted share prices
        df = self.adjust_share(df, quantity_change_info)
        if df.empty: return False
        df = self.qfq(df, price_change_info)
        if df.empty: return False

        #transfer data to split-adjusted share prices
        df = self.transfer2adjusted(df)

        #compute strength relative index
        df = self.relative_index_strength(df, index_info)
        if df is None:
            logger.error("length of code %s is not equal to index." %
                         self.code)
            return False

        #set chip distribution
        #logger.info("compute %s distribution" % self.code)
        dist_data = self.compute_distribution(df)
        if dist_data.empty:
            logger.error("%s is empty distribution." % self.code)
            return False

        if not self.set_chip_distribution(dist_data):
            logger.info("store %s distribution failed" % self.code)
            return False

        df['uprice'] = mac(dist_data, 0)
        df['sprice'] = mac(dist_data, 5)
        df['mprice'] = mac(dist_data, 13)
        df['lprice'] = mac(dist_data, 37)
        df = pro_nei_chip(df, dist_data)

        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clean." % self.code)
            return False

        day_table = self.get_day_table()
        if not self.mysql_client.delsert(df, day_table):
            logger.error("save %s data to mysql failed." % self.code)
            return False
        self.redis.sadd(day_table, *set(df.date.tolist()))
        return True

    def get_base_floating_profit(self, date=None):
        return self.get_k_data(date)

    def get_base_floating_profit_in_range(self, start_date, end_date):
        return self.get_k_data_in_range(start_date, end_date)

    def set_base_floating_profit(self):
        df = self.get_k_data()
        if df is None: return False
        df['base'] = 0.0
        df['ibase'] = 0
        df['breakup'] = 0
        df['ibreakup'] = 0
        df['pday'] = 0
        df['profit'] = 0.0
        df = base_floating_profit(df)
        return self.mysql_client.delsert(df, self.get_day_table())

    def set_k_data(self, bonus_info, index_info, cdate=None):
        if not self.has_on_market(datetime.now().strftime('%Y-%m-%d')):
            return True
        quantity_change_info, price_change_info = self.collect_right_info(
            bonus_info)
        if cdate is None or self.is_need_reright(cdate, price_change_info):
            return self.set_all_data(quantity_change_info, price_change_info,
                                     index_info)
        else:
            today_df, pre_date = self.read(cdate)
            if today_df.empty: return True
            if pre_date is None:
                return self.set_all_data(quantity_change_info,
                                         price_change_info, index_info)
            else:
                return self.set_today_data(today_df, index_info, pre_date,
                                           cdate)

    def get_chip_distribution(self, mdate=None):
        df = pd.DataFrame()
        if mdate is not None:
            table = self.get_chip_distribution_table(mdate)
            if self.is_table_exists(table):
                df = self.mysql_client.get("select * from %s" % table)
                return df.loc[df.date == mdate]
        else:
            time2Market = self.get('timeToMarket')
            start_year = int(time2Market / 10000)
            end_year = int(datetime.now().strftime('%Y'))
            year_list = get_years_between(start_year, end_year)
            for table in [
                    self.get_chip_distribution_table(myear)
                    for myear in year_list
            ]:
                if self.is_table_exists(table):
                    tmp_df = self.mysql_client.get("select * from %s" % table)
                    df = df.append(tmp_df)
            df = df.reset_index(drop=True)
        return df

    def compute_distribution(self, data, zdate=None):
        data = data[[
            'date', 'open', 'aprice', 'outstanding', 'volume', 'amount'
        ]]
        if zdate is None:
            df = compute_distribution(data)
        else:
            mdate_list = data.date.tolist()
            pre_date, now_date = mdate_list
            if now_date != zdate:
                logger.error(
                    "%s data new date %s is not equal to now date %s" %
                    (self.code, now_date, zdate))
                return pd.DataFrame()

            pre_date_dist = self.get_chip_distribution(pre_date)
            if pre_date_dist.empty:
                logger.error("pre data for %s dist %s is empty" %
                             (self.code, pre_date))
                return pd.DataFrame()
            pre_date_dist = pre_date_dist.sort_values(by='pos', ascending=True)
            pos = data.loc[data.date == zdate].index[0]
            volume = data.loc[data.date == zdate, 'volume'].tolist()[0]
            aprice = data.loc[data.date == zdate, 'aprice'].tolist()[0]
            outstanding = data.loc[data.date == zdate,
                                   'outstanding'].tolist()[0]
            pre_outstanding = data.loc[data.date == pre_date,
                                       'outstanding'].tolist()[0]
            zdate = zdate.encode("UTF-8")
            df = compute_oneday_distribution(pre_date_dist, zdate, pos, volume,
                                             aprice, pre_outstanding,
                                             outstanding)
        return df

    def set_chip_table(self, df, myear):
        #get new df
        tmp_df = df.loc[df.date.str.startswith(myear)]
        #some stock halted more than one year
        if tmp_df.empty: return (myear, True)
        tmp_df = tmp_df.reset_index(drop=True)
        #get chip table name
        chip_table = self.get_chip_distribution_table(myear)
        if not self.is_table_exists(chip_table):
            if not self.create_chip_table(chip_table):
                logger.error("create chip table:%s failed" % chip_table)
                return (myear, False)
            if not self.mysql_client.set(tmp_df, chip_table):
                return (myear, False)
        else:
            #update df to mysql
            if not self.mysql_client.delsert(tmp_df, chip_table):
                return (myear, False)
        self.redis.sadd(chip_table, *set(tmp_df.date.tolist()))
        return (myear, True)

    def set_chip_distribution(self, df, zdate=None):
        if zdate is None:
            time2Market = self.get('timeToMarket')
            start_year = int(time2Market / 10000)
            end_year = int(datetime.now().strftime('%Y'))
            year_list = get_years_between(start_year, end_year)
            cfunc = partial(self.set_chip_table, df)
            return concurrent_run(cfunc, year_list, num=20)
        else:
            chip_table = self.get_chip_distribution_table(zdate)
            if not self.is_table_exists(chip_table):
                if not self.create_chip_table(chip_table):
                    logger.error("create chip table:%s failed" % chip_table)
                    return False

            if self.is_date_exists(chip_table, zdate):
                logger.debug("existed chip for code:%s, date:%s" %
                             (self.code, zdate))
                return True

            if is_df_has_unexpected_data(df):
                logger.error("data for %s is not clear" % self.code)
                return False

            if self.mysql_client.set(df, chip_table):
                if self.redis.sadd(chip_table, zdate):
                    logger.debug("finish record chip:%s. table:%s" %
                                 (self.code, chip_table))
                    return True
            return False

    def set_ticket(self, cdate=None):
        cdate = datetime.now().strftime('%Y-%m-%d') if cdate is None else cdate
        if not self.has_on_market(cdate):
            logger.debug("not on market code:%s, date:%s" % (self.code, cdate))
            return True
        tick_table = self.get_redis_tick_table(cdate)
        if not self.is_table_exists(tick_table):
            if not self.create_ticket_table(tick_table):
                logger.error("create tick table failed")
                return False
            if not self.redis.sadd(self.dbname, tick_table):
                logger.error("add tick table to redis failed")
                return False
        if self.is_date_exists(tick_table, cdate):
            logger.debug("existed code:%s, date:%s" % (self.code, cdate))
            return True
        logger.debug("%s read code from file %s" % (self.code, cdate))
        df = ts.get_tick_data(self.code, date=cdate)
        df_tdx = read_tick(
            os.path.join(
                ct.TIC_DIR, '%s.tic' %
                datetime.strptime(cdate, "%Y-%m-%d").strftime("%Y%m%d")),
            self.code)
        if not df_tdx.empty:
            if df is not None and not df.empty and df.loc[0]['time'].find(
                    "当天没有数据") == -1:
                net_volume = df.volume.sum()
                tdx_volume = df_tdx.volume.sum()
                if net_volume != tdx_volume:
                    logger.error(
                        "code:%s, date:%s, net volume:%s, tdx volume:%s not equal"
                        % (self.code, cdate, net_volume, tdx_volume))
            df = df_tdx
        else:
            if df is None:
                logger.debug("nonedata code:%s, date:%s" % (self.code, cdate))
                return True
            if df.empty:
                logger.debug("emptydata code:%s, date:%s" % (self.code, cdate))
                return True
            if df.loc[0]['time'].find("当天没有数据") != -1:
                logger.debug("nodata code:%s, date:%s" % (self.code, cdate))
                return True
        df.columns = ['time', 'price', 'cchange', 'volume', 'amount', 'ctype']
        logger.debug("merge ticket code:%s date:%s" % (self.code, cdate))
        df = self.merge_ticket(df)
        df['date'] = cdate
        logger.debug("write data code:%s, date:%s, table:%s" %
                     (self.code, cdate, tick_table))

        if is_df_has_unexpected_data(df):
            logger.error("data for %s is not clear" % self.code)
            return False

        if self.mysql_client.set(df, tick_table):
            logger.debug("finish record:%s. table:%s" %
                         (self.code, tick_table))
            if self.redis.sadd(tick_table, cdate):
                return True
        return False

    def get_ticket(self, cdate):
        cdate = datetime.now().strftime('%Y-%m-%d') if cdate is None else cdate
        if not self.has_on_market(cdate):
            logger.debug("not on market code:%s, date:%s" % (self.code, cdate))
            return
        sql = "select * from %s where date=\"%s\"" % (
            self.get_redis_tick_table(cdate), cdate)
        return self.mysql_client.get(sql)

    def get_k_data_in_range(self, start_date, end_date, dtype=9):
        table_name = self.get_day_table()
        sql = "select * from %s where date between \"%s\" and \"%s\"" % (
            table_name, start_date, end_date)
        return self.mysql_client.get(sql)

    def get_k_data(self, date=None, dtype=9):
        table_name = self.get_day_table()
        if date is not None:
            sql = "select * from %s where date=\"%s\"" % (table_name, date)
        else:
            sql = "select * from %s" % table_name
        return self.mysql_client.get(sql)