def GetSywg(info): tempdict = {} templist = [] header = { 'Host': 'www.yhqh.com.cn', 'Upgrade-Insecure-Requests': '1', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.84 Safari/537.36' } html = info.mysplider.getUrlcontent(SywgUrl, header=header) bs = BeautifulSoup(html, "html.parser") content = bs.find("ul", id="newsUl").find("li") href = "http://www.sywgqh.com.cn" + content.find("a", href=True)['href'] content = content.find("a", href=True).get_text() nowdate = re.findall(r'\d+', content)[0].encode("utf-8") # if not date==datetime.datetime.now().strftime("%Y%m%d"): # date=nowdate html = info.mysplider.getUrlcontent(href, header=header) bs = BeautifulSoup(html, "html.parser") content = bs.find("table", class_="ke-zeroborder").findAll("tr") for tr in content: tempdata = tr.findAll("td")[1:] col = [] tempdata = tempdata for j in range(len(tempdata)): if j not in [1, 2]: col.append(str(tempdata[j].get_text().encode("utf-8")).strip()) templist.append(col) templist = templist[:-1] BasicAPI().MyPrint(templist) tdict = BasicAPI().TwoList2Dict(templist) reduceDict(info, tdict, nowdate, '1')
def _ProductDetail__GetCZCEProductDetail(self): html = self.mysplider.getUrlcontent(self.Url, self.CZCEHeader) table = self.mysplider.tableTolist(html, "CZCE") if self.name in ['红枣']: VolumeMultiple = re.findall(r'\d+', table[1][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[4][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[3][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[13][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[5][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[12][1]] # ur'([一二三四五六七八九零十百千万亿]+|[0-9]+[,]*[0-9]+.[0-9]+)' tempEndDate = table[8][1] tempEndDelivDate = table[9][1] Delivemonth = table[6][1] elif self.name in ['甲醇']: VolumeMultiple = re.findall(r'\d+', table[3][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[6][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[5][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[15][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[7][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[14][1]] # ur'([一二三四五六七八九零十百千万亿]+|[0-9]+[,]*[0-9]+.[0-9]+)' tempEndDate = table[10][1] tempEndDelivDate = table[11][1] Delivemonth = table[8][1] else: VolumeMultiple = re.findall(r'\d+', table[2][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[5][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[4][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[14][1], self.ExchangeID) if self.name in ["白糖", "棉花"]: MinMargin = float( re.findall(r'([0-9.]+)[ ]*', table[12][1])[0]) / 100 tempEndDate = table[8][1] tempEndDelivDate = table[9][1] Delivemonth = table[6][1] else: MinMargin = float( re.findall(r'([0-9.]+)[ ]*', table[6][1])[0]) / 100 tempEndDate = table[9][1] tempEndDelivDate = table[10][1] Delivemonth = table[7][1] DelivMethod = self.DeliveryMethod[table[13][1]] EndDate = self.explainEnddate(tempEndDate) EndDelivDate = self.explaintempEndDelivDate(tempEndDelivDate) Delivemonth = self.explainDelivemonth(Delivemonth) return self.name, InstrumentCode, self.ExchangeID, VolumeMultiple, PriceTick, LimitUpDown, EndDate, EndDelivDate, MinMargin, DelivMethod, Delivemonth
def _ProductDetail__GetCFFEXProductDetail(self): table = [] html = self.mysplider.getUrlcontent(self.Url, self.CFFEXHeader) bs = BeautifulSoup(html, "lxml") divdata = bs.find("table") trdata = divdata.findAll("tr") for tr in trdata: td = tr.findAll("td") if len(td) == 4: col1 = [ td[0].get_text().encode("utf-8").strip(), td[1].get_text().encode("utf-8").strip() ] col2 = [ td[2].get_text().encode("utf-8").strip(), td[3].get_text().encode("utf-8").strip() ] table.append(col1) table.append(col2) if self.name == "10年期国债" or self.name == "2年期国债" or self.name == "5年期国债": VolumeMultiple = 10000 LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[1][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[6][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[11][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[3][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[9][1]] tempEndDate = table[5][1] tempEndDelivDate = table[7][1] else: VolumeMultiple = re.findall(r'\d+', table[2][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[12][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[6][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[9][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[1][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[7][1]] tempEndDate = table[3][1] tempEndDelivDate = table[5][1] Delivemonth = self.explainDelivemonth(table[8][1]) EndDate = self.explainEnddate(tempEndDate) EndDelivDate = self.explaintempEndDelivDate(tempEndDelivDate) return self.name, InstrumentCode, self.ExchangeID, VolumeMultiple, PriceTick, LimitUpDown, EndDate, EndDelivDate, MinMargin, DelivMethod, Delivemonth
def _ProductDetail__GetSHFEProductDetail(self): html = self.mysplider.getUrlcontent(self.Url, self.SHFEHeader) table = self.mysplider.tableTolist(html, self.ExchangeID) if len(table) > 6: VolumeMultiple = re.findall(r'\d+', table[1][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[4][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[3][1])[0] if self.name in ["白银", "黄金", "铅", "燃料油", "纸浆"]: InstrumentCode = BasicAPI().GetExchangeProductCode( table[14][1], self.ExchangeID) else: InstrumentCode = BasicAPI().GetExchangeProductCode( table[15][1], self.ExchangeID) # MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[11][1])[0]) / 100 if self.name in ["纸浆", "燃料油", "铅", "黄金", "白银"]: MinMargin = float( re.findall(r'([0-9.]+)[ ]*', table[11][1])[0]) / 100 else: MinMargin = float( re.findall(r'([0-9.]+)[ ]*', table[12][1])[0]) / 100 if self.name in ["白银", "黄金", "铅", "燃料油", "纸浆"]: DelivMethod = self.DeliveryMethod[table[12][1]] else: DelivMethod = self.DeliveryMethod[table[13][1]] Delivemonth = table[5][1] EndDate = self.explainEnddate(table[7][1]) tempEndDelivDate = table[8][1] if self.name == "黄金": Delivemonth = "3&13/2" else: Delivemonth = self.explainDelivemonth(Delivemonth) EndDelivDate = self.explaintempEndDelivDate(tempEndDelivDate) else: if self.name in self.imgContract: # temp=InfoApi().GetContractTable(self.ExchangeID,self.InstrumentCode) temp = self.GetContractTable() VolumeMultiple = temp[3] LimitUpDown = temp[5] PriceTick = temp[4] InstrumentCode = temp[1] MinMargin = temp[8] DelivMethod = temp[9] EndDate = temp[6] EndDelivDate = temp[7] Delivemonth = temp[10] return self.name, InstrumentCode, self.ExchangeID, VolumeMultiple, PriceTick, LimitUpDown, EndDate, EndDelivDate, MinMargin, DelivMethod, Delivemonth
def _ProductDetail__GetDCEProductDetail(self): html = self.mysplider.getUrlcontent(self.Url, self.DCEHeader) table = self.mysplider.tableTolistP(html, self.ExchangeID) if self.name == "玉米": VolumeMultiple = re.findall(r'\d+', table[1][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[4][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[3][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[13][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[11][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[12][1]] EndDate = (re.findall( ur'[一二三四五六七八九十]+|[0-9]+', table[8][1].decode("utf-8"))[0]).encode("utf-8") tempEndDate = table[7][1] tempEndDelivDate = table[8][1] Delivemonth = table[5][1] else: VolumeMultiple = re.findall(r'\d+', table[1][1])[0] LimitUpDown = float(re.findall(r'([0-9.]+)[ ]*', table[4][1])[0]) / 100 PriceTick = re.findall(r'([0-9.]+)[ ]*', table[3][1])[0] InstrumentCode = BasicAPI().GetExchangeProductCode( table[13][1], self.ExchangeID) MinMargin = float(re.findall(r'([0-9.]+)[ ]*', table[11][1])[0]) / 100 DelivMethod = self.DeliveryMethod[table[12][1]] EndDate = (re.findall( ur'[一二三四五六七八九十]+|[0-9]+', table[7][1].decode("utf-8"))[0]).encode("utf-8") tempEndDate = table[7][1] tempEndDelivDate = table[8][1] Delivemonth = table[5][1] EndDate = self.explainEnddate(tempEndDate) EndDelivDate = self.explaintempEndDelivDate(tempEndDelivDate) Delivemonth = self.explainDelivemonth(Delivemonth) return self.name, InstrumentCode, self.ExchangeID, VolumeMultiple, PriceTick, LimitUpDown, EndDate, EndDelivDate, MinMargin, DelivMethod, Delivemonth
def abstractData(self, td, data): t = dict() if td in LimitUpDown: Limitupdown = float(re.findall(r'([0-9.]+)[ ]*', data)[0]) / 100 # if float(Limitupdown)==0.0: # Limitupdown = re.findall(r'([0-9.]+)[ ]*%', data) t["LimitUpDown"] = Limitupdown return t elif td in InstrumentCODE: InstrumentCode = BasicAPI().GetExchangeProductCode( data, self.ExchangeID) t["TradeCode"] = InstrumentCode return t elif td in VolumeMULTIPLE: print "data", data VolumeMultiple = re.findall(r'\d+', data)[0] print "VolumeMultiple", VolumeMultiple
def GetAllTradeInstrumentByTradingDay(self, tradingDay): """获取某一个交易日的所有正在交易的合约""" if tradingDay in self.tradingDayInstrument.keys(): return self.tradingDayInstrument[tradingDay] else: self.tradingDayInstrument = dict() tradingDay = datetime.datetime.strptime(tradingDay, "%Y%m%d") if self.mysql is None: self.GetDbHistoryConnect() tempfuture = {} templist = self.mysql.ExecQuery( "select [TradeCode],[ExchangeID],[ProductName] from [PreTrade].[dbo].[StandContract] order by ExchangeID" ) templist = BasicAPI().GetResultList(templist) for i in templist: InstrumentId = i[0] ExchangeId = i[1] temp = self.Get_BasicApi().GetInstrumentMonth( self, InstrumentId, ExchangeId, tradingDay) for i in temp: tempfuture[i] = ExchangeId self.tradingDayInstrument[tradingDay.strftime( "%Y%m%d")] = tempfuture return tempfuture
InstrumentName = str(i.get_text().encode("utf-8")).strip()[:-6] print InstrumentName href = url + i['href'] if not basic.StrinList(InstrumentName, existlist): mysql.ExecNonQuery(insertsql % (ExchangeID, InstrumentName, href)) else: mysql.ExecNonQuery(updatesql % (href, InstrumentName)) if __name__ == '__main__': print """你确定要执行此脚本?执行完成后还要修改相应交易时间""" # raise Exception Instrument = {} temp = {} info = InfoApi() basic = BasicAPI() mysplider = MySplider() account = info.GetDbHistoryDataAccount() mysql = Mysql(account[0], account[1], account[2], account[3]) existsql = "select [InstrumentName] from [PreTrade].[dbo].[ContractCode]" insertsql = "insert into [PreTrade].[dbo].[ContractCode](ExchangeID,[InstrumentName],[Website]) values('%s','%s','%s')" updatesql = "update [PreTrade].[dbo].[ContractCode] set [Website]='%s' where InstrumentName='%s'" existlist = mysql.ExecQueryGetList(existsql) ExchangeList = info.GetAllExchange() for i in ExchangeList: url = info.GetExchangeWebsite(i) if i == 'CZCE': GetCZCEProduct(mysplider, info, i) # elif i=='DCE': # GetDCEProduct(mysplider, info, i) # elif i=='SHFE':
DelivMethod = self.DeliveryMethod[table[13][1]] EndDate = self.explainEnddate(tempEndDate) EndDelivDate = self.explaintempEndDelivDate(tempEndDelivDate) Delivemonth = self.explainDelivemonth(Delivemonth) return self.name, InstrumentCode, self.ExchangeID, VolumeMultiple, PriceTick, LimitUpDown, EndDate, EndDelivDate, MinMargin, DelivMethod, Delivemonth if __name__ == '__main__': sql = "INSERT INTO [dbo].[StandContract]([ProductName],[TradeCode],[ExchangeID] ,[VolumeMultiple],[PriceTick] ,[LimitUpDown],[EndDate],[EndDelivDate],[MinMargin],[DelivMethod],[Delivemonth])VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" selectsql = "select InstrumentName,ExchangeID,InstrumentCode,[Website] FROM [PreTrade].[dbo].[ContractCode] order by ExchangeID" existsql = "SELECT [ProductName] FROM [PreTrade].[dbo].[StandContract]" updatesql = "update [PreTrade].[dbo].[StandContract] set VolumeMultiple='%s',PriceTick='%s',LimitUpDown='%s',EndDate='%s',EndDelivDate='%s',MinMargin='%s',DelivMethod='%s',Delivemonth='%s' where ProductName='%s'" delsql = "delete from [PreTrade].[dbo].[StandContract]" info = InfoApi() templist = info.mysql.ExecQuery(selectsql) existlist = info.mysql.ExecQuery(existsql) existlist = BasicAPI().GetResultList(existlist) tempcol = [] for i in templist: print i[0].encode("utf-8") product = ProductDetail() col = product.GetProductDetail(i[3].encode("utf-8"), i[1].encode("utf-8"), i[0].encode("utf-8"), i[2].encode("utf-8")) print col tempcol.append(col) BasicAPI().MyPrint(tempcol) if len(tempcol) == len(templist) and len(tempcol) > 55: info.mysql.ExecNonQuery(delsql) info.mysql.ExecmanysNonQuery(sql, tempcol)
def main(info): columnsIndex=dict() nowTime="20190227" now=datetime.datetime.strptime(nowTime,"%Y%m%d") filename=str(now.strftime("%Y%m%d")+'交易日历'+".xls").strip() basicapi=BasicAPI() largmonth="".join(list(basicapi.GetInstrumentMonth(info,'sc','SHFE',datetime.datetime.now())[-1:][0])[-2:]) largyear= "".join(list(datetime.datetime.now().strftime("%Y%m%d"))[:2])+"".join(list(basicapi.GetInstrumentMonth(info, 'sc', 'SHFE',datetime.datetime.now())[-1:][0])[-4:-2]) print largmonth,largyear largMonthList =GetContinueM(largyear,largmonth) # print largMonthList columns=["交易所","交易品种","名称"]+largMonthList style=MyXlwt().GetMystyleTitle() redstyle=MyXlwt().GetstyleRed() # filename=datetime.datetime.now().strftime("%Y%m%d")+u"交易日历"+".xlsx" templist = info.mysql.ExecQuery("select [TradeCode],[ExchangeID],[ProductName] from [PreTrade].[dbo].[StandContract] order by ExchangeID") templist = BasicAPI().GetResultList(templist) wbk = xlwt.Workbook(encoding="utf-8") sheet = wbk.add_sheet('sheet Test') sheet.row(0).height_mismatch = True sheet.row(0).height = 1000 sheet.col(0).width = (20 * 240) # indexing is zero based, row then column InstrumentIDList = info.mysql.ExecQueryGetList( "select [InstrumentID] FROM [PreTrade].[dbo].[SettlementInfo] where TradingDay='%s' and [IsFuture]='1'"%now.strftime("%Y-%m-%d")) for i in range(len(columns)): sheet.row(i).height_mismatch = True sheet.row(i).height = 1000 sheet.col(i).width = (20 * 240) sheet.write(0,i,columns[i],style) columnsIndex[columns[i]]=i print columnsIndex for i in range(len(templist)): sheet.row(i+1).height_mismatch = True sheet.row(i+1).height = 1000 sheet.col(i+1).width = (20 * 240) InstrumentId=templist[i][0] if InstrumentId=='fu': pass ExchangeId=templist[i][1] ProductName=templist[i][2] temp = BasicAPI().GetInstrumentMonth(info, InstrumentId, ExchangeId,datetime.datetime.now()) sheet.write(i+1, 0, ExchangeId, style) sheet.write(i+1, 1, InstrumentId, style) sheet.write(i+1, 2, ProductName, style) # sheet.handle_writeaccess(temp) if set(temp) <= set(InstrumentIDList): InstrumentIDList = list(set(InstrumentIDList) - set(temp)) else: print temp print "**********************************" lastCode = temp[0] for j in temp: print "j",j EndDate=info.basicapi.Get_EndDate(info).GetEndDate(j)[0] InstrumentIdtemp=GetIndexColumns(j,ExchangeId,nowTime,columnsIndex) if j!=temp[0]: content = str(j + "\r\n" +EndDate) sheet.write(i + 1,columnsIndex[InstrumentIdtemp],content, style) else: day = BasicAPI().Get_EndDate(info).GetEndDate(lastCode)[0] day = (datetime.datetime.strptime(day, "%Y%m%d") - datetime.datetime.now()).days content=str(j+"\r\n"+"合约交易倒计时:"+str(day) +"天").strip() print content if day<=30: sheet.write(i + 1, columnsIndex[InstrumentIdtemp], content, redstyle) fyear,fmonth=info.basicapi.GetInstrumentYearMonth(j,ExchangeId) days_num = calendar.monthrange(int(fyear), int(fmonth))[1] tempfuture = BasicAPI().GetInstrumentMonth(info, InstrumentId, ExchangeId,now) for FInstr in set(set(tempfuture)-set(temp)): if FInstr=='jd2001': pass print "***********------------------------------------**********" startday = info.basicapi.Get_EndDate(info).GetStartDay(lastCode) print "FInstr",FInstr InstrumentIdtemp = GetIndexColumns(FInstr, ExchangeId, nowTime, columnsIndex) day = (datetime.datetime.strptime(startday, "%Y%m%d") - datetime.datetime.now()).days content = str(FInstr + "\r\n" + "合约挂牌交易倒计时:" + str(day) + "天"+str(startday)).strip() sheet.write(i + 1, columnsIndex[InstrumentIdtemp], content, redstyle) print FInstr,"合约挂牌交易倒计时:", day, "天" break else: sheet.write(i + 1, columnsIndex[InstrumentIdtemp], content, style) # day = BasicAPI().Get_EndDate(info).GetEndDate(lastCode)[0] # day = (datetime.datetime.strptime(day, "%Y%m%d") - now).days # print lastCode # if day <= 100: # print "合约倒计时:", day, "天" print "----------------------------------------------------------" print InstrumentIDList wbk.save(filename.decode('utf-8'))
def Get_BasicApi(self): self.basicapi = BasicAPI() return self.basicapi
class InfoApi: def __init__(self): self.HistoryDbconn = None self.StaticDbconn = None self.setting = setting() self.mysql = self.GetDbHistoryConnect() self.mysql1 = None self.mysplider = None self.basicapi = None """to save tradingDay's Instrument """ self.tradingDayInstrument = dict() self.NameCode = dict() """Memory to Save all kinds Data""" self.tempdata = dict() self.cleanDatadict = list() self.mydate = MyDate() self.PositionTop20InstrumentID = dict() def Get_Msplider(self): self.mysplider = MySplider() return self.mysplider def Set_QryPosition(self, ExchangeID, url, code, InstrumentID, TradingDay): self.QryPositionExchangeID = ExchangeID self.QryPositionurl = url self.QryPositionCode = code self.QryPositionInstrumentID = InstrumentID self.QryPositionTradingDay = TradingDay def Set_StagePosition(self, ExchangeID, Surl, code, begin, end): self.StagePositionExchangeID = ExchangeID self.StagePositionurl = Surl self.StagePositionCode = code self.StagePositionBeginTime = begin self.StagePositionEndTime = end def Get_BasicApi(self): self.basicapi = BasicAPI() return self.basicapi def GetDbHistoryDataAccount(self): """ 依次返回历史行情数据库地址,账号,密码,数据库名字 :return: """ return self.setting.HistoryDbInfo() def GetDbHistoryConnect(self): """ 获取历史行情数据库链接 :return: """ temp = self.setting.HistoryDbInfo() self.HistoryDbconn = SqlServer(temp[0], temp[1], temp[2], temp[3]) self.mysql = self.HistoryDbconn return self.mysql def DisConnectDbHistory(self): self.HistoryDbconn = None self.mysql.Disconnect() self.mysql = None def GetStaticDataAccount(self): """ 依次返回盘后统计数据库地址,账号,密码,数据库名字 :return: """ return self.setting.StaticDbInfo() def GetStaticDataconnect(self): """ 返回统计数据库链接 :return: """ if self.StaticDbconn is None: temp = self.GetStaticDataAccount() self.StaticDbconn = Mysql(temp[0], temp[1], temp[2], temp[3]) self.mysql1 = self.StaticDbconn return self.mysql1 def DisConnectStaticData(self): self.StaticDbconn = None self.mysql1.Disconnect() self.mysql1 = None def GetHolidayList(self): """ 返回一个节假日的list,目前只有2017,2018和2019年 :return: """ return self.setting.GetHoliday() def GetExchangeWebsite(self, ExchangeID): """ 返回相应交易所的官网地址 :param ExchangeID: :return: """ try: return str(self.setting.GetExchangeWeb(ExchangeID)).strip() except: print "please enter right ExchangeID" def GetDetailByTradeCode(self, TradeCode): """获取标准合约,仅供合约信息表使用""" sql = "select [VolumeMultiple],[ProductName],[PriceTick] from [PreTrade].[dbo].[StandContract] where [TradeCode]='%s'" % TradeCode self.mysql = self.GetDbHistoryConnect() templist = self.mysql.ExecQuery(sql) return self.basicapi.GetResultList(templist)[0] def GetAnotherInstrumentByInstrument(self, instrumentID): """获取另一个正在交易的合约,去你补部分数据缺失的问题""" a = str(instrumentID).strip()[:2] sql = "select top 2 [InstrumentID] from [PreTrade].[dbo].[SettlementInfo] where InstrumentID like'" + a + "%' and [IsFuture]='1' order by TradingDay desc" if self.mysql is None: self.GetDbHistoryConnect() templist = self.mysql.ExecQueryGetList(sql) return templist[1] if templist[0] == instrumentID else templist[0] def GetRelaviteInstrumentBycode(self, code): """获取相关合约通过product code""" date = str(datetime.datetime.now().year) if self.mysql is None: self.GetDbHistoryConnect() ExchangeId = self.GetExchangeByCode(code) if ExchangeId == 'CZCE': return code + date[3] else: return code + date[2] def GetExchangeByCode(self, code): sql = "SELECT [ExchangeID] FROM [PreTrade].[dbo].[ContractCode] where InstrumentCode='%s'" % code if self.mysql is None: self.GetDbHistoryConnect() ExchangeID = self.mysql.ExecQueryGetList(sql) return ExchangeID[0] def GetAllTradeInstrumentByTradingDay(self, tradingDay): """获取某一个交易日的所有正在交易的合约""" if tradingDay in self.tradingDayInstrument.keys(): return self.tradingDayInstrument[tradingDay] else: self.tradingDayInstrument = dict() tradingDay = datetime.datetime.strptime(tradingDay, "%Y%m%d") if self.mysql is None: self.GetDbHistoryConnect() tempfuture = {} templist = self.mysql.ExecQuery( "select [TradeCode],[ExchangeID],[ProductName] from [PreTrade].[dbo].[StandContract] order by ExchangeID" ) templist = BasicAPI().GetResultList(templist) for i in templist: InstrumentId = i[0] ExchangeId = i[1] temp = self.Get_BasicApi().GetInstrumentMonth( self, InstrumentId, ExchangeId, tradingDay) for i in temp: tempfuture[i] = ExchangeId self.tradingDayInstrument[tradingDay.strftime( "%Y%m%d")] = tempfuture return tempfuture def GetCodeByName(self, name): """Get ProductCode by ProductNme""" sql = "select [InstrumentCode],[InstrumentName],ExchangeID from [ContractCode]" if len(self.NameCode) == 0: self.GetDbHistoryConnect() templist = self.mysql.ExecQuery(sql) for i in templist: self.NameCode[i[1].encode("utf-8")] = [ i[0].encode("utf-8"), i[2].encode("utf-8") ] return self.NameCode[name] def GetDetailByInstrumentID(self, instrumentID, ExchangeID): """通过合约代码获取code,以及年月""" code = str(re.match(r"\D+", instrumentID).group()) num = str(instrumentID).strip().replace(code, "") if ExchangeID == 'CZCE': nyear = datetime.datetime.now().year if str(nyear)[-1:] == '9': if num[:1] == '0': num = str(nyear + 1)[2:3] + str(num) else: num = str(nyear)[2:3] + str(num) return code, num def GetInstrumentIDByCodeAndDate(self, ExchangeID, code, date): """ 通过品种代码和年月日获取标准的交易合约,检验交易代码的大小写以及郑商所合约后面只保留三位数字(期货) :param code: ProductCode :param date:1904/1908 :return: """ if ExchangeID in ['CZCE', 'CFFEX']: code = str(code).strip().upper() elif ExchangeID in ['SHFE', 'DCE', 'INE']: ode = str(code).strip().lower() if ExchangeID == 'CZCE': date = ''.join(list(date)[1:4]) else: date = ''.join(list(date)[:4]) return code + date def GetMainInstrumentIdByProductCode(self, code, tradingday): """Get main InstrumentID by ProductCode """ relInstrument = self.GetRelaviteInstrumentBycode(code) relInstrument = " '" + relInstrument + "%'" Mainsql = "SELECT top 1 [InstrumentID] FROM [PreTrade].[dbo].[SettlementInfo] where InstrumentID like" + relInstrument + "and TradingDay='%s' order by Position desc" % tradingday if self.mysql is None: self.GetDbHistoryConnect() instrument = self.mysql.ExecQueryGetList(Mainsql) if len(instrument): return instrument[0] else: print "code or traingday error", code, tradingday raise Exception def GetAllExchange(self): """ 获取所有的交易所代码 :return: """ return self.setting.ExchangeList def GetExchangeHeader(self, ExchangeID): """ 返回header头部信息 :return: """ temp = {} temp['CZCE'] = { 'Connection': 'keep-alive', 'Cache-Control': 'max-age=0', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.75 Safari/537.36', 'Accept-Language': 'zh-CN,zh;q=0.9', 'Cookie': 'BIGipServerwww_cbd=842836160.23067.0000; JSESSIONID=kYM9bphG26lQ6p25GXxyVRbkFw815nqCmJmTYxzGxxxvJnVTblhb!-1757810877; TS014ada8c=0169c5aa326a6aa66d379d396a2033d86a84f272d2b5c2f5d7b7d668355885aa62597926b41ead5b31a9f62f3252ecfb1e2c33422b', 'Host': 'www.czce.com.cn', 'Pragma': 'no-cache', 'Upgrade-Insecure-Requests': '1' } temp['DCE'] = { 'Connection': 'keep-alive', 'Cache-Control': 'no-cache', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.84 Safari/537.36', 'Accept-Language': 'zh-CN,zh;q=0.9', 'Cookie': 'JSESSIONID=5C1180D80CDBC1F61E550F5B7B1F9864; WMONID=MxRKrfMhnv4; Hm_lvt_a50228174de2a93aee654389576b60fb=1534468904,1534468904', 'Host': 'www.dce.com.cn', 'Pragma': 'no-cache', 'Upgrade-Insecure-Requests': '1' } temp['SHFE'] = { 'Connection': 'keep-alive', 'Cache-Control': 'no-cache', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.84 Safari/537.36', 'Accept-Language': 'zh-CN,zh;q=0.9', 'Cookie': 'shfe_cookid=180824110220b34cbb20ae74edd52deea05ac6be61fd; shfe_fbl=2560*1440', 'Host': 'www.shfe.com.cn', 'Pragma': 'no-cache', 'Upgrade-Insecure-Requests': '1' } temp['CFFEX'] = { 'Connection': 'keep-alive', 'Cache-Control': 'no-cache', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.84 Safari/537.36', 'Accept-Language': 'zh-CN,zh;q=0.9', 'Host': 'www.cffex.com.cn', 'Pragma': 'no-cache', 'Upgrade-Insecure-Requests': '1' } return temp[ExchangeID] def GetExchangeProduct(self, ExchangeID): sql = "select [InstrumentCode] FROM [PreTrade].[dbo].[ContractCode] where ExchangeID='%s'" % ExchangeID if self.HistoryDbconn is None: self.GetDbHistoryConnect() templist = self.HistoryDbconn.ExecQueryGetList(sql) return templist def IsInstrumentMonth(self, Tradecode, month): """判断month是否为该品种的合约月份,格式:直接传入月份01/02/.../10/11/12""" pass sql = """select [Delivemonth] from [PreTrade].[dbo].[StandContract] where [TradeCode]='%s'""" % Tradecode temp = self.mysql.ExecQueryGetList(sql) if temp.find("|") != -1: temp = temp.split("|") temp = map(lambda x: x.zfill(2), temp) if month in temp: return True elif temp.find("&"): pass def GetFutureInstrumentLits(self, a): """获取tradingday当日的数据库里面的SettlementInfo的交易的合约""" tempdict = {} a = a[:4].zfill(4) + "-" + a[5:6].zfill(2) + "-" + a[-2:].zfill(2) sql = """select [InstrumentID],[ExchangeID] from [PreTrade].[dbo].[SettlementInfo] where TradingDay='%s' and [IsFuture]='1' order by ExchangeID""" % a if self.mysql is None: self.GetDbHistoryConnect() tlist = self.mysql.ExecQuery(sql) for i in tlist: tempdict[i[0].encode("utf-8")] = i[1].encode("utf-8") return tempdict def GetAllProduct(self): """获取所有的期货品种名称""" sql = "select [InstrumentName] FROM [PreTrade].[dbo].[ContractCode] order by ExchangeID" mysql = self.GetDbHistoryConnect() templist = mysql.ExecQuery(sql) return self.Get_BasicApi().GetResultList(templist) def GetChineseToEnglish(self): temp = { "交易所": "ExchangeID", "交易代码": "InstrumentCode", "最小变动价位": "PriceTick", "合约乘数": "VolumeMultiple", "合约月份": "InstrumentMonth", "最低交易保证金": "MinMargin", "最后交易日": "" } def GetPositionTop20InstrumentID(self): """获取交易所持仓top20 合约以及对应的交易所""" sql = "SELECT distinct [InstrumentID],[ExchangeID] FROM [PreTrade].[dbo].[Position_Top20]" if self.mysql is None: self.GetDbHistoryConnect() tlist = self.mysql.ExecQuery(sql) for i in tlist: self.PositionTop20InstrumentID[i[0].encode("utf-8")] = i[1].encode( "utf-8") def Get2Listfromsql(self, sql): """存数据库中获取数据到二维list""" templist = [] if self.mysql is None: self.GetDbHistoryConnect() tlist = self.mysql.ExecQuery(sql) for i in tlist: col = [] for j in i: col.append(j) templist.append(col) return templist