def query_cetf_components_info(ticker, market_id): """ 根据二级市场代码获取所有成分股的信息 :param ticker: etf二级市场代码 :param market_id: etf市场,1是上海,2是深圳 :return: """ market_id = str(market_id) sql = ('SELECT underlying_instrument_id, substitute_flag,' + ' component_share, premium_ratio, preclose_px/10000,' + ' creation_cash_substitute, estimate_cash_component,' + ' redemption_cash_substitute, underlying_instrument_source' + ' from xtp_etf_components_' + date + ' a, xtp_etf_baseinfo_' + date + ' b, xtp_exch_sec_' + date + ' c' + ' where a.etf_code1 = b.etf_code1' + ' and a.underlying_instrument_id = c.instrument_id' + ' and b.ticker = "' + ticker + '"' + ' and b.exch_id = "' + market_id + '"' + ' and c.security_type in (0, 1, 2)') conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() cur.close() conn.close() return rs
def __select_db_fetch_all(self, s): """连接数据库,执行语句,返回所有结果""" conn = connectMysql() cur = conn.cursor() rs = [] try: cur.execute(s) rs = cur.fetchall() except Exception, e: print u'执行sql语句失败' print e
def query_creation_redem_unit(ticker): """查询etf最小申赎单位""" date = time.strftime('%Y%m%d', time.localtime(time.time())) sql = 'SELECT creation_redemption_unit from xtp_etf_baseinfo_'+ date \ +' t1 where t1.ticker=' + ticker + '' conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchone() cur.close() conn.close() return rs[0]
def query_market_by_stkcode(ticker): """查询etf成分股的市场,参数tiker:成分股代码""" sql = 'SELECT exch_id' \ ' from xtp_exch_sec_' + date + \ ' where security_type in (0, 1, 2)' \ ' and security_status = 2' \ ' and instrument_id = ' + str(ticker) conn = connectMysql() with conn: cur = conn.cursor() cur.execute(sql) rs = cur.fetchone() return rs[0]
def query_estimate_cash_component(ticker): """查询预估现金差额""" date = time.strftime('%Y%m%d', time.localtime(time.time())) sql = 'SELECT estimate_cash_component / 10000.0' \ ' from xtp_etf_baseinfo_' + date + \ ' where etf_code0 = ' + ticker conn = connectMysql() with conn: cur = conn.cursor() cur.execute(sql) rs = cur.fetchone() estimate_cash_component = float(rs[0]) if rs else 0 return estimate_cash_component
def query_cetf_basketcount(ticker): """查询跨市场etf成分股数量""" date = time.strftime('%Y%m%d', time.localtime(time.time())) sql = 'SELECT count(*) from xtp_etf_components_' + date + \ ' a join xtp_etf_baseinfo_'+ date + \ ' b on a.etf_code1 = b.etf_code1 where b.ticker=' + ticker + '' conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchone() cur.close() conn.close() return rs[0]
def query_cetf_code1code2(ticker): """获取etf '一级市场申购赎回代码' 和 '资金划转代码'""" sql = 'SELECT etf_code1, etf_code2' \ ' from xtp_etf_baseinfo_' + date + \ ' where etf_code0 = ' + ticker conn = connectMysql() with conn: cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() code_rs = {} if rs is not (): code_rs['etf_code1'] = rs[0][0] code_rs['etf_code2'] = rs[0][1] return code_rs
def query_crossmakert_sub_cash(etf_code1): """查询跨市场etf申赎或赎回时,跨市场成分股(退补现金替代 + 必须现金替代)需要的总金额 """ sql1 = ( 'SELECT ' + 'case when sum(creation_cash_substitute * (1 + premium_ratio/100000))' + 'is null then 0' + ' else sum(creation_cash_substitute * (1 + premium_ratio/100000)) ' + 'end as sum_creation_cash, ' + 'case when sum(redemption_cash_substitute * (1 - premium_ratio/100000))' + ' is null then 0 ' + ' else sum(redemption_cash_substitute * (1 - premium_ratio/100000)) ' + 'end as sum_redemption_cash' + ' from xtp_etf_components_' + date + ' where etf_code1 = ' + etf_code1 + ' and substitute_flag = 3') sql2 = ( 'SELECT ' + ' case when sum(creation_cash_substitute) is null then 0' + ' else sum(creation_cash_substitute) end as sum_creation_cash,' + ' case when sum(redemption_cash_substitute) is null then 0' + ' else sum(redemption_cash_substitute) end as sum_redemption_cash' + ' from xtp_etf_components_' + date + ' where etf_code1 = ' + etf_code1 + ' and substitute_flag = 4') conn = connectMysql() with conn: cur = conn.cursor() # 退补现金替代部分 cur.execute(sql1) rs1 = cur.fetchall() code_rs1 = [0, 0] if rs1: code_rs1[0] = rs1[0][0] code_rs1[1] = rs1[0][1] # 必须现金替代部分 cur.execute(sql2) rs2 = cur.fetchall() code_rs2 = [0, 0] if rs2: code_rs2[0] = rs2[0][0] code_rs2[1] = rs2[0][1] # 退补现金替代部分 + 必须现金替代部分 rs = [code_rs1[i] + code_rs2[i] for i in range(2)] code_rs = {} if sum(rs) != 0: code_rs['creation_cash_substitute'] = rs[0] code_rs['redemption_cash_substitute'] = rs[1] return code_rs
def query_cetf_components(ticker,underlying_instrument_id): """查询etf单支成分股数量 参数ticker:二级市场代码 underlying_instrument_id:成分股代码""" sql = 'SELECT a.component_share ' \ 'from xtp_etf_components_' + date + \ ' a, xtp_etf_baseinfo_' + date + \ ' b where a.etf_code1 = b.etf_code1 ' \ ' and b.ticker = "' + ticker + \ '" and a.underlying_instrument_id = "' \ + underlying_instrument_id + '"' conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchone() cur.close() conn.close() return rs[0]
def query_cetf_component_share(ticker): """查询etf的成分股代码及成分股对应数量(etf最小申购赎回时对应的该成分股数量) 参数ticker:ETF二级市场代码 返回字典:key=成分股,value=成分股数量 """ sql = 'SELECT ' \ ' a.underlying_instrument_id, ' \ ' a.component_share ' \ 'from xtp_etf_components_' + date + \ ' a, xtp_etf_baseinfo_' + date + \ ' b where a.etf_code1 = b.etf_code1 ' \ ' and b.ticker = ' + ticker conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() component_shares = dict(rs) cur.close() conn.close() return component_shares
def query_creation_redemption_subcash(etf_code1): """查询同市场成分股必须现金替代时,申赎或赎回需要的总金额""" sql = ( 'SELECT ' + ' case when sum(creation_cash_substitute) is null then 0' + ' else sum(creation_cash_substitute) end as sum_creation_cash,' + ' case when sum(redemption_cash_substitute) is null then 0' ' else sum(redemption_cash_substitute) end as sum_redemption_cash' + ' from xtp_etf_components_' + date + ' where etf_code1 = ' + etf_code1 + ' and substitute_flag = 2') conn = connectMysql() with conn: cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() code_rs = {} if rs is not (): code_rs['creation_cash_substitute'] = rs[0][0] code_rs['redemption_cash_substitute'] = rs[0][1] return code_rs
def query_cetf_asset(stockcode, market, security_type, security_status, trade_status, fundid): """ 查询跨市场etf持仓 :param stockcode: 证券代码 :param market: 市场 :param security_type: 证券类型 :param security_status: 证券状态 :param trade_status: 交易状态 :param fundid: 资金账号 :return: """ date = time.strftime('%Y%m%d', time.localtime(time.time())) if stockcode == '999999': sql = ('SELECT a.stock_code,c.creation_redemption_unit from ' + 'xtp_stk_asset_' + date + ' a,xtp_exch_sec_' + date + ' b,xtp_etf_baseinfo_' + date + ' c WHERE a.stock_code=b.instrument_id ' + 'AND a.stock_code=c.ticker AND b.exch_id=' + market + ' AND b.security_type=' + security_type + ' and b.security_status=' + security_status + ' and b.trade_status=' + trade_status + ' AND a.fund_acc=\'' + fundid + '\'') else: sql = ('SELECT a.stock_code,c.creation_redemption_unit from ' + 'xtp_stk_asset_' + date + ' a,xtp_exch_sec_' + date + ' b,xtp_etf_baseinfo_' + date + ' c WHERE a.stock_code=b.instrument_id ' + 'AND a.stock_code=c.ticker AND b.exch_id=' + market + ' AND b.security_type=' + security_type + ' and b.security_status=' + security_status + ' and a.stock_code = ' + stockcode + ' and b.trade_status=' + trade_status + ' AND a.fund_acc=\'' + fundid + '\'') conn = connectMysql() cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() cur.close() conn.close() return rs
def query_cetf_components_code(ticker): """根据一级市场代码查询出ETF所有成分股代码 ticker:etf一级市场代码""" sql = ('SELECT' ' underlying_instrument_id' ' from' ' xtp_etf_components_' + date + ' c' ' join' ' xtp_exch_sec_' + date + ' e' ' on c.underlying_instrument_id = e.instrument_id' ' where' ' c.etf_code1 = ' + ticker + ' and e.security_type in (0, 1, 2)' ' and e.security_status = 2') conn = connectMysql() with conn: cur = conn.cursor() cur.execute(sql) rs = cur.fetchall() code_rs = [] if rs is not (): for code in rs: code_rs.append(code[0]) return code_rs