def main():
storeservice = MysqlService()
kline_5m_tables_number = 35
schemaArr = [
{
"table": "ft_5M_{0}",
"dtype": {
"id": sa.types.BIGINT,
"code": sa.types.NVARCHAR(20),
"time_key": sa.types.DATETIME,
"open": sa.types.FLOAT,
"close": sa.types.FLOAT,
"high": sa.types.FLOAT,
"low": sa.types.FLOAT,
"pe_ratio": sa.types.FLOAT,
"turnover_rate": sa.types.FLOAT,
"volume": sa.types.BIGINT,
"turnover": sa.types.FLOAT,
"change_rate": sa.types.FLOAT,
"last_close": sa.types.FLOAT
},
"clauses": [
'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
'ALTER TABLE `{0}` ADD INDEX (`code`);',
'ALTER TABLE `{0}` ADD INDEX (`time_key`);',
'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\'',
'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';',
'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';',
'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';',
'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';',
'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';',
'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';',
'ALTER TABLE `{0}` ENGINE=MyISAM;'
]
},
]
try:
logging.info("create sub kline 5m schema, starting")
for index in range(1,kline_5m_tables_number,1):
for schema in schemaArr:
df = pd.DataFrame(None, columns=schema['dtype'].keys())
table = schema['table'].format(index)
logging.info(table)
logging.info('table:{0}'.format(table))
clauses = []
for clause in schema['clauses']:
clause = clause.format(table)
clauses.append(clause)
storeservice.init_schema(table, df, schema['dtype'], clauses)
logging.info("create sub kline 5m, end")
except IOError as err:
logging.error("OS|error: {0}".format(err))
else:
logging.info('create sub kline success')
union_table = [('ft_5M_{0}'.format(table)) for table in range(1, kline_5m_tables_number, 1)]
mrg_kline_claus = 'ALTER TABLE `{0}` ENGINE = MRG_MyISAM UNION = ({1}) INSERT_METHOD = LAST;'.format({0}, ','.join(union_table))
schemaArr = [
{
"table": "ft_5m",
"dtype": {
"id": sa.types.BIGINT,
"code": sa.types.NVARCHAR(20),
"time_key": sa.types.DATETIME,
"open": sa.types.FLOAT,
"close": sa.types.FLOAT,
"high": sa.types.FLOAT,
"low": sa.types.FLOAT,
"pe_ratio": sa.types.FLOAT,
"turnover_rate": sa.types.FLOAT,
"volume": sa.types.BIGINT,
"turnover": sa.types.FLOAT,
"change_rate": sa.types.FLOAT,
"last_close": sa.types.FLOAT
},
"clauses": [
'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
'ALTER TABLE `{0}` ADD INDEX (`code`);',
'ALTER TABLE `{0}` ADD INDEX (`time_key`);',
'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\'',
'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';',
'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';',
'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';',
'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';',
'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';',
'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';',
mrg_kline_claus
]
}
]
try:
logging.info("create kline 5m schema, starting")
for schema in schemaArr:
df = pd.DataFrame(None, columns=schema['dtype'].keys())
table = schema['table']
logging.info(table)
logging.info('table:{0}'.format(table))
clauses = []
for clause in schema['clauses']:
clause = clause.format(table)
clauses.append(clause)
storeservice.init_schema(table, df, schema['dtype'], clauses)
logging.info("create kline 5m, end")
except IOError as err:
logging.error("OS|error: {0}".format(err))
else:
logging.info('create kline 5m success')
# -*- coding: UTF-8 -*-
def main():
storeservice = MysqlService()
schemaArr = [
# {
# "table": "ft_stat_week_probability",
# "dtype": {
# "id":sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "up_count": sa.types.SMALLINT,
# "down_count": sa.types.SMALLINT,
# "up_probability": sa.types.Float,
# "down_probability": sa.types.Float,
# "week_of_day": sa.types.SMALLINT,
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`)',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`week_of_day`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# ]
# },
# {
# "table": "ft_stock_basicinfo",
# "dtype": {
# "id":sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "name": sa.types.NVARCHAR(200),
# "lot_size": sa.types.BIGINT,
# "stock_type": sa.types.NVARCHAR(30),
# "stock_child_type": sa.types.NVARCHAR(30),
# "stock_owner": sa.types.NVARCHAR(30),
# "listing_date": sa.types.DATE,
# "stock_id": sa.types.BIGINT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`)',
# #code not unique,
# 'ALTER TABLE `{0}` ADD KEY (`name`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`stock_type`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN lot_size BIGINT COMMENT \'每手数量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_type VARCHAR(30) COMMENT \'股票类型，参见SecurityType\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_child_type VARCHAR(30) COMMENT \'涡轮子类型，参见WrtType\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_owner VARCHAR(30) COMMENT \'正股代码\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN listing_date DATE COMMENT \'上市时间\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_id BIGINT COMMENT \'股票id\';'
# ]
# },
# {
# "table": "ft_history_kline",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time_key": sa.types.DATETIME,
# "open": sa.types.FLOAT,
# "close": sa.types.FLOAT,
# "high": sa.types.FLOAT,
# "low": sa.types.FLOAT,
# "pe_ratio": sa.types.FLOAT,
# "turnover_rate": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "change_rate": sa.types.FLOAT,
# "last_close": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time_key`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';'
# #'ALTER TABLE `{0}` ENGINE=MRG_MyISAM UNION=(ft_history_kline_1,ft_history_kline_2,ft_history_kline_3,ft_history_kline_4,ft_history_kline_5,ft_history_kline_6,ft_history_kline_7,ft_history_kline_8,ft_history_kline_9,ft_history_kline_10,ft_history_kline_11) INSERT_METHOD=LAST;'
# 'ALTER TABLE `{0}` ENGINE=MyISAM;'
# ]
# },
# {
# "table": "ft_history_kline_K_5M",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time_key": sa.types.DATETIME,
# "open": sa.types.FLOAT,
# "close": sa.types.FLOAT,
# "high": sa.types.FLOAT,
# "low": sa.types.FLOAT,
# "pe_ratio": sa.types.FLOAT,
# "turnover_rate": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "change_rate": sa.types.FLOAT,
# "last_close": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time_key`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';'
# #'ALTER TABLE `{0}` ENGINE=MRG_MyISAM UNION=(ft_history_kline_K_5M_1,ft_history_kline_K_5M_2,ft_history_kline_K_5M_3,ft_history_kline_K_5M_4,ft_history_kline_K_5M_5,ft_history_kline_K_5M_6,ft_history_kline_K_5M_7,ft_history_kline_K_5M_8,ft_history_kline_K_5M_9,ft_history_kline_K_5M_10,ft_history_kline_K_5M_11) INSERT_METHOD=LAST;'
# 'ALTER TABLE `{0}` ENGINE=MyISAM;'
# ]
# },
# {
# "table": "ft_history_kline_MyISAM",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time_key": sa.types.DATETIME,
# "open": sa.types.FLOAT,
# "close": sa.types.FLOAT,
# "high": sa.types.FLOAT,
# "low": sa.types.FLOAT,
# "pe_ratio": sa.types.FLOAT,
# "turnover_rate": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "change_rate": sa.types.FLOAT,
# "last_close": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time_key`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';'
# 'ALTER TABLE `{0}` ENGINE=MyISAM;'
# ]
# },
# {
# "table": "ft_history_kline_K_5M_MyISAM",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time_key": sa.types.DATETIME,
# "open": sa.types.FLOAT,
# "close": sa.types.FLOAT,
# "high": sa.types.FLOAT,
# "low": sa.types.FLOAT,
# "pe_ratio": sa.types.FLOAT,
# "turnover_rate": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "change_rate": sa.types.FLOAT,
# "last_close": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time_key`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN change_rate FLOAT COMMENT \'涨跌幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨收价\';'
# 'ALTER TABLE `{0}` ENGINE=MyISAM;'
# ]
# },
# {
# "table": "ft_broker",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "bid_broker_id": sa.types.BIGINT,
# "bid_broker_name": sa.types.NVARCHAR(100),
# "bid_broker_pos": sa.types.BIGINT,
# "ask_broker_id": sa.types.BIGINT,
# "ask_broker_name": sa.types.NVARCHAR(100),
# "ask_broker_pos": sa.types.BIGINT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# ]
# },
# {
# "table": "ft_rtdata",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time": sa.types.DATETIME,
# "is_blank": sa.types.INT,
# "opened_mins": sa.types.INT,
# "cur_price": sa.types.FLOAT,
# "last_close": sa.types.FLOAT,
# "avg_price": sa.types.FLOAT,
# "turnover": sa.types.FLOAT,
# "volume": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN opened_mins INT COMMENT \'零点到当前多少分钟\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN cur_price FLOAT COMMENT \'当前价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_close FLOAT COMMENT \'昨天收盘的价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN avg_price FLOAT COMMENT \'平均价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume FLOAT COMMENT \'成交量\';'
# ]
# },
# {
# "table": "ft_stockquote",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "data_date": sa.types.DATE,
# "data_time": sa.types.TIME,
# "last_price": sa.types.FLOAT,
# "open_price": sa.types.FLOAT,
# "high_price": sa.types.FLOAT,
# "low_price": sa.types.FLOAT,
# "prev_close_price": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "turnover_rate": sa.types.FLOAT,
# "amplitude": sa.types.INT,
# "suspension": sa.types.BOOLEAN,
# "listing_date": sa.types.DATE,
# "price_spread": sa.types.FLOAT,
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`data_date`);'
# 'ALTER TABLE `{0}` ADD INDEX (`data_time`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交金额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN amplitude INT COMMENT \'振幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN suspension BOOL COMMENT \'是否停牌(True表示停牌)\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN listing_date DATE COMMENT \'上市日期\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN price_spread FLOAT COMMENT \'当前价差，亦即摆盘数据的买档或卖档的相邻档位的报价差\';'
# ]
# },
# {
# "table": "ft_ticker",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "time": sa.types.DATETIME,
# "price": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.FLOAT,
# "ticker_direction": sa.types.VARCHAR(20),
# "sequence": sa.types.VARCHAR(50),
# "recv_time": sa.types.DATETIME,
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`time`);'
# 'ALTER TABLE `{0}` ADD INDEX (`sequence`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交金额\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交数量(股数)\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN ticker_direction VARCHAR(20) COMMENT \'逐笔方向\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN sequence VARCHAR(50) COMMENT \'逐笔序号\';',
# ]
# },
# {
# "table": "ft_tradeorder",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "stock_name": sa.types.NVARCHAR(100),
# "dealt_avg_price": sa.types.FLOAT,
# "dealt_qty": sa.types.FLOAT,
# "qty": sa.types.FLOAT,
# "order_id": sa.types.NVARCHAR(50),
# "order_type": sa.types.NVARCHAR(20),
# "price": sa.types.FLOAT,
# "order_status": sa.types.NVARCHAR(20),
# "create_time": sa.types.DATETIME,
# "updated_time": sa.types.DATETIME,
# "trd_side": sa.types.NVARCHAR(20),
# "last_err_msg": sa.types.NVARCHAR(1000),
# "trd_market": sa.types.NVARCHAR(30),
# "trd_env": sa.types.NVARCHAR(30),
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`stock_name`);',
# 'ALTER TABLE `{0}` ADD INDEX (`create_time`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN dealt_avg_price FLOAT COMMENT \'成交均价，无精度限制\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN dealt_qty FLOAT COMMENT \'成交数量，2位精度，期权单位是”张”\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN qty FLOAT COMMENT \'订单数量，2位精度，期权单位是”张”\';',
# ]
# },
# {
# "table": "ft_tradedetail",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "stock_name": sa.types.NVARCHAR(100),
# "trd_env": sa.types.NVARCHAR(20),
# "deal_id": sa.types.NVARCHAR(50),
# "order_id": sa.types.NVARCHAR(50),
# "qty": sa.types.FLOAT,
# "price": sa.types.FLOAT,
# "trd_side": sa.types.NVARCHAR(20),
# "create_time": sa.types.DATETIME,
# "counter_broker_id": sa.types.INT,
# "counter_broker_name": sa.types.NVARCHAR(100),
# "trd_market": sa.types.NVARCHAR(50),
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`stock_name`);'
# 'ALTER TABLE `{0}` ADD INDEX (`create_time`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN trd_env VARCHAR(20) COMMENT \'交易环境\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN deal_id VARCHAR(50) COMMENT \'成交号\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN order_id VARCHAR(50) COMMENT \'订单号\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN qty FLOAT COMMENT \'成交数量，2位精度，期权单位是”张”\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN price FLOAT COMMENT \'成交价格，3位精度(A股2位)\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN trd_side VARCHAR(20) COMMENT \'交易方向\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN counter_broker_id INT COMMENT \'对手经纪号，港股有效\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN counter_broker_name VARCHAR(100) COMMENT \'对手经纪名称，港股有效\';',
# 'ALTER TABLE `{0}` MODIFY COLUMN trd_market VARCHAR(50) COMMENT \'交易市场\';',
# ]
# },
# {
# "table": "ft_autype",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "ex_div_date": sa.types.DATE,
# "split_ratio": sa.types.FLOAT,
# "per_cash_div": sa.types.FLOAT,
# "per_share_div_ratio": sa.types.FLOAT,
# "per_share_trans_ratio": sa.types.FLOAT,
# "allotment_ratio": sa.types.FLOAT,
# "allotment_price": sa.types.FLOAT,
# "stk_spo_ratio": sa.types.FLOAT,
# "stk_spo_price": sa.types.FLOAT,
# "forward_adj_factorA": sa.types.FLOAT,
# "forward_adj_factorB": sa.types.FLOAT,
# "backward_adj_factorA": sa.types.FLOAT,
# "backward_adj_factorB": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`ex_div_date`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN ex_div_date DATE COMMENT \'除权除息日\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN split_ratio FLOAT COMMENT \'拆合股比例； double，例如，对于5股合1股为1/5，对于1股拆5股为5/1\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN per_cash_div FLOAT COMMENT \'每股派现\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN per_share_div_ratio FLOAT COMMENT \'每股送股比例\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN per_share_trans_ratio FLOAT COMMENT \'每股转增股比例\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN allotment_ratio FLOAT COMMENT \'每股配股比例\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN allotment_price FLOAT COMMENT \'配股价\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stk_spo_ratio FLOAT COMMENT \'增发比例\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stk_spo_price FLOAT COMMENT \'增发价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN forward_adj_factorA FLOAT COMMENT \'前复权因子A\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN forward_adj_factorB FLOAT COMMENT \'前复权因子B\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN backward_adj_factorA FLOAT COMMENT \'后复权因子A\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN backward_adj_factorB FLOAT COMMENT \'后复权因子B\';'
# ]
# },
# {
# "table": "ft_market_snapshot",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "update_time": sa.types.DATETIME,
# "last_price": sa.types.FLOAT,
# "open_price": sa.types.FLOAT,
# "high_price": sa.types.FLOAT,
# "low_price": sa.types.FLOAT,
# "prev_close_price": sa.types.FLOAT,
# "volume": sa.types.BIGINT,
# "turnover": sa.types.BIGINT,
# "turnover_rate": sa.types.FLOAT,
# "suspension": sa.types.INT,
# "listing_date": sa.types.DATE,
# "circular_market_val": sa.types.FLOAT,
# "low_price": sa.types.FLOAT,
# "total_market_val": sa.types.FLOAT,
# "wrt_valid": sa.types.INT,
# "wrt_conversion_ratio": sa.types.FLOAT,
# "wrt_type": sa.types.NVARCHAR(20),
# "wrt_strike_price": sa.types.FLOAT,
# "wrt_maturity_date": sa.types.DATE,
# "wrt_end_trade": sa.types.DATE,
# "wrt_code": sa.types.NVARCHAR(50),
# "wrt_recovery_price": sa.types.FLOAT,
# "wrt_street_vol": sa.types.FLOAT,
# "wrt_issue_vol": sa.types.FLOAT,
# "wrt_street_ratio": sa.types.FLOAT,
# "wrt_delta": sa.types.FLOAT,
# "wrt_implied_volatility": sa.types.FLOAT,
# "wrt_premium": sa.types.FLOAT,
# "lot_size": sa.types.BIGINT,
# "issued_Shares": sa.types.BIGINT,
# "net_asset": sa.types.FLOAT,
# "net_profit": sa.types.FLOAT,
# "earning_per_share": sa.types.FLOAT,
# "outstanding_shares": sa.types.BIGINT,
# "net_asset_per_share": sa.types.FLOAT,
# "ey_ratio": sa.types.FLOAT,
# "pe_ratio": sa.types.FLOAT,
# "pb_ratio": sa.types.FLOAT,
# "price_spread": sa.types.FLOAT
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`update_time`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN last_price FLOAT COMMENT \'最新价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN open_price FLOAT COMMENT \'今日开盘价\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN high_price FLOAT COMMENT \'最高价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN low_price FLOAT COMMENT \'最低价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN prev_close_price FLOAT COMMENT \'昨收盘价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN volume BIGINT COMMENT \'成交数量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover FLOAT COMMENT \'成交金额\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN turnover_rate FLOAT COMMENT \'换手率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN suspension INT COMMENT \'是否停牌(True表示停牌)\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN listing_date DATE COMMENT \'上市日期 (yyyy-MM-dd)\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN circular_market_val FLOAT COMMENT \'流通市值\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN total_market_val FLOAT COMMENT \'总市值\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_valid INT COMMENT \'是否是窝轮\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_conversion_ratio FLOAT COMMENT \'换股比率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_type VARCHAR(20) COMMENT \'窝轮类型；1=认购证 2=认沽证 3=牛证 4=熊证 \';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_strike_price FLOAT COMMENT \'行使价格\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_maturity_date DATE COMMENT \'格式化窝轮到期时间\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_end_trade DATE COMMENT \'格式化窝轮最后交易时间\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_code VARCHAR(50) COMMENT \'窝轮对应的正股\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_recovery_price FLOAT COMMENT \'窝轮回收价\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_street_vol FLOAT COMMENT \'窝轮街货量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_issue_vol FLOAT COMMENT \'窝轮发行量\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_street_ratio FLOAT COMMENT \'窝轮街货占比\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_delta FLOAT COMMENT \'窝轮对冲值\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_implied_volatility FLOAT COMMENT \'窝轮引伸波幅\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN wrt_premium FLOAT COMMENT \'窝轮溢价\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN lot_size BIGINT COMMENT \'每手股数\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN issued_Shares BIGINT COMMENT \'发行股本\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN net_asset FLOAT COMMENT \'资产净值\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN net_profit FLOAT COMMENT \'净利润\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN earning_per_share FLOAT COMMENT \'每股盈利\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN outstanding_shares BIGINT COMMENT \'流通股本\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN net_asset_per_share FLOAT COMMENT \'每股净资产\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN ey_ratio FLOAT COMMENT \'收益率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN pe_ratio FLOAT COMMENT \'市盈率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN pb_ratio FLOAT COMMENT \'市净率\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN price_spread FLOAT COMMENT \'当前摆盘价差亦即摆盘数据的买档或卖档的相邻档位的报价差\';'
# ]
# },
# {
# "table": "ft_plate_list",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "plate_name": sa.types.NVARCHAR(50),
# "plate_id": sa.types.NVARCHAR(20),
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# # code aren't unique
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`plate_name`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN code VARCHAR(20) COMMENT \'股票代码\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN plate_name VARCHAR(50) COMMENT \'板块名字\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN plate_id VARCHAR(20) COMMENT \'板块id\';'
# ]
# },
# {
# "table": "ft_plate_stock",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "lot_size": sa.types.BIGINT,
# "stock_name": sa.types.NVARCHAR(100),
# "stock_owner": sa.types.NVARCHAR(100),
# "stock_child_type": sa.types.NVARCHAR(20),
# "stock_type": sa.types.NVARCHAR(20),
# "list_time": sa.types.DATE,
# "stock_id": sa.types.BIGINT,
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` ADD INDEX (`stock_name`);'
# 'ALTER TABLE `{0}` ADD INDEX (`stock_child_type`);'
# 'ALTER TABLE `{0}` ADD INDEX (`stock_type`);'
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN code VARCHAR(20) COMMENT \'股票代码\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN lot_size BIGINT COMMENT \'每手股数\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_name VARCHAR(100) COMMENT \'股票名称\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_owner VARCHAR(100) COMMENT \'所属正股的代码\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_child_type VARCHAR(100) COMMENT \'股票子类型，参见WrtType\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_type VARCHAR(20) COMMENT \'股票类型，参见SecurityType\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN list_time DATE COMMENT \'上市时间\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN stock_id BIGINT COMMENT \'股票id\';'
# ]
# },
# {
# "table": "sys_sharding",
# "dtype": {
# "id": sa.types.BIGINT,
# "code": sa.types.NVARCHAR(20),
# "dtype": sa.types.Enum('hk','hk_5m','hk_1m'),
# "tindex": sa.types.SMALLINT,
# "lastdate": sa.types.DATE,
# },
# "clauses": [
# 'ALTER TABLE `{0}` ADD PRIMARY KEY (`id`);',
# 'ALTER TABLE `{0}` ADD INDEX (`code`);',
# 'ALTER TABLE `{0}` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT COMMENT \'id\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN code VARCHAR(20) COMMENT \'股票代码\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN tindex SMALLINT COMMENT \'表索引\';'
# 'ALTER TABLE `{0}` MODIFY COLUMN lastdate SMALLINT COMMENT \'最后更新时间\';'
# 'ALTER TABLE `{0}` ENGINE = InnoDB;'
# ]
# },
]
try:
logging.info("int_schema, starting")
for schema in schemaArr:
df = pd.DataFrame(None,columns=schema['dtype'].keys())
table = schema['table']
logging.info('table:{0}'.format(table))
clauses = []
for clause in schema['clauses']:
clause = clause.format(table)
clauses.append(clause)
storeservice.init_schema(table,df,schema['dtype'],clauses)
logging.info("int_schema, end")
except IOError as err:
logging.error("OS|error: {0}".format(err))
else:
logging.info('init schema success')