本文整理汇总了Python中dbconn.MSSQL类的典型用法代码示例。如果您正苦于以下问题：Python MSSQL类的具体用法？Python MSSQL怎么用？Python MSSQL使用的例子？那么, 这里精选的类代码示例或许可以为您提供帮助。
在下文中一共展示了MSSQL类的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: order_get_ac_ratio_three
def order_get_ac_ratio_three(account):
#获取总账户配置的虚拟组的ratio
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
#sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM [LogRecord].[dbo].[order_p_follow] WHERE ac='%s' UNION ALL SELECT D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM Emp INNER JOIN [LogRecord].[dbo].[order_p_follow] d ON d.ac = Emp.F_ac)SELECT AC,f_AC,ratio FROM Emp" % (account)
#sql="select AC,f_AC,ratio from [LogRecord].[dbo].[order_p_follow] where ac='%s'" % (account)
#检测是否自循环
sql="select * from [Future].[dbo].[p_follow] where ac=F_ac and ac='%s'" % (account)
res=ms.dict_sql(sql)
if res:
return []
sql="WITH Emp AS ( SELECT ac,F_ac,ratio,stock FROM [Future].[dbo].[p_follow] WHERE ac='%s' UNION ALL SELECT D.AC,D.F_ac,D.ratio*emp.ratio/100,D.stock FROM Emp INNER JOIN [Future].[dbo].[p_follow] d ON d.ac = Emp.F_ac) select '%s' as AC,f_AC+'__'+stock as f_AC,SUM(ratio) as ratio from Emp where f_ac not in (select ac from Emp) and ratio<>0 group by F_ac,stock order by F_ac" % (account,account)
#sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM [Future].[dbo].[p_follow] WHERE ac='%s' UNION ALL SELECT D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM Emp INNER JOIN [Future].[dbo].[p_follow] d ON d.ac = Emp.F_ac) select '%s' as AC,f_AC,SUM(ratio) as ratio from Emp where f_ac not in (select ac from Emp) and ratio<>0 group by F_ac" % (account,account)
res=ms.dict_sql(sql)
accountlist=[]
aclist=[]
for item in res:
accountlist.append(item['AC'])
aclist.append(item['f_AC'])
accountlist=list(set(accountlist))
aclist=list(set(aclist))
ac_ratio={}
for item in aclist:
ac_ratio[item]=0
for item in res:
ac_ratio[item['f_AC']]=ac_ratio[item['f_AC']]+item['ratio']
# print ac_ratio
#删除基本账户信息
for key in accountlist:
if ac_ratio.has_key(key):
del ac_ratio[key]
# print ac_ratio
return ac_ratio示例2: get_dailyquanyi
def get_dailyquanyi(account,fromDdy):
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
ac_ratio=get_ac_ratio(account)
totalquanyi=[]
for key in ac_ratio:
ratio=ac_ratio[key]
if ratio>0:
sql="SELECT [quanyisymbol] FROM [LogRecord].[dbo].[quanyicaculatelist] where acname='%s'" % (key)
res=ms.dict_sql(sql)
if not res:
# print {"ispass":0,"result":"%s does not has equity" % (key)}
return {"ispass":0,"result":"%s does not has equity" % (key)}
else:
symbol=res[0]['quanyisymbol']
acname=key
sql="select top 1 D,quanyi as quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (acname,symbol,fromDdy)
tempres=ms.find_sql(sql)
if tempres==[]:
initoalquanyi=0
else:
initoalquanyi=tempres[0][1]
sql="select D,(quanyi-%s) as quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (initoalquanyi,acname,symbol,fromDdy)
res1=ms.find_sql(sql)
#乘以ratio
newres1=[]
for item in res1:
newres1.append([item[0],item[1]*ratio/10.0])
totalquanyi=add_time_series(totalquanyi,newres1)
totalquanyi=sorted(totalquanyi,key=lambda a :a[0])
totalquanyi=[[item[1],item[0]] for item in totalquanyi]
# for item in totalquanyi:
# print item
return {"ispass":1,"result":totalquanyi}示例3: read_date_write_to_database_day
def read_date_write_to_database_day(targetfile,date,mysymbol=''):
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
#date=20160428
date=str(date)
date=date[0:4]+'/'+date[4:6]+'/'+date[6:8]
print "Starting..."
file = open(targetfile)
successnum=0
while 1:
lines = file.readlines(100000)
if not lines:
break
for line in lines:
line=line.strip('\n')
linelist=line.split(',')
timeint=int(linelist[8][0:2])*100+int(linelist[8][3:])
if linelist[7]==date and (timeint>=900 and timeint<=1530) and (mysymbol=='' or mysymbol.lower()==linelist[0].lower()) and linelist[0] not in ('CMXGLD','[email protected]','BRENTOIL','CMXSLV','[email protected]'):
sql="insert into Tsymbol ([Symbol],[O],[C],[H],[L],[V],[OPI],[D],[T],[StockDate],[refc])values('%s',%s,%s,%s,%s,%s,%s,'%s','%s','%s',%s)" % (linelist[0],linelist[1],linelist[2],linelist[3],linelist[4],linelist[5],linelist[6],linelist[7],linelist[8],linelist[9],linelist[10])
try:
ms.insert_sql(sql)
successnum=successnum+1
print successnum
except Exception,e:
if "Cannot insert duplicate key row" in str(e):
pass
else:
break示例4: get_ac_ratio
def get_ac_ratio(account):
#获取总账户配置的虚拟组的ratio
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
sql="WITH Emp AS ( SELECT ac,F_ac,ratio FROM p_follow WHERE ac='%s' UNION ALL SELECT D.AC,D.F_ac,D.ratio*emp.ratio/100 FROM Emp INNER JOIN p_follow d ON d.ac = Emp.F_ac)SELECT AC,f_AC,ratio FROM Emp" % (account)
res=ms.dict_sql(sql)
accountlist=[]
aclist=[]
for item in res:
accountlist.append(item['AC'])
aclist.append(item['f_AC'])
accountlist=list(set(accountlist))
aclist=list(set(aclist))
# print accountlist
# print aclist
ac_ratio={}
for item in aclist:
ac_ratio[item]=0
for item in res:
ac_ratio[item['f_AC']]=ac_ratio[item['f_AC']]+item['ratio']
# print ac_ratio
for key in accountlist:
if ac_ratio.has_key(key):
del ac_ratio[key]
# print ac_ratio
return ac_ratio示例5: main_fun
def main_fun():
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
ms05 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
ms03 = MSSQL(host="192.168.0.3\SQLEXPRESS",user="future",pwd="[email protected]",db="future")
# get_Kbarinfo(ms05,15,'2016-12-13 10:00:00')
# get_Kbarinfo(ms03,15,'2016-12-13 10:00:00')
resultlist=[]
total_day_generate(fromdate='2016-11-01',interval=15,resultlist=resultlist)
sql=" select distinct top(60) stockdate from [Future].[dbo].[TSymbol_%smin] order by stockdate desc" % (15)
res=ms.dict_sql(sql)
firsttime=res[-1]['stockdate']
lasttime=res[0]['stockdate']
aaa=[item for item in resultlist if item >=firsttime ]
aaa=[item for item in aaa if item <=lasttime ]
for item in aaa:
print item
get_Kbarinfo(ms05,15,item)
get_Kbarinfo(ms03,15,item)
write_heart('Kbars','queren')示例6: analysis_st
def analysis_st(st,period):
print st
ms = MSSQL(host="192.168.0.5", user="future", pwd="[email protected]", db="future")
nowday=datetime.datetime.now().strftime('%y%m%d')
nowday=int(nowday)
sql="select * from real_st_report where st='%s' and D>='%s' order by stockdate,id" % (st,nowday)
res=ms.dict_sql(sql)
# drop replecate return [[p,stockdate],[],[]]
res=drop_replacate(res)
(num60_3,content)=analysis_result(res,60,3)
contentstr=','.join(content[:5])
contentstr=contentstr[:800]
print contentstr
(num120_3, content) = analysis_result(res, 120, 3)
(num120_4, content) = analysis_result(res, 120, 4)
(num120_5, content) = analysis_result(res, 120, 5)
aa= [st,num60_3,num120_3,num120_4,num120_5]
if aa<>[st,0,0,0,0]:
sql = "insert into [LogRecord].[dbo].[st_shandan_analysis](st,[s60_3],[s120_3],[s120_4],[s120_5],s60_content,[period]) values('%s',%s,%s,%s,%s,'%s','%s');" % (aa[0], aa[1], aa[2], aa[3], aa[4],contentstr,period)
ms.insert_sql(sql)示例7: monitor_add_errorinfo
def monitor_add_errorinfo(type,myitem):
#查询发件人
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
sql="select email from [LogRecord].[dbo].[mailtolist] where istomail=1"
reslist=ms.find_sql(sql)
mailtolist=''
sendmessage=''
for item in reslist:
if "@" in item[0]:
mailtolist=mailtolist+','+item[0]
else:
sendmessage=sendmessage+','+item[0]
mailtolist=mailtolist.strip(',')
sendmessage=sendmessage.strip(',')
# print mailtolist
#待检测的ABmachine列表
sql="select item,starttime,endtime from [LogRecord].[dbo].[monitorconfig] where type='%s' and item='%s' and ismonitor=1" % (type,myitem)
res=ms.dict_sql(sql)
for item in res:
symbol=item['item']
starttime=item['starttime']
endtime=item['endtime']
sql="select getdate()"
getnow=ms.find_sql(sql)[0][0]
nowtime=getnow.strftime('%H:%M:%S')
nowtime=datetime.datetime.strptime(nowtime,'%H:%M:%S')
starttime=datetime.datetime.strptime(starttime,'%H:%M:%S')
endtime=datetime.datetime.strptime(endtime,'%H:%M:%S')
if nowtime>starttime and nowtime<=endtime:
# print '报警'
# print "@@@@@@@@@@@@@[email protected]@@@@@@@@@@@@@"
subject='ctontab出错 %s' % (myitem)
msg=subject
sql="insert into [LogRecord].[dbo].[maillist](subject,mailtolist,msg,type,inserttime,sendmessage) values('%s','%s','%s',%s,getdate(),'%s')" % (subject,mailtolist,msg,0,sendmessage)
print sql
ms.insert_sql(sql)
break示例8: reload
#coding=utf-8
#!/usr/bin/env python
# pymssql
import sys
import pandas as pd
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
resList = ms.dict_sql("select top 2 * from st_report")
df1=pd.DataFrame(resList)
print df1
print resList
示例9: reload
#coding=utf-8
#!/usr/bin/env python
import os
import sys
import csv
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
# resList = ms.find_sql("select top 2 * from st_report")
# print resList
# -*- coding: utf-8 -*-
root1=r'C:\Users\YuYang\Documents\Tencent Files\794513386\FileRecv\future_index_shipan'
files=os.listdir(root1)
for item in files:
print item
symbol=item.split('.csv')[0].split('_zs')[0]
symbol=symbol.upper()
content=csv.reader(open(root1+"\\"+item))
str1="insert into [TSymbol_allfuture]([Symbol] ,[O] ,[C] ,[H] ,[L] ,[V] ,[OPI] ,[D] ,[T] ,[StockDate]) values"
totalstr=""
i=0
for row in content:
if row[0]>='20161230' and row[0]<='20170112':
D=row[0][0:4]+"/"+row[0][4:6]+"/"+row[0][6:]
T=row[1][0:2]+":"+row[1][2:4]
stockdate=D+" "+T示例10: order_get_dailyquanyi_margin
def order_get_dailyquanyi_margin(account,fromDdy):
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
ac_ratio=order_get_ac_ratio_three(account)
totalquanyi=[]
#获取对齐时间
Dlist=[]
tempresult=""
configinfo=[]
Dlist.append(fromDdy)
if ac_ratio==[]:
return {"ispass":0,"result":"存在有自己跟随自己的配置，请修正"}
if ac_ratio=={}:
return {"ispass":0,"result":"P_follow中不存在相应配置，请确认是否正确"}
for key in ac_ratio:
realac=key.split("__")[0]
quanyisymbols_id=key.split("__")[-1]
sql="select top(1) [positionsymbol] from [LogRecord].[dbo].[quanyicaculatelist] where acname='%s'" % (realac)
res11=ms.dict_sql(sql)
if res11:
positionsymbol=res11[0]['positionsymbol']
else:
return {"ispass":0,"result":"虚拟组权益没有准备，请联系俞洋--%s" % (realac)}
sql="select a.acname,s.S_ID,s.Symbol from LogRecord.dbo.quanyicaculatelist a left join Symbol_ID s on a.quanyisymbol=s.Symbol where a.acname='%s' and s.S_ID='%s'" % (realac,quanyisymbols_id)
quanyisymbol=ms.dict_sql(sql)[0]['Symbol']
sql="SELECT top 1 (convert(int,replace(convert(varchar(10),DATEADD(day,1,stockdate),120),'-',''))-20000000) as D FROM [Future].[dbo].[quanyi_log_groupby_v2] where ac='%s' and symbol='%s' order by stockdate" % (realac,positionsymbol)
# or
sql="select top 1 D from dailyquanyi_V2 where ac='%s' and symbol='%s' and not (position=0 and quanyi=0 and times=0) order by D" % (realac,positionsymbol)
tempD=ms.dict_sql(sql)
if tempD:
Dlist.append(tempD[0]['D'])
configinfo.append([key,ac_ratio[key],tempD[0]['D']])
else:
Dlist.append(200000)
sql="select ac from [LogRecord].[dbo].[order_p_follow] where F_ac='%s' and stock='%s'" % (realac,quanyisymbol)
tempres=ms.dict_sql(sql)
tempresult=tempresult+" 基本账户 %s 中 %s 没有产生过信号,请补全近两年策略信号</br>" % (tempres[0]['ac'],key)
configinfo.append([key,ac_ratio[key],200000])
fromDdy=max(Dlist)
if 200000 in Dlist:
return {"ispass":0,"result":tempresult,"configinfo":configinfo}
for key in ac_ratio:
realac=key.split("__")[0]
quanyisymbols_id=key.split("__")[-1]
sql="select a.acname,s.S_ID,s.Symbol from LogRecord.dbo.quanyicaculatelist a left join Symbol_ID s on a.quanyisymbol=s.Symbol where a.acname='%s' and s.S_ID='%s'" % (realac,quanyisymbols_id)
quanyisymbol=ms.dict_sql(sql)[0]['Symbol']
ratio=ac_ratio[key]
if ratio<>0:
sql="SELECT [quanyisymbol] FROM [LogRecord].[dbo].[quanyicaculatelist] where acname='%s' and quanyisymbol='%s'" % (realac,quanyisymbol)
res=ms.dict_sql(sql)
if not res:
# print {"ispass":0,"result":"%s does not has equity" % (key)}
return {"ispass":0,"result":"%s 不在配置表 quanyicaculatelist 中，请加上并获得历史信号" % (key),"configinfo":configinfo}
else:
symbol=res[0]['quanyisymbol']
acname=realac
sql="select top 1 D,quanyi as quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (acname,symbol,fromDdy)
tempres=ms.find_sql(sql)
if tempres==[]:
initoalquanyi=0
else:
initoalquanyi=tempres[0][1]
initoalquanyi=0
sql="select D,(quanyi-(%s)) as quanyia from dailyquanyi_V2 where ac='%s' and symbol='%s' and D>=%s order by D" % (initoalquanyi,acname,symbol,fromDdy)
res1=ms.find_sql(sql)
#乘以ratio
newres1=[]
for item in res1:
newres1.append([item[0],item[1]*ratio/10.0])
totalquanyi=add_time_series(totalquanyi,newres1)
totalquanyi=sorted(totalquanyi,key=lambda a :a[0])
totalquanyi=[[item[1],item[0]] for item in totalquanyi]
# for item in totalquanyi:
# print item
return {"ispass":1,"result":totalquanyi,"configinfo":configinfo}示例11: reload
#coding=utf-8
#!/usr/bin/env python
import sys
import os
import csv
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
# resList = ms.find_sql("select top 2 * from st_report")
# print resList
# -*- coding: utf-8 -*-
root=r'C:\Users\YuYang\Desktop\20161107'
files=os.listdir(root)
for file in files:
conid=file.split('.csv')[0].split('_')[0]
day=file.split('.csv')[0].split('_')[1]
day=day[0:4]+'-'+day[4:6]+'-'+day[6:]
csvfile = open(root+"\\"+file, 'rb')
reader = csv.reader(csvfile)
tempsql=""
i=0
for line in reader:
datetime=day+' '+line[0]
ask=line[1]
askv=line[2]
bid=line[3]
bidv=line[4]
lastp=line[5]
cv=line[6]
opi=line[7]示例12: reload
# -*- coding: utf-8 -*-
#!/usr/bin/env python
import os, sys
from os.path import join, getsize, splitext, split
from ftplib import FTP
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
# resList = ms.find_sql("select top 2 * from st_report")
# print resList
##ftp下载数据
def ftp_down(dfrom,localf,filename = "histWhcj.txt"):
try:
ftp=FTP()
ftp.set_debuglevel(2)
ftp.connect('120.26.89.143','8021')
# pdb.set_trace()
# print '11111'
ftp.login('el','104104')
#print ftp.getwelcome()#显示ftp服务器欢迎信息
lf = str(localf)
ml = lf[-4:]
ftp.cwd(ml+'_1m/') #选择操作目录
# ftp.cwd(ml+'_1m(all_40cid)/') #选择操作目录
#0324_1m(all_40cid)
bufsize = 1024
localname = lf+".csv"
localname = open(os.path.join(dfrom,localname),'wb')
file_handler =localname.write #以写模式在本地打开文件 示例13: read_date_write_to_database_night
def read_date_write_to_database_night(targetfile,date,mysymbol=''):
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
#date=20160428
date=str(date)
date=date[0:4]+'/'+date[4:6]+'/'+date[6:8]
print "Starting..."
file = open(targetfile)
successnum=0
while 1:
lines = file.readlines(100000)
if not lines:
break
for line in lines:
line=line.strip('\n')
linelist=line.split(',')
isnight=0
timeint=int(linelist[8][0:2])*100+int(linelist[8][3:])
# print timeint
if ((timeint>=2100 and timeint<=2359) or (timeint>=0000 and timeint<=400)) and (mysymbol=='' or mysymbol.lower()==linelist[0].lower()) and linelist[0] not in ('CMXGLD','[email protected]','BRENTOIL','CMXSLV','[email protected]'):
if linelist[0]=='RB':
linelist[0]='RBnight'
isnight=1
if linelist[0]=='P':
linelist[0]='Rnight'
isnight=1
if linelist[0]=='RU':
linelist[0]='RUnight'
isnight=1
if linelist[0]=='TA':
linelist[0]='TAnight'
isnight=1
if linelist[0]=='NI':
linelist[0]='NInight'
isnight=1
if linelist[0]=='L':
linelist[0]='Lnight'
isnight=1
if linelist[0]=='J':
linelist[0]='Jnight'
isnight=1
if linelist[0]=='I':
linelist[0]='Inight'
isnight=1
if linelist[0]=='HC':
linelist[0]='HCnight'
isnight=1
if linelist[0]=='CS':
linelist[0]='CSnight'
isnight=1
if linelist[0]=='BU':
linelist[0]='BUnight'
isnight=1
if linelist[0]=='AL':
linelist[0]='ALnight'
isnight=1
if isnight==1:
sql="insert into Tsymbol ([Symbol],[O],[C],[H],[L],[V],[OPI],[D],[T],[StockDate],[refc])values('%s',%s,%s,%s,%s,%s,%s,'%s','%s','%s',%s)" % (linelist[0],linelist[1],linelist[2],linelist[3],linelist[4],linelist[5],linelist[6],linelist[7],linelist[8],linelist[9],linelist[10])
#print sql
try:
ms.insert_sql(sql)
successnum=successnum+1
print successnum
except Exception,e:
if "Cannot insert duplicate key row" in str(e):
pass
else:
break示例14: reload
#coding=utf-8
#!/usr/bin/env python
import sys, urllib, urllib2, json
import sys
import datetime
import time
import os
import numpy as np
import pandas as pd
#from openpyxl.writer.excel import ExcelWriter
from pandas.tseries import offsets
from pandas import Timestamp
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
#ms = MSSQL(host="27.115.14.62:3888",user="future",pwd="[email protected]",db="future")
# resList = ms.find_sql("select top 2 * from st_report")
# print resList
# -*- coding: utf-8 -*-
global lilun_total
global real_total
global lilun_total_ZL
global real_total_ZL
global totalroot
global totalresult_df1
global totalresult_df2
global totalresult_df3
示例15: reload
#coding=utf-8
#!/usr/bin/env python
import sys, urllib, urllib2, json
import sys
reload(sys)
sys.setdefaultencoding('utf8')
from dbconn import MSSQL
import pandas as pd
import datetime
from pandas.tseries import offsets
import numpy as np
import multiprocessing
ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
# 获取原始的仓位
def get_position(acname,symbol):
print acname,symbol
sql="select p.AC,p.P_size*a.ratio/100.0*s.P as p,s.ST as st,s.stockdate from P_BASIC p inner join AC_RATIO a on p.AC=a.AC and p.AC='%s' inner join st_report s on p.ST=s.ST order by stockdate asc,s.id asc" % (acname)
res=ms.dict_sql(sql)
deltepositionlist={}
laststlistposition={}
if len(res)>0:
for item in res:
if not deltepositionlist.has_key(item['stockdate']):
deltepositionlist[item['stockdate']]=0
if not laststlistposition.has_key(item['st']):
laststlistposition[item['st']]=0
deltepositionlist[item['stockdate']] = deltepositionlist[item['stockdate']] + (item['p'] - laststlistposition[item['st']])