通联数据库查询sql语句

#获取所有股票列表
sql = "SELECT SECURITY_ID,TICKER_SYMBOL,SEC_FULL_NAME,SEC_SHORT_NAME,LIST_STATUS_CD,ASSET_CLASS,PARTY_ID FROM md_security where ASSET_CLASS='E' AND EXCHANGE_CD IN ('XSHG', 'XSHE') ;"
df = pd.read_sql(sql, conn)
df = df.drop(df[df['TICKER_SYMBOL'].str.contains('DY', na=False)].index)
df = df.drop(df[df['TICKER_SYMBOL'].str.contains('A', na=False)].index)
df = df.drop(df[df['SEC_SHORT_NAME'].str.contains('B', na=False)].index)
#丢掉B股
df_t = df_t.drop(df_t[df_t['TICKER_SYMBOL'].str.startswith('90')].index)
df_t = df_t.drop(df_t[df_t['TICKER_SYMBOL'].str.startswith('20')].index)
#查询指数行情
open_date="20220801"
end_date="20221108"
sql="SELECT * FROM 	mkt_idxd where TICKER_SYMBOL='000852' AND(TRADE_DATE BETWEEN '"+open_date+"' AND '"+end_date+"');"

#申万一级行业名称
name_lst = ['银行', '公用事业', '房地产', '商贸零售', '机械设备', '电力设备', '建筑装饰', '建筑材料', '家用电器',
            '纺织服饰', '农林牧渔', '电子', '交通运输', '汽车', '医药生物', '社会服务', '计算机', '有色金属', '通信',
            '环保', '食品饮料', '传媒', '石油石化', '基础化工', '非银金融', '轻工制造', '国防军工', '美容护理', '钢铁',
            '煤炭', '综合']
#从mkt_idxd获取申万一级行业指数行情的INDEX_ID
code_lst=[11488,	2162,	2164,	2166,	11482,	11481,	11480,	11479,	2157,	2159,	2147,	2154,	2163,	11484,	2161,	2167,	11485,	2151,	11487,	592004,	2158,	11486,	591993,	2149,	11489,	2160,	11483,	592012,	2150,	591987,	2169]

#保存df为csv，注意编码
df.to_csv("all.csv",index=False,encoding='utf_8_sig')

#行业指数日行情。先从idx.csv里找到申万一级行业的SECURITY_ID对应表里的INDEX_ID
sql="SELECT a.INDEX_ID,a.TICKER_SYMBOL,a.EXCHANGE_CD,a.TRADE_DATE,a.PRE_CLOSE_INDEX,a.OPEN_INDEX,a.HIGHEST_INDEX,a.LOWEST_INDEX,a.CLOSE_INDEX,a.TURNOVER_VALUE,a.TURNOVER_VOL,a.UPDATE_TIME "\
"FROM mkt_idxd a WHERE a.INDEX_ID='2150' "

#某一交易日沪深两市股票的总成交量和总流通A股数
sql="SELECT TRADE_DATE, sum(TURNOVER_VOL) AS TURNOVER_VOL,sum(TURNOVER_VALUE) as TURNOVER_VALUE, sum(FLOAT_SHARES) as FLOAT_SHARES, sum(MARKET_VALUE) AS MARKET_VALUE, sum(NEG_MARKET_VALUE) AS NEG_MARKET_VALUE FROM mkt_stats_ex_td_shsz "\
"WHERE TRADE_DATE = '20181228' AND STATS_TYPE_CLASS_CD = '0101' GROUP BY TRADE_DATE"

#获取单个股票申万一级行业类别
sql = "SELECT a.PARTY_ID, c.TICKER_SYMBOL, c.SEC_FULL_NAME,b.TYPE_ID,b.TYPE_NAME,b.INDUSTRY,a.INTO_DATE,a.OUT_DATE,a.IS_NEW " \
"FROM md_inst_type a JOIN md_type b ON left(a.TYPE_ID,8)=b.TYPE_ID JOIN md_security c ON a.PARTY_ID=c.PARTY_ID " \
"WHERE a.TYPE_ID like '010321%' AND c.ASSET_CLASS = 'E' AND a.OUT_DATE is NULL AND c.TICKER_SYMBOL='600816'"

#获得各个股票行业分类
def getstockind():
    sql = "SELECT SECURITY_ID,TICKER_SYMBOL,SEC_FULL_NAME,SEC_SHORT_NAME,LIST_STATUS_CD,ASSET_CLASS,PARTY_ID FROM md_security where ASSET_CLASS='E' AND EXCHANGE_CD IN ('XSHG', 'XSHE') ;"
    df = pd.read_sql(sql, conn)
    df = df.drop(df[df['TICKER_SYMBOL'].str.contains('DY', na=False)].index)
    df = df.drop(df[df['TICKER_SYMBOL'].str.contains('A', na=False)].index)
    df = df.drop(df[df['SEC_SHORT_NAME'].str.contains('B', na=False)].index)
    df=df.reset_index()
    df['industry']=""
    #df.to_csv("stock_list_clean.csv",index=False,encoding='utf_8_sig')
    for index,row in df.iterrows():
        code = row['TICKER_SYMBOL']
        code = str(code).zfill(6)
        print(code)
        #date = row['TRADE_DATE']

        sql_ind = "SELECT a.PARTY_ID, c.TICKER_SYMBOL, c.SEC_FULL_NAME,b.TYPE_ID,b.TYPE_NAME,b.INDUSTRY,a.INTO_DATE,a.OUT_DATE,a.IS_NEW " \
              "FROM md_inst_type a JOIN md_type b ON left(a.TYPE_ID,8)=b.TYPE_ID JOIN md_security c ON a.PARTY_ID=c.PARTY_ID " \
              "WHERE a.TYPE_ID like '010321%' AND c.ASSET_CLASS = 'E' AND a.OUT_DATE is NULL AND c.TICKER_SYMBOL="+code

        df_ind = pd.read_sql(sql_ind, conn)
        try:
            indname=df_ind.iloc[0,4]
        except Exception as e:
            print(e)
            continue
        print(indname)
        df.at[index, 'industry'] =indname
    df.to_csv("industry.csv",index=False,encoding='utf_8_sig')
    return None

#获取各个股票成交量数据
sql_t="SELECT SECURITY_ID,TICKER_SYMBOL,EXCHANGE_CD,TRADE_DATE,TURNOVER_VOL,TURNOVER_VALUE,TURNOVER_RATE FROM mkt_equd WHERE EXCHANGE_CD IN ('XSHG', 'XSHE') AND TRADE_DATE="+today
#填0
df_t['TICKER_SYMBOL']=df_t['TICKER_SYMBOL'].apply(lambda x:str(x).zfill(6))
#丢掉B股
df_t = df_t.drop(df_t[df_t['TICKER_SYMBOL'].str.startswith('90')].index)
df_t = df_t.drop(df_t[df_t['TICKER_SYMBOL'].str.startswith('20')].index)
#获取无限售流通A股
sql_f = "SELECT a.PARTY_ID,a.NONREST_FLOAT_A,a.CHANGE_DATE FROM equ_share_change a JOIN md_security b ON a.PARTY_ID=b.PARTY_ID WHERE b.TICKER_SYMBOL=" + code

#获取所有沪深股票成交量、流通股本，2022/5之后没有深交所数据了
sql_all = "SELECT TRADE_DATE, sum(TURNOVER_VOL) AS TURNOVER_VOL,sum(TURNOVER_VALUE) as TURNOVER_VALUE, sum(FLOAT_SHARES) as FLOAT_SHARES, sum(MARKET_VALUE) AS MARKET_VALUE, sum(NEG_MARKET_VALUE) AS NEG_MARKET_VALUE FROM mkt_stats_ex_td_shsz " \
"WHERE STATS_TYPE_CLASS_CD = '010101' GROUP BY TRADE_DATE"

#获取各个股票资金流向数据
sql_t="select * from mkt_equ_flow_order where TRADE_DATE=" + today

#获取上市公司立案调查数据
sql="select a.PARTY_ID,a.INVESTIGATION_BEGIN_DATE,a.INVESTIGATION_END_DATE,a.PUBLISH_DATE,a.EVENT,b.PARTY_ID,b.TICKER_SYMBOL,b.SEC_SHORT_NAME from equ_qa_investigation a "\
    "join md_security b ON a.PARTY_ID = b.PARTY_ID AND b.EXCHANGE_CD IN ('XSHG','XSHE')"

#获取股票前复权行情数据日线
sql_t = "SELECT SECURITY_ID,TICKER_SYMBOL,EXCHANGE_CD,TRADE_DATE,CLOSE_PRICE_1,TURNOVER_VOL FROM mkt_equd_adj WHERE TICKER_SYMBOL='"+code+"' AND(TRADE_DATE BETWEEN '" + open_date + "' AND '" + end_date + "');"

#获取股票对应的申万二级行业ID和行业估值PB PE信息
sql = "SELECT a.PARTY_ID, c.TICKER_SYMBOL, c.SEC_FULL_NAME,b.TYPE_ID,b.TYPE_NAME,b.INDUSTRY,a.INTO_DATE,a.OUT_DATE,a.IS_NEW,d.SECURITY_ID,e.PE_VALUE,e.PB " \
      "FROM md_inst_type a JOIN md_type b ON left(a.TYPE_ID,10)=b.TYPE_ID JOIN md_security c ON a.PARTY_ID=c.PARTY_ID JOIN idx d ON b.TYPE_ID=d.IND_ID JOIN mkt_idxd_eval e on d.SECURITY_ID=e.INDEX_ID "  \
      "WHERE a.TYPE_ID like '010321%' AND c.ASSET_CLASS = 'E' AND a.OUT_DATE is NULL AND e.TRADE_DATE='20230512' AND  e.PE_TYPE='1' AND c.TICKER_SYMBOL='600816' "
#查行业行情的INDEX_ID是在idx.csv里的SECURITY_ID，sw_syscode.csv里的VALUE_NUM_CD和idx.csv里的IND_ID可以连接起来

#筛选沪深300成分股
sql="select a.SECURITY_ID,b.TICKER_SYMBOL AS INDEX_SYMBOL,b.SEC_SHORT_NAME AS INDEX_NAME,a.CONS_ID,c.TICKER_SYMBOL,c.SEC_SHORT_NAME,a.INTO_DATE,a.OUT_DATE,a.IS_NEW,a.UPDATE_TIME FROM idx_cons a JOIN md_security b on a.SECURITY_ID=b.SECURITY_ID JOIN md_security c on a.CONS_ID=c.SECURITY_ID  "\
    "WHERE b.TICKER_SYMBOL='000300'"