Python MSSQL.insert_sql方法代码示例

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
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

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
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)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
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

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
def write_heart(type,name):
    ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    sql="update [LogRecord].[dbo].[quotes_python_heart] set [updatetime]=getdate() where type='%s' and name='%s' and [isactive]=1" % (type,name)
    ms.insert_sql(sql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
		timeinfo=ms.dict_sql(sql)[0]
		if timeinfo['stockdate'] is None:
			lastrecordtime=datetime.datetime(2015,01,01,01,00)
		else:
			lastrecordtime=timeinfo['stockdate']

		insertvalue=""
		numofinsert=0
		for item in mymewquote:
			if item[0]>lastrecordtime:
				insertvalue=insertvalue+","+"('%s','%s',0,%s,'%s',%s,%s)" % (ac,symbol,item[1],item[0],item[2],totalsum)
				numofinsert=numofinsert+1
			if numofinsert>700:
				insertvalue=insertvalue.strip(',')
				sql="insert into [Future].[dbo].[quanyi_log_groupby_v2](ac,symbol,type,ClosePrice,stockdate,totalposition,totalNum) values%s" % insertvalue
				ms.insert_sql(sql)
				numofinsert=0
				insertvalue=""


		if insertvalue !="":
			insertvalue=insertvalue.strip(',')
			sql="insert into [Future].[dbo].[quanyi_log_groupby_v2](ac,symbol,type,ClosePrice,stockdate,totalposition,totalNum) values%s" % insertvalue
			ms.insert_sql(sql)
			# print sql
		##--end
		return mymewquote,totalsum
		
	else:
		return 0,0.0001
		

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]

targetfilename=add_data_wenhua(_dfrom,date, timestart, timeend,mysymbol )  #  extrct  data from timestart to timeend


#按顺序往不同数据库写入
try:
    print "write into 05"
    ms05 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    read_date_write_to_database_night(ms05,targetfilename,date,mysymbol) # targetfile=r'Y:\data_wenhua\20160427_0_2359.csv'
    read_date_write_to_database_day(ms05,targetfilename,date,mysymbol)
except Exception,e:
    msg="05数据库 "+str(e).replace("'",'#')
    sql="insert into [LogRecord].[dbo].[report_daily_error](type,msg) values('%s','%s')" % ('定时补全数据',msg)
    ms05 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    ms05.insert_sql(sql)

try:
    print "write into 03"
    ms03 = MSSQL(host="192.168.0.3",user="future",pwd="[email protected]",db="future")
    read_date_write_to_database_night(ms03,targetfilename,date,mysymbol) # targetfile=r'Y:\data_wenhua\20160427_0_2359.csv'
    read_date_write_to_database_day(ms03,targetfilename,date,mysymbol)
except Exception,e:
    msg="03数据库 "+str(e).replace("'",'#')
    sql="insert into [LogRecord].[dbo].[report_daily_error](type,msg) values('%s','%s')" %('定时补全数据',msg)
    ms05 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
    ms05.insert_sql(sql)

try:
    print "write into 07"
    ms07 = MSSQL(host="192.168.0.7",user="future",pwd="[email protected]",db="future")

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
def cal_position_lilun():
	#1 put lilun equity into account_position_lilun,添加不存在报警机制
	ms = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future") 
	sql="truncate table [LogRecord].[dbo].account_position_lilun"
	ms.insert_sql(sql)
	sql="select distinct userid from [LogRecord].[dbo].[account_position]  where userid<>'05810058' order by userid"
	res=ms.dict_sql(sql)
	totalsql=""
	for item in res:
		userid=item['userid']
		ispass=0
		try:
			sql="select 1 from Fun_account_position_all_V2('%s')" % (userid)
			ms.insert_sql(sql)
			ispass=1
		except:
			sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime],beizhu) values('%s',0,'%s',getdate(),'%s')" % (userid,0,'No view in 0.5 database')
			ms.insert_sql(sql)
		if ispass==1:
			tempsql="select '%s' as [userID],STOCK as [stockID],Expr1 as position,GETDATE() as nowtime from Fun_account_position_all_V2('%s')" % (userid,userid)
			totalsql=totalsql+" union all "+tempsql
	totalsql=totalsql.strip(" union all ")
	totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) "+ totalsql
	ms.insert_sql(totalsql)

	#2 shangpin yingshe
	totalsql=""
	ms1 = MSSQL(host="139.196.104.105",user="future",pwd="[email protected]",db="Future")
	res=['05810058']
	for item in res:
		userid=item
		ispass=0
		try:
			sql="select 1 from future.dbo.view_%s" % (userid)
			ms1.insert_sql(sql)
			ispass=1
		except:
			sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime],beizhu) values('%s',0,'%s',getdate(),'%s')" % (userid,0,'No view in 0.5 database')
			print sql
			ms.insert_sql(sql)
		if ispass==1:
			tempsql="select '%s' as [userID],STOCK as [stockID],Expr1 as position,GETDATE() as inserttime from future.dbo.view_%s" % (userid,userid)
			totalsql=totalsql+" union all "+tempsql
	totalsql=totalsql.strip(" union all ")
	#totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) "+ totalsql
	tempres=ms1.dict_sql(totalsql)

	#3 stock yinghse
	sql="SELECT [Symbol]  ,[S_ID] FROM [Future].[dbo].[Symbol_ID] where Symbol in ('IC','IF','IH','TF','T')"
	res=ms.dict_sql(sql)
	symboldict={}
	for item in res:
		symboldict[item['Symbol']]=item['S_ID']
	sql="SELECT a.account,a.symbol,sum(a.ratio*b.position ) as Position  FROM [future].[dbo].[account_position_stock_yingshe] a left join [future].[dbo].[RealPosition] b on a.acanme=b.Name group by a.account,a.symbol having a.account not in ('666061008')"
	res=ms1.dict_sql(sql)
	for item in res:
		#print item 
		symbol_id=symboldict[item['symbol']]
		sql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) values('%s','%s','%s',getdate())" % (item['account'],symbol_id,item['Position'])
		#print sql 
		ms.insert_sql(sql)

	totalsql=""
	tempv=""
	for item in tempres:
		tempv=",('%s','%s','%s','%s')" % (item['userID'],item['stockID'],int(item['position']),item['inserttime'].strftime("%Y-%m-%d %H:%M:%S"))
		totalsql=totalsql+tempv
	totalsql=totalsql.strip(",")
	if len(totalsql)>10:
		totalsql="insert into [LogRecord].[dbo].account_position_lilun([userID],[stockID],[position],[inserttime]) values%s" % (totalsql)
		print totalsql
		ms.insert_sql(totalsql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
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
			temp="('%s',%s,%s,%s,%s,%s,%s,'%s','%s','%s')" % (symbol,row[2],row[5],row[3],row[4],row[6],row[7],D,T,stockdate)
			totalstr=totalstr+","+temp
			i=i+1
			if i>900:
				totalstr=totalstr.strip(",")
				totalstr=str1+totalstr
				ms.insert_sql(totalstr)
				totalstr=""
				i=0
	if len(totalstr)>20:
		totalstr=totalstr.strip(",")
		totalstr=str1+totalstr
		ms.insert_sql(totalstr)
		totalstr=""	

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
import datetime
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 -*-


dateroot=r'Y:\data_yangzhen\to_huangcheng\future_all'
filename='1m_20170201.csv'

with open(dateroot+"\\"+filename) as fh:
	content=csv.reader(fh)
	totalsql=""
	i=0
	for row in content:
		i=i+1
		stockdate=datetime.datetime.strptime(row[1],'%Y%m%d%H%M%S')
		sql="insert into [LogRecord].[dbo].[every_con_close]([con] ,[stockdate]   ,[C]) values('%s','%s',%s)" % (row[0],stockdate,row[2])
		totalsql=totalsql+sql
		if i>900:
			print i
			ms.insert_sql(totalsql)
			totalsql=""
			i=0
	if len(totalsql)>10:
		ms.insert_sql(totalsql)
sql="update top(500000) LogRecord.dbo.[every_con_close] set symbol=Future.dbo.m_getstr(con) where symbol is null"
ms.insert_sql(sql)

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
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

# 需要导入模块: from dbconn import MSSQL [as 别名]
# 或者: from dbconn.MSSQL import insert_sql [as 别名]
try:
	ms07 = MSSQL(host="192.168.0.7",user="future",pwd="[email protected]",db="future")
except:
	pass

# resList = ms.find_sql("select top 2 * from st_report")
# print resList
# -*- coding: utf-8 -*-

try:
	cmd='sudo ntpdate pool.ntp.org'
	os.system(cmd)
except:
	pass
print 'Next task'



try:
	sql="delete from [Future].[dbo].[TSymbol] where DATEDIFF(day,[StockDate],GETDATE())>130"
	ms03.insert_sql(sql)
	ms07.insert_sql(sql)
	# mscloud.insert_sql(sql)
except Exception,e:
	ms1 = MSSQL(host="192.168.0.5",user="future",pwd="[email protected]",db="future")
	subject="清理Tsymbol定时任务出错"+datetime.datetime.now().strftime("%H:%M:%S")
	mailtolist="[email protected]"
	message=str(e).replace("'",'#')
	sendmessage="13764504303"
	sql="insert into [LogRecord].[dbo].[maillist](subject,mailtolist,msg,type,inserttime,sendmessage) values('%s','%s','%s',%s,getdate(),'%s')" % (subject,mailtolist,message,0,sendmessage)
	ms1.insert_sql(sql)