Python sqlalchemy.dialects.mysql.DOUBLE Examples

def import_coin_info(chain_param=None):
    """获取全球交易币基本信息"""
    table_name = 'tushare_coin_info'
    has_table = engine_md.has_table(table_name)
    # 设置 dtype
    dtype = {
        'coin': String(60),
        'en_name': String(60),
        'cn_name': String(60),
        'issue_date': Date,
        'amount': DOUBLE,
    }
    coinlist_df = pro.coinlist(start_date='20170101', end_date=date_2_str(date.today(), DATE_FORMAT_STR))
    data_count = bunch_insert_on_duplicate_update(coinlist_df, table_name, engine_md, dtype)
    logging.info("更新 %s 完成 新增数据 %d 条", table_name, data_count)

    if not has_table and engine_md.has_table(table_name):
        alter_table_2_myisam(engine_md, [table_name])
        create_pk_str = """ALTER TABLE {table_name}
            CHANGE COLUMN `coin` `coin` VARCHAR(60) NOT NULL FIRST,
            CHANGE COLUMN `en_name` `en_name` VARCHAR(60) NOT NULL AFTER `coin`,
            ADD PRIMARY KEY (`coin`, `en_name`)""".format(table_name=table_name)
        with with_db_session(engine_md) as session:
            session.execute(create_pk_str) 

def add_new_col_data_to_fin(col_name, param, chain_param=None, db_col_name=None, col_type_str='DOUBLE', ths_code_set: set = None):
    """
    1）修改 fin 表，增加字段
    2）ckpv表增加数据
    3）第二部不见得1天能够完成，当第二部完成后，将ckvp数据更新fin表中
    :param col_name:增加字段名称
    :param param: 参数
    :param chain_param: 该参数仅用于 task.chain 串行操作时，上下传递参数使用
    :param dtype: 数据库字段类型
    :param db_col_name: 默认为 None，此时与col_name相同
    :param col_type_str: DOUBLE, VARCHAR(20), INTEGER, etc. 不区分大小写
    :param ths_code_set: 默认 None， 否则仅更新指定 ths_code
    :return:
    """
    table_name = 'ifind_stock_fin'
    if db_col_name is None:
        # 默认为 None，此时与col_name相同
        db_col_name = col_name

    # 检查当前数据库是否存在 db_col_name 列，如果不存在则添加该列
    add_col_2_table(engine_md, table_name, db_col_name, col_type_str)
    # 将数据增量保存到 ckdvp 表
    all_finished = add_data_fin_2_ckdvp(col_name, param, ths_code_set)
    # 将数据更新到 ds 表中
    if all_finished:
        sql_str = """update {table_name} daily, ifind_stock_ckdvp ckdvp
        set daily.{db_col_name} = ckdvp.value
        where daily.ths_code = ckdvp.ths_code
        and daily.time = ckdvp.time
        and ckdvp.key = '{db_col_name}'
        and ckdvp.param = '{param}'""".format(table_name=table_name, db_col_name=db_col_name, param=param)
        with with_db_session(engine_md) as session:
            session.execute(sql_str)
            session.commit()
        logger.info('更新 %s 字段 %s 表', db_col_name, table_name) 

def save_future_daily_df_list(data_df_list):
    """将期货历史数据保存的数据库"""
    data_df_count = len(data_df_list)
    if data_df_count > 0:
        logger.info('merge data with %d df', data_df_count)
        data_df = pd.concat(data_df_list)
        data_count = data_df.shape[0]
        data_df.to_sql('ifind_future_daily', engine_md, if_exists='append', index=False,
                       dtype={
                           'ths_code': String(20),
                           'time': Date,
                           'preClose': String(20),
                           'open': DOUBLE,
                           'high': DOUBLE,
                           'low': DOUBLE,
                           'close': DOUBLE,
                           'volume': DOUBLE,
                           'amount': DOUBLE,
                           'avgPrice': DOUBLE,
                           'change': DOUBLE,
                           'changeRatio': DOUBLE,
                           'preSettlement': DOUBLE,
                           'settlement': DOUBLE,
                           'change_settlement': DOUBLE,
                           'chg_settlement': DOUBLE,
                           'openInterest': DOUBLE,
                           'positionChange': DOUBLE,
                           'amplitude': DOUBLE,
                       })
        logger.info("更新 wind_future_daily 结束 %d 条记录被更新", data_count)
    else:
        logger.info("更新 wind_future_daily 结束 0 条记录被更新") 

def add_new_col_data(col_name, param, chain_param=None, db_col_name=None, col_type_str='DOUBLE', ths_code_set: set = None):
    """
    1）修改 daily 表，增加字段
    2）ckpv表增加数据
    3）第二部不见得1天能够完成，当第二部完成后，将ckvp数据更新daily表中
    :param col_name:增加字段名称
    :param param: 参数
    :param chain_param: 该参数仅用于 task.chain 串行操作时，上下传递参数使用
    :param db_col_name: 默认为 None，此时与col_name相同
    :param col_type_str: DOUBLE, VARCHAR(20), INTEGER, etc. 不区分大小写
    :param ths_code_set: 默认 None， 否则仅更新指定 ths_code
    :return:
    """
    table_name = 'ifind_stock_daily_ds'
    if db_col_name is None:
        # 默认为 None，此时与col_name相同
        db_col_name = col_name

    # 检查当前数据库是否存在 db_col_name 列，如果不存在则添加该列
    add_col_2_table(engine_md, table_name, db_col_name, col_type_str)
    # 将数据增量保存到 ckdvp 表
    all_finished = add_data_2_ckdvp(col_name, param, ths_code_set)
    # 将数据更新到 ds 表中
    if all_finished:
        sql_str = """update {table_name} daily, ifind_ckdvp_stock ckdvp
        set daily.{db_col_name} = ckdvp.value
        where daily.ths_code = ckdvp.ths_code
        and daily.time = ckdvp.time
        and ckdvp.key = '{db_col_name}'
        and ckdvp.param = '{param}'""".format(table_name=table_name, db_col_name=db_col_name, param=param)
        with with_db_session(engine_md) as session:
            session.execute(sql_str)
            session.commit()
        logger.info('更新 %s 字段 %s 表', db_col_name, table_name) 

def add_new_col_data(col_name, param, chain_param=None, db_col_name=None, col_type_str='DOUBLE', ths_code_set: set = None):
    """
    1）修改 daily 表，增加字段
    2）ckpv表增加数据
    3）第二部不见得1天能够完成，当第二部完成后，将ckvp数据更新daily表中
    :param chain_param: 该参数仅用于 task.chain 串行操作时，上下传递参数使用
    :param col_name:增加字段名称
    :param param: 参数
    :param db_col_name: 默认为 None，此时与col_name相同
    :param col_type_str: DOUBLE, VARCHAR(20), INTEGER, etc. 不区分大小写
    :param ths_code_set: 默认 None， 否则仅更新指定 ths_code
    :return:
    """
    if db_col_name is None:
        # 默认为 None，此时与col_name相同
        db_col_name = col_name
    table_name = 'ifind_index_daily_ds'
    # 检查当前数据库是否存在 db_col_name 列，如果不存在则添加该列
    add_col_2_table(engine_md, table_name, db_col_name, col_type_str)
    # 将数据增量保存到 ckdvp 表
    all_finished = add_data_2_ckdvp(col_name, param, ths_code_set)
    # 将数据更新到 ds 表中
    if all_finished:
        sql_str = """update ifind_index_daily_ds daily, ifind_ckdvp_index ckdvp
        set daily.{db_col_name} = ckdvp.value
        where daily.ths_code = ckdvp.ths_code
        and daily.time = ckdvp.time
        and ckdvp.key = '{db_col_name}'
        and ckdvp.param = '{param}'""".format(db_col_name=db_col_name, param=param)
        with with_db_session(engine_md) as session:
            session.execute(sql_str)
            session.commit()
        logger.info('更新 %s 字段 ifind_index_daily_ds 表', db_col_name) 

def add_new_col_data(col_name, param, chain_param=None, db_col_name=None, col_type_str='DOUBLE',
                     wind_code_set: set = None):
    """
    1）修改 daily 表，增加字段
    2）wind_ckdvp_stock_hk表增加数据
    3）第二部不见得1天能够完成，当第二部完成后，将wind_ckdvp_stock_hk数据更新daily表中
    :param col_name:增加字段名称
    :param param: 参数
    :param chain_param:  在celery 中將前面結果做爲參數傳給後面的任務
    :param db_col_name: 默认为 None，此时与col_name相同
    :param col_type_str: DOUBLE, VARCHAR(20), INTEGER, etc. 不区分大小写
    :param wind_code_set: 默认 None， 否则仅更新指定 wind_code
    :return:
    """
    if db_col_name is None:
        # 默认为 None，此时与col_name相同
        db_col_name = col_name

    # 检查当前数据库是否存在 db_col_name 列，如果不存在则添加该列
    add_col_2_table(engine_md, 'wind_stock_daily_hk', db_col_name, col_type_str)
    # 将数据增量保存到 wind_ckdvp_stock_hk 表
    all_finished = add_data_2_ckdvp(col_name, param, wind_code_set)
    # 将数据更新到 ds 表中
    # 对表的列进行整合，daily表的列属性值插入wind_ckdvp_stock_hk的value 根据所有条件进行判定
    if all_finished:
        sql_str = """
            update wind_stock_daily_hk daily, wind_ckdvp_stock_hk ckdvp
            set daily.{db_col_name} = ckdvp.value
            where daily.wind_code = ckdvp.wind_code
            and ckdvp.key = '{db_col_name}' and ckdvp.param = '{param}'
            and ckdvp.time = daily.trade_date""".format(db_col_name=db_col_name, param=param)
        # 进行事务提交
        with with_db_session(engine_md) as session:
            rst = session.execute(sql_str)
            data_count = rst.rowcount
            session.commit()
        logger.info('更新 %s 字段 wind_stock_daily_hk 表 %d 条记录', db_col_name, data_count) 

def fund_nav_df_2_sql(table_name, fund_nav_df, engine_md, is_append=True):
    col_name_param_list = [
        ('NAV_DATE', Date),
        ('NAV', DOUBLE),
        ('NAV_ACC', DOUBLE),

    ]
    col_name_dic = {col_name.upper(): col_name.lower() for col_name, _ in col_name_param_list}
    dtype = {col_name.lower(): val for col_name, val in col_name_param_list}
    dtype['wind_code'] = String(200)
    dtype['trade_date'] = Date
    #    print('reorg dfnav data[%d, %d]' % fund_nav_df.shape)
    try:
        fund_nav_df['NAV_DATE'] = pd.to_datetime(fund_nav_df['NAV_DATE']).apply(lambda x: x.date())
    except Exception as exp:
        logger.exception(str(fund_nav_df['NAV_DATE']))
        return None
    trade_date_s = pd.to_datetime(fund_nav_df.index)
    trade_date_latest = trade_date_s.max().date()
    fund_nav_df['trade_date'] = trade_date_s
    fund_nav_df.rename(columns=col_name_dic, inplace=True)
    # fund_nav_df['trade_date'] = trade_date_s
    fund_nav_df.set_index(['wind_code', 'trade_date'], inplace=True)
    fund_nav_df.reset_index(inplace=True)
    # action_str = 'append' if is_append else 'replace'
    #    print('df--> sql fundnav table if_exists="%s"' % action_str)
    bunch_insert_on_duplicate_update(fund_nav_df, table_name, engine_md, dtype=dtype)

    # fund_nav_df.to_sql(table_name, engine_md, if_exists=action_str, index_label=['wind_code', 'trade_date'],
    #                    dtype={
    #                        'wind_code': String(200),
    #                        'nav_date': Date,
    #                        'trade_date': Date,
    #                    })  # , index=False
    logger.info('%d data inserted', fund_nav_df.shape[0])
    return trade_date_latest 

def add_new_col_data(col_name, param, chain_param=None, db_col_name=None, col_type_str='DOUBLE',
                     wind_code_set: set = None):
    """
    1）修改 daily 表，增加字段
    2）wind_ckdvp_stock表增加数据
    3）第二部不见得1天能够完成，当第二部完成后，将wind_ckdvp_stock数据更新daily表中
    :param col_name:增加字段名称
    :param param: 参数
    :param chain_param:  在celery 中將前面結果做爲參數傳給後面的任務
    :param db_col_name: 默认为 None，此时与col_name相同
    :param col_type_str: DOUBLE, VARCHAR(20), INTEGER, etc. 不区分大小写
    :param wind_code_set: 默认 None， 否则仅更新指定 wind_code
    :return:
    """
    if db_col_name is None:
        # 默认为 None，此时与col_name相同
        db_col_name = col_name

    # 检查当前数据库是否存在 db_col_name 列，如果不存在则添加该列
    add_col_2_table(engine_md, 'wind_stock_daily', db_col_name, col_type_str)
    # 将数据增量保存到 wind_ckdvp_stock 表
    all_finished = add_data_2_ckdvp(col_name, param, wind_code_set)
    # 将数据更新到 ds 表中
    # 对表的列进行整合，daily表的列属性值插入wind_ckdvp_stock的value 根据所有条件进行判定
    if all_finished:
        sql_str = """
            update wind_stock_daily daily, wind_ckdvp_stock ckdvp
            set daily.{db_col_name} = ckdvp.value
            where daily.wind_code = ckdvp.wind_code
            and ckdvp.key = '{db_col_name}' and ckdvp.param = '{param}'

            and ckdvp.time = daily.trade_date""".format(db_col_name=db_col_name, param=param)
        # 进行事务提交
        with with_db_session(engine_md) as session:
            rst = session.execute(sql_str)
            data_count = rst.rowcount
            session.commit()
        logger.info('更新 %s 字段 wind_stock_daily 表 %d 条记录', db_col_name, data_count) 

def insert_into_db(data_df_list, engine_md):
    data_count = len(data_df_list)
    table_name = 'wind_stock_tick'
    has_table = engine_md.has_table(table_name)
    param_list = [
        ('datetime', DateTime),
        ('open', DOUBLE),
        ('high', DOUBLE),
        ('low', DOUBLE),
        ('close', DOUBLE),
        ('ask1', DOUBLE),
        ('bid1', DOUBLE),
        ('asize1', DOUBLE),
        ('bsize1', DOUBLE),
        ('volume', DOUBLE),
        ('amount', DOUBLE),
        ('preclose', DOUBLE),
    ]
    dtype = {key: val for key, val in param_list}
    dtype['wind_code'] = String(20)
    if data_count > 0:
        data_df_all = pd.concat(data_df_list)
        data_df_all.index.rename('datetime', inplace=True)
        data_df_all.reset_index(inplace=True)
        bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype=dtype)
        logger.info('%d data imported', data_df_all.shape[0])
        if not has_table and engine_md.has_table(table_name):
            alter_table_2_myisam(engine_md, [table_name])
            build_primary_key([table_name])

    return data_count 

def merge_latest(chain_param=None, ):
    """
    将 cmc_coin_v1_daily 历史数据 以及 cmc_coin_pro_latest 最新价格数据 合并到 cmc_coin_merged_latest
    :return:
    """
    table_name = 'cmc_coin_merged_latest'
    logger.info("开始合并数据到 %s 表", table_name)
    has_table = engine_md.has_table(table_name)
    create_sql_str = """CREATE TABLE {table_name} (
      `id` VARCHAR(60) NOT NULL,
      `date` DATE NOT NULL,
      `datetime` DATETIME NULL,
      `name` VARCHAR(60) NULL,
      `symbol` VARCHAR(20) NULL,
      `close` DOUBLE NULL,
      `volume` DOUBLE NULL,
      `market_cap` DOUBLE NULL,
      PRIMARY KEY (`id`, `date`))
    ENGINE = MyISAM""".format(table_name=table_name)
    with with_db_session(engine_md) as session:
        if not has_table:
            session.execute(create_sql_str)
            logger.info("创建 %s 表", table_name)
        session.execute('truncate table {table_name}'.format(table_name=table_name))
        insert_sql_str = """INSERT INTO `{table_name}` 
            (`id`, `date`, `datetime`, `name`, `symbol`, `close`, `volume`, `market_cap`) 
            select daily.id, `date`, `date`, `name`, `symbol`, `close`, `volume`, `market_cap` 
            from cmc_coin_v1_daily daily
            left join cmc_coin_v1_info info
            on daily.id = info.id""".format(table_name=table_name)
        session.execute(insert_sql_str)
        session.commit()
        insert_latest_sql_str = """INSERT INTO `{table_name}` 
            (`id`, `date`, `datetime`, `name`, `symbol`, `close`, `volume`, `market_cap`) 
            select info.id, date(latest.last_updated), latest.last_updated, 
                latest.name, latest.symbol, price, volume_24h, market_cap
            from cmc_coin_pro_latest latest
            left join
            (
                select latest.name, latest.symbol, max(latest.last_updated) last_updated
                from cmc_coin_pro_latest latest
                group by latest.name, latest.symbol
            ) g
            on latest.name = g.name
            and latest.symbol = g.symbol
            and latest.last_updated = g.last_updated
            left outer join cmc_coin_v1_info info
            on latest.name = info.name
            and latest.symbol = info.symbol
            on duplicate key update
                `datetime`=values(`datetime`), 
                `name`=values(`name`), 
                `symbol`=values(`symbol`), 
                `close`=values(`close`), 
                `volume`=values(`volume`), 
                `market_cap`=values(`market_cap`)""".format(table_name=table_name)
        session.execute(insert_latest_sql_str)
        session.commit()
        data_count = session.execute("select count(*) from {table_name}".format(table_name=table_name)).scalar()
        logger.info("%d 条记录插入到 %s", data_count, table_name) 

def import_coin_info(chain_param=None, ):
    """插入基础信息数据到 cmc_coin_v1_info"""
    table_name = "cmc_coin_v1_info"
    logging.info("更新 %s 开始", table_name)
    has_table = engine_md.has_table(table_name)
    # url = 'https://api.coinmarketcap.com/v2/listings/'
    # dtype = {
    #     'id': String(60),
    #     'name': String(60),
    #     'symbol': String(20),
    #     'website_slug': String(60),
    # }

    url = 'https://api.coinmarketcap.com/v1/ticker/?limit=0'
    dtype = {
        'id': String(60),
        'name': String(60),
        'symbol': String(20),
        'rank': Integer,
        'price_usd': DOUBLE,
        'price_btc': DOUBLE,
        '24h_volume_usd': DOUBLE,
        'market_cap_usd': DOUBLE,
        'available_supply': DOUBLE,
        'total_supply': DOUBLE,
        'max_supply': DOUBLE,
        'percent_change_1h': DOUBLE,
        'percent_change_24h': DOUBLE,
        'percent_change_7d': DOUBLE,
        'last_updated': DATETIME,
    }
    rsp = requests.get(url)
    if rsp.status_code != 200:
        raise ValueError('请求 listings 相应失败')
    json = rsp.json()
    data_df = pd.DataFrame(json)
    data_df['last_updated'] = data_df['last_updated'].apply(
        lambda x: None if x is None else datetime.datetime.fromtimestamp(float(x)))
    data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, dtype=dtype)
    logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)
    if not has_table and engine_md.has_table(table_name):
        alter_table_2_myisam(engine_md, [table_name])
        # build_primary_key([table_name])
        create_pk_str = """ALTER TABLE {table_name}
        CHANGE COLUMN `id` `id` VARCHAR(60) NOT NULL FIRST ,
        ADD PRIMARY KEY (`id`)""".format(table_name=table_name)
        with with_db_session(engine_md) as session:
            session.execute(create_pk_str)

    # 更新 code_mapping 表
    # update_from_info_table(table_name) 

def update_df_2_db(instrument_type, table_name, data_df):
    """将 DataFrame 数据保存到 数据库对应的表中"""
    dtype = {
        'trade_date': Date,
        'Contract': String(20),
        'ContractNext': String(20),
        'Close': DOUBLE,
        'CloseNext': DOUBLE,
        'TermStructure': DOUBLE,
        'Volume': DOUBLE,
        'VolumeNext': DOUBLE,
        'OI': DOUBLE,
        'OINext': DOUBLE,
        'Open': DOUBLE,
        'OpenNext': DOUBLE,
        'High': DOUBLE,
        'HighNext': DOUBLE,
        'Low': DOUBLE,
        'LowNext': DOUBLE,
        'WarehouseWarrant': DOUBLE,
        'WarehouseWarrantNext': DOUBLE,
        'adj_factor_main': DOUBLE,
        'adj_factor_secondary': DOUBLE,
        'instrument_type': String(20),
    }
    # 为了解决 AttributeError: 'numpy.float64' object has no attribute 'translate' 错误，需要将数据类型转换成 float
    data_df["Close"] = data_df["Close"].apply(str_2_float)
    data_df["CloseNext"] = data_df["CloseNext"].apply(str_2_float)
    data_df["TermStructure"] = data_df["TermStructure"].apply(str_2_float)
    data_df["Volume"] = data_df["Volume"].apply(str_2_float)
    data_df["VolumeNext"] = data_df["VolumeNext"].apply(str_2_float)
    data_df["OI"] = data_df["OI"].apply(str_2_float)
    data_df["OINext"] = data_df["OINext"].apply(str_2_float)
    data_df["Open"] = data_df["Open"].apply(str_2_float)
    data_df["OpenNext"] = data_df["OpenNext"].apply(str_2_float)
    data_df["High"] = data_df["High"].apply(str_2_float)
    data_df["HighNext"] = data_df["HighNext"].apply(str_2_float)
    data_df["Low"] = data_df["Low"].apply(str_2_float)
    data_df["LowNext"] = data_df["LowNext"].apply(str_2_float)
    data_df["WarehouseWarrant"] = data_df["WarehouseWarrant"].apply(str_2_float)
    data_df["WarehouseWarrantNext"] = data_df["WarehouseWarrantNext"].apply(str_2_float)
    data_df["adj_factor_main"] = data_df["adj_factor_main"].apply(str_2_float)
    data_df["adj_factor_secondary"] = data_df["adj_factor_secondary"].apply(str_2_float)
    # 清理历史记录
    with with_db_session(engine_md) as session:
        sql_str = """SELECT table_name FROM information_schema.TABLES 
            WHERE table_name = :table_name and TABLE_SCHEMA=(select database())"""
        # 复权数据表
        is_existed = session.execute(sql_str, params={"table_name": table_name}).fetchone()
        if is_existed is not None:
            session.execute("delete from %s where instrument_type = :instrument_type" % table_name,
                            params={"instrument_type": instrument_type})
            logger.debug("删除 %s 中的 %s 历史数据", table_name, instrument_type)

    # 插入数据库
    # pd.DataFrame.to_sql(data_df, table_name, engine_md, if_exists='append', index=False, dtype=dtype)
    bunch_insert_on_duplicate_update(data_df, table_name, engine_md,
                                     dtype=dtype, myisam_if_create_table=True,
                                     primary_keys=['trade_date', 'Contract'], schema=config.DB_SCHEMA_MD) 

def update_future_info_hk(chain_param=None):
    """
    更新 香港股指 期货合约列表信息
    香港恒生指数期货，香港国企指数期货合约只有07年2月开始的合约，且无法通过 wset 进行获取
    :param chain_param:  在celery 中將前面結果做爲參數傳給後面的任務
    :return:
    """
    table_name = "wind_future_info_hk"
    has_table = engine_md.has_table(table_name)
    param_list = [
        ("ipo_date", Date),
        ("sec_name", String(50)),
        ("sec_englishname", String(50)),
        ("exch_eng", String(50)),
        ("lasttrade_date", Date),
        ("lastdelivery_date", Date),
        ("dlmonth", String(50)),
        ("lprice", Date),
        ("sccode", String(50)),
        ("margin", Date),
        ("punit", String(50)),
        ("changelt", Date),
        ("mfprice", Date),
        ("contractmultiplier", DOUBLE),
        ("ftmargins", String(100)),
        ("trade_code", String(50)),
    ]
    wind_indictor_str = ",".join([key for key, _ in param_list])
    dtype = {key: val for key, val in param_list}
    dtype['wind_code'] = String(20)
    logger.info("更新 wind_future_info_hk 开始")
    # 获取已存在合约列表
    sql_str = 'select wind_code, ipo_date from wind_future_info_hk'
    with with_db_session(engine_md) as session:
        table = session.execute(sql_str)
        wind_code_ipo_date_dic = dict(table.fetchall())

    # 获取合约列表
    # 手动生成合约列表
    # 香港恒生指数期货，香港国企指数期货合约只有07年2月开始的合约，且无法通过 wset 进行获取
    wind_code_list = ['%s%02d%02d.HK' % (name, year, month)
                      for name, year, month in itertools.product(['HSIF', 'HHIF'], range(7, 19), range(1, 13))
                      if not (year == 7 and month == 1)]

    # 获取合约基本信息
    # w.wss("AU1706.SHF,AG1612.SHF,AU0806.SHF", "ipo_date,sec_name,sec_englishname,exch_eng,lasttrade_date,lastdelivery_date,dlmonth,lprice,sccode,margin,punit,changelt,mfprice,contractmultiplier,ftmargins,trade_code")
    # future_info_df = wss_cache(w, wind_code_list,
    #                            "ipo_date,sec_name,sec_englishname,exch_eng,lasttrade_date,lastdelivery_date,dlmonth,lprice,sccode,margin,punit,changelt,mfprice,contractmultiplier,ftmargins,trade_code")
    if len(wind_code_list) > 0:
        future_info_df = invoker.wss(wind_code_list, wind_indictor_str)
        future_info_df['MFPRICE'] = future_info_df['MFPRICE'].apply(mfprice_2_num)
        future_info_df.rename(columns={c: str.lower(c) for c in future_info_df.columns}, inplace=True)
        future_info_df.index.rename('wind_code', inplace=True)
        future_info_df = future_info_df[~(future_info_df['ipo_date'].isna() | future_info_df['lasttrade_date'].isna())]
        future_info_df.reset_index(inplace=True)
        future_info_count = future_info_df.shape[0]
        bunch_insert_on_duplicate_update(future_info_df, table_name, engine_md, dtype=dtype)
        logger.info("更新 wind_future_info_hk 结束 %d 条记录被更新", future_info_count) 

def merge_index_info():
    """
    合并 wind，ifind 数据到对应名称的表中
    :return:
    """
    table_name = 'index_info'
    logging.info("更新 %s 开始", table_name)
    has_table = engine_md.has_table(table_name)
    ifind_table_name = 'ifind_{table_name}'.format(table_name=table_name)
    wind_table_name = 'wind_{table_name}'.format(table_name=table_name)
    # ifind_model = TABLE_MODEL_DIC[ifind_table_name]
    # wind_model = TABLE_MODEL_DIC[wind_table_name]
    # with with_db_session(engine_md) as session:
    #     session.query(ifind_model, wind_model).filter(ifind_model.c.ths_code == wind_model.c.wind_code)
    ifind_sql_str = "select * from {table_name}".format(table_name=ifind_table_name)
    wind_sql_str = "select * from {table_name}".format(table_name=wind_table_name)
    ifind_df = pd.read_sql(ifind_sql_str, engine_md)  # , index_col='ths_code'
    wind_df = pd.read_sql(wind_sql_str, engine_md)  # , index_col='wind_code'
    joined_df = pd.merge(ifind_df, wind_df, how='outer',
                         left_on='ths_code', right_on='wind_code', indicator='indicator_column')
    col_merge_dic = {
        'unique_code': (String(20), prefer_left, {'left_key': 'ths_code', 'right_key': 'wind_code'}),
        'sec_name': (String(20), prefer_left, {'left_key': 'ths_index_short_name_index', 'right_key': 'sec_name'}),
        'crm_issuer': (String(20), prefer_left, {'left_key': 'ths_publish_org_index', 'right_key': 'crm_issuer'}),
        'base_date': (
            Date, prefer_left, {'left_key': 'ths_index_base_period_index', 'right_key': 'basedate'}),
        'basevalue': (DOUBLE, prefer_left, {'left_key': 'ths_index_base_point_index', 'right_key': 'basevalue'}),
        'country': (String(20), get_value, {'key': 'country'}),
        'launchdate': (Date, get_value, {'key': 'launchdate'}),
        'index_code': (String(20), get_value, {'key': 'ths_index_code_index'}),
        'index_category': (String(10), get_value, {'key': 'ths_index_category_index'}),

    }
    col_merge_rule_dic = {
        key: (val[1], val[2]) for key, val in col_merge_dic.items()
    }
    dtype = {
        key: val[0] for key, val in col_merge_dic.items()
    }
    data_df = merge_data(joined_df, col_merge_rule_dic)
    data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, dtype)
    logger.info('%s 新增或更新记录 %d 条', table_name, data_count)
    if not has_table and engine_md.has_table(table_name):
        alter_table_2_myisam(engine_md, [table_name])
        # build_primary_key([table_name])
        create_pk_str = """ALTER TABLE {table_name}
                        CHANGE COLUMN `unique_code` `unique_code` VARCHAR(20) NOT NULL ,
                        ADD PRIMARY KEY (`unique_code`)""".format(table_name=table_name)
        with with_db_session(engine_md) as session:
            session.execute(create_pk_str)

    return data_df 

def import_tushare_stock_fund_holdings():
    table_name = 'tushare_stock_fund_holdings'
    logging.info("更新 %s 开始", table_name)
    has_table = engine_md.has_table(table_name)
    tushare_fund_holdings_indicator_param_list = [
        ('ts_code', String(20)),
        ('sec_name', String(20)),
        ('end_date', Date),
        ('nums', DOUBLE),
        ('nlast', DOUBLE),
        ('count', DOUBLE),
        ('clast', DOUBLE),
        ('amount', DOUBLE),
        ('ratio', DOUBLE),
    ]
    tushare_fund_holdings_dtype = {key: val for key, val in tushare_fund_holdings_indicator_param_list}
    data_df_list, data_count, all_data_count, = [], 0, 0
    years = list(range(2013, 2019))
    try:
        for year in years:
            for quarter in list([1, 2, 3, 4]):
                print((year, quarter))
                data_df = invoke_fund_holdings(year, quarter)
                ts_code_list = []
                for i in data_df.code:
                    if i[0] == '6':
                        sh = i + '.SH'
                        ts_code_list.append(sh)
                    else:
                        sz = i + '.SZ'
                        ts_code_list.append(sz)
                data_df.code = ts_code_list
                data_df = data_df.rename(columns={'code': 'ts_code', 'name': 'sec_name', 'date': 'end_date'})
                # 把数据攒起来
                if data_df is not None and data_df.shape[0] > 0:
                    data_count += data_df.shape[0]
                    data_df_list.append(data_df)
                # 大于阀值有开始插入
                if data_count >= 50:
                    data_df_all = pd.concat(data_df_list)
                    bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, tushare_fund_holdings_dtype)
                    all_data_count += data_count
                    data_df_list, data_count = [], 0
    finally:
        if len(data_df_list) > 0:
            data_df_all = pd.concat(data_df_list)
            data_count = bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md,
                                                          tushare_fund_holdings_dtype)
            all_data_count = all_data_count + data_count
            logging.info("更新 %s 结束 %d 条信息被更新", table_name, all_data_count)
            if not has_table and engine_md.has_table(table_name):
                alter_table_2_myisam(engine_md, [table_name])
                build_primary_key([table_name]) 

def update_df_2_db(instrument_type, table_name, data_df):
    """将 DataFrame 数据保存到 数据库对应的表中"""
    dtype = {
        'trade_date': Date,
        'Contract': String(20),
        'ContractNext': String(20),
        'Close': DOUBLE,
        'CloseNext': DOUBLE,
        'Volume': DOUBLE,
        'VolumeNext': DOUBLE,
        'OI': DOUBLE,
        'OINext': DOUBLE,
        'Open': DOUBLE,
        'OpenNext': DOUBLE,
        'High': DOUBLE,
        'HighNext': DOUBLE,
        'Low': DOUBLE,
        'LowNext': DOUBLE,
        'Amount': DOUBLE,
        'AmountNext': DOUBLE,
        'adj_factor_main': DOUBLE,
        'adj_factor_secondary': DOUBLE,
        'instrument_type': String(20),
    }
    # 为了解决 AttributeError: 'numpy.float64' object has no attribute 'translate' 错误，需要将数据类型转换成 float
    data_df["Close"] = data_df["Close"].apply(str_2_float)
    data_df["CloseNext"] = data_df["CloseNext"].apply(str_2_float)
    data_df["Volume"] = data_df["Volume"].apply(str_2_float)
    data_df["VolumeNext"] = data_df["VolumeNext"].apply(str_2_float)
    data_df["OI"] = data_df["OI"].apply(str_2_float)
    data_df["OINext"] = data_df["OINext"].apply(str_2_float)
    data_df["Open"] = data_df["Open"].apply(str_2_float)
    data_df["OpenNext"] = data_df["OpenNext"].apply(str_2_float)
    data_df["High"] = data_df["High"].apply(str_2_float)
    data_df["HighNext"] = data_df["HighNext"].apply(str_2_float)
    data_df["Low"] = data_df["Low"].apply(str_2_float)
    data_df["LowNext"] = data_df["LowNext"].apply(str_2_float)
    data_df["Amount"] = data_df["Amount"].apply(str_2_float)
    data_df["AmountNext"] = data_df["AmountNext"].apply(str_2_float)
    data_df["adj_factor_main"] = data_df["adj_factor_main"].apply(str_2_float)
    data_df["adj_factor_secondary"] = data_df["adj_factor_secondary"].apply(str_2_float)
    # 清理历史记录
    with with_db_session(engine_md) as session:
        sql_str = """SELECT table_name FROM information_schema.TABLES 
            WHERE table_name = :table_name and TABLE_SCHEMA=(select database())"""
        # 复权数据表
        is_existed = session.execute(sql_str, params={"table_name": table_name}).fetchone()
        if is_existed is not None:
            session.execute("delete from %s where instrument_type = :instrument_type" % table_name,
                            params={"instrument_type": instrument_type})
            logger.debug("删除 %s 中的 %s 历史数据", table_name, instrument_type)

    # 插入数据库
    bunch_insert(data_df, table_name=table_name, dtype=dtype, primary_keys=['trade_date', 'Contract']) 

def import_trade_date(chain_param=None):
    """
    增量导入交易日数据导数据库表 wind_trade_date，默认导入未来300天的交易日数据
    2018-01-17 增加港股交易日数据，眼前考虑对减少对已有代码的冲击，新建一张 wind_trade_date_hk表
    日后将会考虑将两张表进行合并
    :return:
    """
    table_name = TABLE_NAME
    exch_code_trade_date_dic = {}
    has_table = engine_md.has_table(table_name)
    if has_table:
        with with_db_session(engine_md) as session:
            try:
                table = session.execute('SELECT exchange,max(cal_date) FROM {table_name} GROUP BY exchange'.format(
                    table_name=table_name
                ))
                exch_code_trade_date_dic = {exch_code: trade_date for exch_code, trade_date in table.fetchall()}
            except Exception as exp:
                logger.exception("交易日获取异常")

    exchange_code_dict = {
        "HKEX": "香港联合交易所",
        "SZSE": "深圳证券交易所",
        "SSE": "上海证券交易所",
    }
    exchange_code_list = list(exchange_code_dict.keys())
    for exchange_code in exchange_code_list:
        if exchange_code in exch_code_trade_date_dic:
            trade_date_max = exch_code_trade_date_dic[exchange_code]
            start_date_str = (trade_date_max + timedelta(days=1)).strftime(STR_FORMAT_DATE_TS)
        else:
            start_date_str = '19900101'

        end_date_str = (date.today() + timedelta(days=310)).strftime(STR_FORMAT_DATE_TS)
        trade_date_df = pro.trade_cal(exchange_id='', start_date=start_date_str, end_date=end_date_str)
        if trade_date_df is None or trade_date_df.shape[0] == 0:
            logger.warning('%s[%s] [%s - %s] 没有查询到交易日期',
                           exchange_code_dict[exchange_code], exchange_code, start_date_str, end_date_str)
            continue
        date_count = trade_date_df.shape[0]
        logger.info("%s[%s] %d 条交易日数据将被导入 %s",
                    exchange_code_dict[exchange_code], exchange_code, date_count, table_name)
        date_count = bunch_insert_on_duplicate_update(trade_date_df, table_name, engine_md, dtype={
            'exchange': String(10),
            'cal_date': Date,
            'is_open': DOUBLE,
        }, myisam_if_create_table=True, primary_keys=['exchange', 'cal_date'], schema=config.DB_SCHEMA_MD)
        logger.info('%s[%s] %d 条交易日数据导入 %s 完成',
                    exchange_code_dict[exchange_code], exchange_code, date_count, table_name) 

def import_tushare_block_trade(chain_param=None):
    """
    插入股票日线数据到最近一个工作日-1。
    如果超过 BASE_LINE_HOUR 时间，则获取当日的数据
    :return:
    """
    table_name = 'tushare_block_trade'
    logging.info("更新 %s 开始", table_name)
    param_list = [
        ('trade_date', Date),
        ('ts_code', String(20)),
        ('price', DOUBLE),
        ('vol', DOUBLE),
        ('amount', DOUBLE),
        ('buyer', String(100)),
        ('seller', String(100)),
    ]

    has_table = engine_md.has_table(table_name)
    # 进行表格判断，确定是否含有 table_name

    if has_table:
        sql_str = f"""select cal_date            
                 FROM
                  (
                   select * from tushare_trade_date trddate 
                   where( cal_date>(SELECT max(trade_date) FROM  {table_name}))
                 )tt
                 where (is_open=1 
                        and cal_date <= if(hour(now())<16, subdate(curdate(),1), curdate()) 
                        and exchange='SSE') """
    else:
        # 2003-08-02 大宗交易制度开始实施
        sql_str = """SELECT cal_date FROM tushare_trade_date trddate WHERE (trddate.is_open=1 
                  AND cal_date <= if(hour(now())<16, subdate(curdate(),1), curdate()) 
                  AND exchange='SSE'  AND cal_date>='2003-08-02') ORDER BY cal_date"""
        logger.warning('%s 不存在，仅使用 tushare_trade_date 表进行计算日期范围', table_name)

    with with_db_session(engine_md) as session:
        # 获取交易日数据
        table = session.execute(sql_str)
        trade_date_list = list(row[0] for row in table.fetchall())
    # 设置 dtype
    dtype = {key: val for key, val in param_list}

    try:
        trade_date_list_len = len(trade_date_list)
        for num, trade_date in enumerate(trade_date_list, start=1):
            trade_date = datetime_2_str(trade_date, STR_FORMAT_DATE_TS)
            data_df = invoke_block_trade(trade_date=trade_date)
            if len(data_df) > 0:
                # 当前表不设置主键，由于存在重复记录，因此无法设置主键
                # 例如：002325.SZ 2014-11-17 华泰证券股份有限公司沈阳光荣街证券营业部 两笔完全相同的大宗交易
                data_count = bunch_insert(
                    data_df, table_name=table_name, dtype=dtype)
                logging.info("%d/%d) %s更新 %s 结束 %d 条信息被更新",
                             num, trade_date_list_len, trade_date, table_name, data_count)
            else:
                logging.info("%d/%d) %s 无数据信息可被更新", num, trade_date_list_len, trade_date)
    except:
        logger.exception('更新 %s 表异常', table_name) 

def import_index_info(chain_param=None, ths_code=None):
    """
    导入 info 表
    :param chain_param: 该参数仅用于 task.chain 串行操作时，上下传递参数使用
    :param ths_code:
    :param refresh:
    :return:
    """
    table_name = 'ifind_index_info'
    has_table = engine_md.has_table(table_name)
    logging.info("更新 ifind_index_info 开始")
    if ths_code is None:
        # 获取全市场股票代码及名称
        date_end = date.today()
        stock_code_set = set()
        stock_code_set_sub = get_stock_code_set(date_end)
        if stock_code_set_sub is not None:
            stock_code_set |= stock_code_set_sub

        ths_code = ','.join(stock_code_set)

    indicator_param_list = [
        ('ths_index_short_name_index', '', String(20)),
        ('ths_index_code_index', '', String(10)),
        ('ths_index_category_index', '', String(20)),
        ('ths_index_base_period_index', '', Date),
        ('ths_index_base_point_index', '', DOUBLE),
        ('ths_publish_org_index', '', String(20)),
    ]
    # indicator' = 'ths_index_short_name_index;ths_index_code_index;ths_thscode_index;ths_index_category_index;
    # ths_index_base_period_index;ths_index_base_point_index;ths_publish_org_index',
    # param = ';;;;;;'
    indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';')
    data_df = invoker.THS_BasicData(ths_code, indicator, param)
    if data_df is None or data_df.shape[0] == 0:
        logging.info("没有可用的 index info 可以更新")
        return

    dtype = {key: val for key, _, val in indicator_param_list}
    dtype['ths_code'] = String(20)
    data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, dtype)
    logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)
    if not has_table and engine_md.has_table(table_name):
        alter_table_2_myisam(engine_md, [table_name])
        build_primary_key([table_name])

    # 更新 code_mapping 表
    update_from_info_table(table_name)