quartz学习笔记5：update

来源：互联网发布：nginx if 编辑：程序博客网时间：2024/05/21 19:41
#回测框架的核心代码，回测数据
import chardetfrom quantify import db,dataApiimport pandas as pdfrom pandas import DataFrameimport timeimport tracebackdb.create_engine(user='root', password='', database='quant', host='127.0.0.1', port=3306)#导入所有代码进数据库def input_list_data(buf):    #buf='SH' or buf='SZ'        f=open('../data/%s.SNT'%buf,'r')    str=f.readlines()    for index,i in enumerate(str):        if index>1:            line=i.strip().split('\t')                    try:                code_name=line[1].decode('GB2312').encode('utf-8')                code=line[0]+'.%s'%buf                n = db.update('insert into code_list(code, name , market) values(?, ? ,?)',code , code_name , buf)                print n            except Exception as e:                print e            #print chardet.detect(code_name)查看编码    f.close()def input_stock_data():    res=db.select('select * from code_list')    #string_list=[]    for i in res:        stock_code=i[u'code'].decode('utf-8')        stock_num=stock_code.split('.')        get_sd_csv('20010101','20150814',stock_num[0],'../stock_data/%s.csv'%stock_num[0])        print stock_num[0]        def get_sd_csv(start,end,stock_num,filename):    client = dataApi.Client()    url='/api/market/getMktEqud.csv?field=&beginDate=%s&endDate=%s&secID=&ticker=%s&tradeDate='%(start,end,stock_num)    code, result = client.getData(url)    if(code==200):        file_object = open(filename, 'w')        file_object.write(result)        file_object.close( )        print 'ok'    else:        print code        print resultdef input_daily_data():    res=db.select('select * from code_list')    #string_list=[]    for i in res:        stock_code=i[u'code'].decode('utf-8')        stock_num=stock_code.split('.')        stock_data=open_csv('../stock_data/%s.csv'%stock_num[0])        try:            for i in range(stock_data.shape[0]):                only_key='%s+%s'%(stock_data.iloc[i]['secID'],stock_data.iloc[i]['tradeDate'])                charset=chardet.detect(stock_data.iloc[i]['secShortName'])                print charset,stock_data.iloc[i]['secShortName'].decode('GB2312')                n = db.update('insert into daily_data(secID, ticker , secShortName , exchangeCD , tradeDate , preClosePrice , actPreClosePrice ,openPrice , highestPrice , lowestPrice , closePrice , turnoverVol , turnoverValue , dealAmount , turnoverRate , accumAdjFactor ,negMarketValue , marketValue , PE ,PE1, PB, only_key) values(?, ? ,? ,? ,? ,? ,? ,? ,? ,?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? )',stock_data.iloc[i]['secID'],  str(stock_data.iloc[i]['ticker']) ,stock_data.iloc[i]['secShortName'].decode('GB2312').encode('utf-8'), stock_data.iloc[i]['exchangeCD'] , stock_data.iloc[i]['tradeDate'] , float(stock_data.iloc[i]['preClosePrice']) , float(stock_data.iloc[i]['actPreClosePrice']) ,float(stock_data.iloc[i]['openPrice']) , float(stock_data.iloc[i]['highestPrice']) , float(stock_data.iloc[i]['lowestPrice']) , float(stock_data.iloc[i]['closePrice']) , int(stock_data.iloc[i]['turnoverVol']) , float(stock_data.iloc[i]['turnoverValue']) , int(stock_data.iloc[i]['dealAmount']) , float(stock_data.iloc[i]['turnoverRate']) , float(stock_data.iloc[i]['accumAdjFactor']) , float(stock_data.iloc[i]['negMarketValue']) , float(stock_data.iloc[i]['marketValue']) , float(stock_data.iloc[i]['PE']) , float(stock_data.iloc[i]['PE1']), float(stock_data.iloc[i]['PB']),only_key)                print n            print '%s:插入完成'%stock_num[0]        except Exception as e:            print e             f=open('log.txt','a')            traceback.print_exc(file=f)               f.flush()               f.close()               #f.write('time:%s,error:%s')%(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),str(e))            #f.close()                def open_csv(filename):    try:        res=pd.read_csv(filename)        return res    except Exception as e:        print e def up_data(start,end,stock_num):    client = dataApi.Client()    url='/api/market/getMktEqud.csv?field=&beginDate=%s&endDate=%s&secID=&ticker=%s&tradeDate='%(start,end,stock_num)    code, result = client.getData(url)    if(code==200):        return result        else:        print code      def select_updata():    stock_list=db.select('select * from code_list')           for i in stock_list:        stock_code=i[u'code'].decode('utf-8')        stock_num=stock_code.split('.')        rs=up_data('20150817', '20150817', stock_num[0])        if '-1:No Data Returned' in rs:            print '00'        else:            hs=rs.split(',')            temp=[]                        for index,i in enumerate(hs[20:]):                if index==0:                    h=i.replace('PB\n','')                    temp.append(h.strip('\"'))                elif index==20:                    temp.append(i.replace('\n',''))                else:                    temp.append(i.strip('\"'))                                          if temp[2] :                  only_key='%s+%s'%(temp[0],temp[4])                                try:                                    n=db.update('insert into daily_data(secID, ticker , secShortName , exchangeCD , tradeDate , preClosePrice , actPreClosePrice ,openPrice , highestPrice , lowestPrice , closePrice , turnoverVol , turnoverValue , dealAmount , turnoverRate , accumAdjFactor ,negMarketValue , marketValue , PE ,PE1, PB, only_key) values(?, ? ,? ,? ,? ,? ,? ,? ,? ,?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? )',temp[0],  temp[1] ,temp[2].decode('GB18030').encode('utf-8'), temp[3] , temp[4] , float(temp[5]) , float(temp[6]) ,float(temp[7]) , float(temp[8]) , float(temp[9]) , float(temp[10]) , int(temp[11]) , float(temp[12]) , int(temp[13]) , float(temp[14]) , float(temp[15]) , float(temp[16]) , float(temp[17]) , float(temp[18]) , float(temp[19]), float(temp[20]),only_key)                    print temp[2].decode('GB18030').encode('utf-8'),n                    f=open('stock_list.txt','a')                    f.write('%s,%s,%s\n'%(temp[0],temp[1],temp[2].decode('GB18030').encode('utf-8')))                    f.close()                except Exception as e:                    print e                    print 'All stock updata done!'def auto_updata(day):    f=open('stock_list.txt','r')    h=f.readlines()    f.close()    for i in h:        stock_line=i.strip().split(',')        rs=up_data(day, day, stock_line[1])        if '-1:No Data Returned' in rs:            print '00'        else:            hs=rs.split(',')            temp=[]                        for index,i in enumerate(hs[20:]):                if index==0:                    h=i.replace('PB\n','')                    temp.append(h.strip('\"'))                elif index==20:                    temp.append(i.replace('\n',''))                else:                    temp.append(i.strip('\"'))                    #return temp                        if temp[2] :                  only_key='%s+%s'%(temp[0],temp[4])                                try:                                    n=db.update('insert into daily_data(secID, ticker , secShortName , exchangeCD , tradeDate , preClosePrice , actPreClosePrice ,openPrice , highestPrice , lowestPrice , closePrice , turnoverVol , turnoverValue , dealAmount , turnoverRate , accumAdjFactor ,negMarketValue , marketValue , PE ,PE1, PB, only_key) values(?, ? ,? ,? ,? ,? ,? ,? ,? ,?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? )',temp[0],  temp[1] ,temp[2].decode('GB18030').encode('utf-8'), temp[3] , temp[4] , float(temp[5]) , float(temp[6]) ,float(temp[7]) , float(temp[8]) , float(temp[9]) , float(temp[10]) , int(temp[11]) , float(temp[12]) , int(temp[13]) , float(temp[14]) , float(temp[15]) , float(temp[16]) , float(temp[17]) , float(temp[18]) , float(temp[19]), float(temp[20]),only_key)                    print temp[2].decode('GB18030').encode('utf-8'),n                except Exception as e:                    print e                    print 'All stock updata done!'def getTradeCal(exchangeCD,beginDate,endDate,field):    client = dataApi.Client()    url='/api/master/getTradeCal.csv?field=%s&exchangeCD=%s&beginDate=%s&endDate=%s'%(field,exchangeCD,beginDate,endDate)    code, result = client.getData(url)    if(code==200):        return result        else:        print codetoday=time.strftime("%Y%m%d",time.localtime())tradecal=getTradeCal(exchangeCD='XSHG',beginDate=today,endDate=today,field='isOpen')is_open=tradecal.split('\n')if is_open[1]=='1':    auto_updata(today)    print todayelse:    print "today is not trade day!"#auto_updata('20150901')
阅读全文
0 0