zhmg23

通过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模块

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