def update_server_instrument_job(server_name_tuple, symbol_type=None):
server_host = server_constant.get_server_model('host')
common_session = server_host.get_db_session('common')
query = common_session.query(Instrument)
today = date_utils.get_today_str(format_str='%Y-%m-%d')
obj_list = []
if not symbol_type:
for future_db in query.filter(Instrument.del_flag == 0):
obj_list.append(future_db)
elif symbol_type == const.INSTRUMENT_TYPE_ENUMS.Future:
for future_db in query.filter(Instrument.del_flag == 0).filter(
Instrument.exchange_id.in_([const.EXCHANGE_TYPE_ENUMS.SHF, const.EXCHANGE_TYPE_ENUMS.DCE,
const.EXCHANGE_TYPE_ENUMS.ZCE, const.EXCHANGE_TYPE_ENUMS.CFF,
const.EXCHANGE_TYPE_ENUMS.INE])):
obj_list.append(future_db)
else:
for future_db in query.filter(and_(Instrument.del_flag == 0, Instrument.exchange_id.in_(
[const.EXCHANGE_TYPE_ENUMS.ANY, const.EXCHANGE_TYPE_ENUMS.HK, const.EXCHANGE_TYPE_ENUMS.CG,
const.EXCHANGE_TYPE_ENUMS.CS, const.EXCHANGE_TYPE_ENUMS.FX]))):
obj_list.append(future_db)
daily_instrument_obj_list = to_many_sql(Instrument, obj_list, 'common.instrument')
file_name = 'INSTRUMENT_' + today + '.pickle'
for ys_file in os.listdir(UPDATE_PRICE_PICKLE):
os.remove(os.path.join(UPDATE_PRICE_PICKLE, ys_file))
daily_instrument_obj_list_file = '%s/%s' % (UPDATE_PRICE_PICKLE, file_name)
if os.path.exists(daily_instrument_obj_list_file):
os.remove(daily_instrument_obj_list_file)
with open(daily_instrument_obj_list_file, 'wb') as f:
pickle.dump(daily_instrument_obj_list, f, True)
upload_instrument_file_job(server_name_tuple)
update_server_instrument(server_name_tuple)
def insert_server_db():
if len(future_insert_list) == 0 and len(option_insert_list) == 0:
return
server_model = server_constant_local.get_server_model('host')
server_session = server_model.get_db_session('common')
future_sql_list = to_many_sql(Instrument, future_insert_list,
'common.instrument', 1000)
for future_sql in future_sql_list:
server_session.execute(future_sql)
option_sql_list = to_many_sql(Instrument, option_insert_list,
'common.instrument', 1000)
for option_sql in option_sql_list:
server_session.execute(option_sql)
server_session.commit()
server_model.close()
def update_db():
sql_list = to_many_sql(Instrument, instrument_exchange_db_dict.values(), 'common.instrument')
for sql in sql_list:
session_common.execute(sql)
now_time = long(date_utils.get_today_str('%H%M%S'))
# 000974,000823的prev_close早上行情中为0，用昨日close赋值。
if now_time < 150500:
update_sql = 'update common.instrument t set t.PREV_CLOSE = t.`CLOSE` where t.PREV_CLOSE = 0'
session_common.execute(update_sql)
def insert_server_db():
if len(instrument_insert_list) == 0:
return
server_model = server_constant_local.get_server_model('host')
server_session = server_model.get_db_session('common')
sql_list = to_many_sql(Instrument, instrument_insert_list,
'common.instrument')
for sql in sql_list:
server_session.execute(sql)
server_session.commit()
server_model.close()
def __update_instrument(session_common, instrument_db_dict, market_info_df, fund_instrument_df):
now_time = int(now.strftime('%H%M%S'))
instrument_db_list = []
market_info_dict = dict()
for index, row in market_info_df.iterrows():
market_info_dict[row['InstrumentID']] = row
fund_instrument_dict = dict()
for index, row in fund_instrument_df.iterrows():
fund_instrument_dict[row['InstrumentID']] = row
for (ticker, instrument_db) in instrument_db_dict.items():
if ticker not in market_info_dict and ticker not in fund_instrument_dict:
continue
if ticker in market_info_dict:
row = market_info_dict[ticker]
if now_time > 150500:
if row['ClosePrice'] != '' and float(row['ClosePrice']) > 0:
instrument_db.close = row['ClosePrice']
instrument_db.close_update_time = datetime.now()
instrument_db.volume = row['Volume']
else:
if row['PreClosePrice'] != '' and float(row['PreClosePrice']) > 0:
instrument_db.prev_close = row['PreClosePrice']
instrument_db.prev_close_update_time = datetime.now()
instrument_db.prev_settlementprice = row['PreSettlementPrice'] if row['PreSettlementPrice'] != '' else None
instrument_db.uplimit = row['UpperLimitPrice'] if row['UpperLimitPrice'] != '' else None
instrument_db.downlimit = row['LowerLimitPrice'] if row['LowerLimitPrice'] != '' else None
if ticker in fund_instrument_dict:
row = fund_instrument_dict[ticker]
if instrument_db.tranche is None:
# 只更新母基金的prev_nav
instrument_db.prev_nav = row['NetPrice']
instrument_db.pcf = row['pcf']
instrument_db_list.append(instrument_db)
sql_list = to_many_sql(Instrument, instrument_db_list, 'common.instrument')
for sql in sql_list:
session_common.execute(sql)
def backup_instrument_pickle_file():
date_str = date_utils.get_today_str('%Y-%m-%d')
server_host = server_constant.get_server_model('host')
session_common = server_host.get_db_session('common')
query = session_common.query(Instrument)
obj_list = []
for future_db in query.filter(Instrument.del_flag == 0):
obj_list.append(future_db)
instrument_obj_list = to_many_sql(Instrument, obj_list,
'common.instrument')
pickle_file_name = 'INSTRUMENT_' + date_str.replace('-', '') + '.pickle'
target_folder_list = [
PRICE_FILES_BACKUP_FOLDER,
]
for target_folder in target_folder_list:
target_path_base = '%s/%s' % (target_folder, date_str.replace('-', ''))
if not os.path.exists(target_path_base):
os.mkdir(target_path_base)
target_path = '%s/%s' % (target_path_base, pickle_file_name)
with open(target_path, 'wb') as f:
pickle.dump(instrument_obj_list, f, True)
def __save_instrument(session_common, instrument_db_dict, instrument_df):
instrument_db_list = []
now_time = int(now.strftime('%H%M%S'))
for index, row in instrument_df.iterrows():
ticker = row['InstrumentID']
if ticker in instrument_db_dict:
instrument_db = instrument_db_dict[ticker]
if (now_time > 150500) and (now_time < 180000):
instrument_db.close = row['ClosePrice']
instrument_db.close_update_time = datetime.now()
instrument_db.volume = row['Volume']
else:
instrument_db.prev_close = row['PreClosePrice']
instrument_db.prev_settlementprice = row['PreSettlementPrice']
instrument_db.uplimit = row['UpperLimitPrice']
instrument_db.downlimit = row['LowerLimitPrice']
instrument_db.prev_close_update_time = datetime.now()
else:
instrument_db = Instrument()
instrument_db.ticker = ticker
instrument_db.name = ticker
instrument_db.ticker_exch = ticker
instrument_db.ticker_exch_real = ticker
instrument_db.create_date = row['CreateDate']
instrument_db.effective_since = row['OpenDate']
instrument_db.tick_size_table = '0:%f' % float(row['PriceTick'])
instrument_db.exchange_id = row['exchange_id']
instrument_db.type_id = row['type_id']
instrument_db.market_status_id = row['market_status_id']
instrument_db.multiplier = row['multiplier']
instrument_db.crncy = row['crncy']
instrument_db.market_sector_id = row['market_sector_id']
instrument_db.round_lot_size = row['round_lot_size']
instrument_db.undl_tickers = row['undl_tickers']
instrument_db.commission_rate_type = row['commission_rate_type']
instrument_db.is_settle_instantly = row['is_settle_instantly']
instrument_db.is_purchase_to_redemption_instantly = row[
'is_purchase_to_redemption_instantly']
instrument_db.is_buy_to_redpur_instantly = row[
'is_buy_to_redpur_instantly']
instrument_db.is_redpur_to_sell_instantly = row[
'is_redpur_to_sell_instantly']
instrument_db.put_call = row['put_call']
instrument_db.strike = row['strike']
instrument_db.option_margin_factor1 = row['option_margin_factor1']
instrument_db.option_margin_factor2 = row['option_margin_factor2']
instrument_db.session = row['session']
instrument_db.expire_date = row['ExpireDate']
instrument_db.fut_val_pt = row['VolumeMultiple']
instrument_db.max_market_order_vol = row['MaxMarketOrderVolume']
instrument_db.min_market_order_vol = row['MinMarketOrderVolume']
instrument_db.max_limit_order_vol = row['MaxLimitOrderVolume']
instrument_db.min_limit_order_vol = row['MinLimitOrderVolume']
instrument_db.longmarginratio = row['longmarginratio']
instrument_db.shortmarginratio = row['shortmarginratio']
instrument_db.longmarginratio_speculation = instrument_db.longmarginratio
instrument_db.shortmarginratio_speculation = instrument_db.shortmarginratio
instrument_db.longmarginratio_hedge = instrument_db.longmarginratio
instrument_db.shortmarginratio_hedge = instrument_db.shortmarginratio
instrument_db.longmarginratio_arbitrage = instrument_db.longmarginratio
instrument_db.shortmarginratio_arbitrage = instrument_db.shortmarginratio
instrument_db.update_date = datetime.now()
instrument_db_list.append(instrument_db)
sql_list = to_many_sql(Instrument, instrument_db_list, 'common.instrument')
print len(sql_list)
for sql in sql_list:
session_common.execute(sql)
def __add_instrument_db(session_common, instrument_db_dict, instrument_df):
instrument_db_list = []
for index, row in instrument_df.iterrows():
ticker = row['InstrumentID']
if ticker in instrument_db_dict:
instrument_db = instrument_db_dict[ticker]
else:
instrument_db = Instrument()
instrument_db.ticker = ticker
# instrument_db.name = ticker
instrument_db.name = row['ExchangeInstID']
instrument_db.ticker_exch = ticker
instrument_db.ticker_exch_real = ticker
instrument_db.create_date = row['CreateDate'] if row['CreateDate'] != '' else None
instrument_db.effective_since = row['OpenDate'] if row['OpenDate'] != '' else None
instrument_db.tick_size_table = '0:%f' % float(row['PriceTick'])
instrument_db.exchange_id = row['exchange_id']
instrument_db.type_id = row['type_id']
instrument_db.market_status_id = row['market_status_id']
instrument_db.multiplier = row['multiplier']
instrument_db.crncy = 'CNY'
instrument_db.market_sector_id = row['market_sector_id']
instrument_db.round_lot_size = row['round_lot_size']
instrument_db.undl_tickers = row['undl_tickers']
instrument_db.commission_rate_type = row['commission_rate_type']
instrument_db.is_settle_instantly = row['is_settle_instantly']
instrument_db.is_purchase_to_redemption_instantly = row['is_purchase_to_redemption_instantly']
instrument_db.is_buy_to_redpur_instantly = row['is_buy_to_redpur_instantly']
instrument_db.is_redpur_to_sell_instantly = row['is_redpur_to_sell_instantly']
instrument_db.put_call = row['put_call'] if row['put_call'] != '' else None
instrument_db.strike = row['strike'] if row['strike'] != '' else None
instrument_db.option_margin_factor1 = row['option_margin_factor1'] if row['option_margin_factor1'] != '' else None
instrument_db.option_margin_factor2 = row['option_margin_factor2'] if row['option_margin_factor2'] != '' else None
instrument_db.expire_date = row['ExpireDate'] if row['ExpireDate'] != '' else None
instrument_db.fut_val_pt = row['VolumeMultiple']
instrument_db.max_market_order_vol = row['max_market_order_vol'] if row['max_market_order_vol'] != '' else None
instrument_db.min_market_order_vol = row['min_market_order_vol'] if row['min_market_order_vol'] != '' else None
instrument_db.max_limit_order_vol = row['max_limit_order_vol'] if row['max_limit_order_vol'] != '' else None
instrument_db.min_limit_order_vol = row['min_limit_order_vol'] if row['min_limit_order_vol'] != '' else None
instrument_db.longmarginratio = row['longmarginratio']
instrument_db.shortmarginratio = row['shortmarginratio']
instrument_db.longmarginratio_speculation = instrument_db.longmarginratio
instrument_db.shortmarginratio_speculation = instrument_db.shortmarginratio
instrument_db.longmarginratio_hedge = instrument_db.longmarginratio
instrument_db.shortmarginratio_hedge = instrument_db.shortmarginratio
instrument_db.longmarginratio_arbitrage = instrument_db.longmarginratio
instrument_db.shortmarginratio_arbitrage = instrument_db.shortmarginratio
instrument_db.update_date = datetime.now()
if instrument_db.type_id == instrument_type_enums.CommonStock:
if row['IsTrading'] == '1':
instrument_db.inactive_date = None
else:
instrument_db.inactive_date = now.strftime('%Y-%m-%d')
instrument_db_list.append(instrument_db)
sql_list = to_many_sql(Instrument, instrument_db_list, 'common.instrument')
for sql in sql_list:
session_common.execute(sql)
def update_db():
sql_list = to_many_sql(Instrument, instrument_db_dict.values(), 'common.instrument')
for sql in sql_list:
session_common.execute(sql)