zhmg23

我们是如此的不同
资深运维工程师互荐群: 102123162

通过Python操作SQLServer数据库

问题:因业务需要,需要根据一批号码,去查询SQLserver数据库,如果一个号码一个号码的查询,太费事,当然,可以把这些号码导入到数据库的一个临时表,然后in 去查询,这也是一个办法!但我们用python,来快速批量执行这个查询!

# -*- coding:utf-8 -*-
import pymssql
import time
import os
import sys
import datetime

class MSSQL:
def __init__(self,host,user,pwd,db):
self.host = "192.168.5.3"
self.user = "sa"
self.pwd = "password"
self.db = "tpdb"

def __GetConnect(self):
if not self.db:
raise(NameError,"没有设置数据库信息")
self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
cur = self.conn.cursor()
if not cur:
raise(NameError,"连接数据库失败")
else:
return cur

def ExecQuery(self,sql):
cur = self.__GetConnect()
cur.execute(sql)
resList = cur.fetchall()

#查询完毕后必须关闭连接
#self.conn.close()
return resList

def ExecNonQuery(self,sql):
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()

#从D:\tel.txt中获取查询条件的变量
with open("D:\\tel.txt","r") as f:
data = f.readlines()
ms = MSSQL(host="192.168.5.3",user="sa",pwd="password",db="tpdb")

for i in data:
reslist = ms.ExecQuery("select status,LastTime from UARPge where caller='%s'" %i.strip('\n'))
with open("D:\\data.txt","a+") as f:

for item in reslist:
f.write("%s,%s\n" %(item[0],item[1].strftime("%Y-%m-%d %H:%M:%S")))

说明:执行完成后,查询结果会生成在D:\data.txt

            windows下需要安装pymssql模块


参考:http://lovesoo.org/python-example-sqlserver.html

评论

热度(1)