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
