#!/usr/bin/env python#-*- coding: UTF-8 -*-
importpymssqlclassMSSQL(object):'''对pymssql的简单封装
pymssql库,该库到这里下载:http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
使用该库时,需要在Sql Server Configuration Manager里面将TCP/IP协议开启
用法:'''
def __init__(self,host,user,pwd,db):
self.host=host
self.user=user
self.pwd=pwd
self.db=dbdefGetConnect(self):'''得到链接信息
:return:'''
if notself.db:raise (NameError,"没有设置数据库信息")
self.connect=pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
cur=self.connect.cursor()if notcur:raise (NameError,"链接数据库失败")else:returncurdefExecQuery(self,sql):'''执行查询语句
返回的是一个包含tuple的list,list的元素是记录行,tuple的元素是每行记录的字段
调用示例:
ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
for (id,NickName) in resList:
print str(id),NickName
:param sql: sql语句
:return:'''cur=self.GetConnect()
cur.execute(sql)
resList=cur.fetchall()#查询完毕后必须关闭连接
self.connect.close()returnresListdefExecNonQuery(self, sql):"""执行非查询语句
调用示例:
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()"""cur= self.__GetConnect()
cur.execute(sql)
self.connect.commit()
self.connect.close()deftest():## ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
## #返回的是一个包含tuple的list,list的元素是记录行,tuple的元素是每行记录的字段
## ms.ExecNonQuery("insert into WeiBoUser values('2','3')")
ms= MSSQL(host="172.16.2.248", user="sa", pwd="qazwsx", db="cytest")
resList= ms.ExecQuery("SELECT * FROM t_user")print(resList)if __name__ == '__main__':
test()