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