python 積累6（wind數(shù)據(jù)庫對指數(shù)成分股的提?。?/h2>

import cx_Oracle
import pandas as pd
import numpy as np
import os
os.chdir('C:\\Users\\jiangbaiyan\\Desktop\\找到行業(yè)的成分股')
os.getcwd()  


conn = cx_Oracle.connect('windquery/wind2010query@10.2.89.132:1521/winddb')
cur = conn.cursor() 
def search_securityID(windID):
    sql = ''' select F16_0001 from TB_OBJECT_0001 where F1_0001 = '%s' ''' % windID
    cur.execute(sql)
    temp = cur.fetchall()
    return temp[0][0]

def search_name(securityID):
    sql = ''' select F1_0001, F6_0001 from TB_OBJECT_0001 where F16_0001 = '%s' ''' % securityID
    cur.execute(sql)
    temp = cur.fetchall()
    return [temp[0][0], temp[0][1]]

def search_name2securityID(name):
    sql = ''' select F16_0001 from TB_OBJECT_0001 where F6_0001 = '%s' ''' % name
    cur.execute(sql)
    temp = cur.fetchall()
    return temp[0][0]

def get_stock(indexID):
    sql = ''' select F1_1475  from TB_OBJECT_1475 where F2_1475 = '%s' and F6_1475 = 1 ''' % (indexID)
    cur.execute(sql)
    temp = cur.fetchall()    
    return temp

def search_windID(securityID):
    sql = ''' select F1_0001 from TB_OBJECT_0001 where F16_0001 = '%s' ''' % securityID
    cur.execute(sql)
    temp = cur.fetchall()
    return temp[0][0]

industry = pd.read_excel('非重復行業(yè).xlsx')
stock_wind = pd.read_excel('個股代碼.xlsx')
industry.columns = ['方便閱讀','簡稱']
industry_list = []
stock_list = []

for key in list(industry['簡稱']):
    print(key)
    industry_list.append(search_name2securityID(key))    
for key in list(stock_wind['代碼']):
    print(key)
    stock_list.append(search_securityID(key))

dic = {}
for key in industry_list:
    temp =[]
    temp = get_stock(key)
    pd_temp = pd.DataFrame()
    for i,stock in enumerate(temp):
        pd_temp.loc[i,'股票ID'] = search_name(stock[0])[0]
        pd_temp.loc[i,'證券簡稱']= search_name(stock[0])[1]
        if stock[0] in stock_list:
            pd_temp.loc[i,'輔助列'] = 1
        else:
            pd_temp.loc[i,'輔助列'] = 0
        print(stock[0])    
    dic[search_name(key)[1]] = pd_temp
    print(key)
np.save('先進制造行業(yè)成分股.npy',dic)    


writer = pd.ExcelWriter('先進制造行業(yè)成分股.xlsx')
for key in dic:
    temp = dic[key]
    temp.to_excel(writer,sheet_name = key,index = 0)
    print(key)
writer.save()

?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。