1 库文件
访问mssql需要使用pymssql,安装命令
pip install pymssql
原来安装过旧版本,每次运行提示
Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated since
此问题是警告,可以忽略。但是看着不舒服,升级为最新版本即可;
pip install --upgrade pymssql
2 打开远程访问
执行代码时,会提示如下问题:
pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\.
导致不能正确运行,肯定是找不多服务器的原因;但是SQL Server 2008 R2 Express在开始菜单中没有“SQL Server 配置管理器”,晕。用命令行搞定:
SQLServerManager10.msc
启动TCP/IP,重新启动访问,记下动态端口号。
3 访问程序
直接上代码,两种初始化方式。
import pymssql
class MssqlBase:
host = 'localhost'
server = r'.\Express'
port = 51648
user = 'sa'
pwd = '123456'
db = 'ttt'
# def __init__(self, host, server, port, user, pwd, db):
# self.host = host
# self.server = server
# self.port = port
# self.user = user
# self.pwd = pwd
# self.db = db
def __GetConnect(self):
"""
得到连接信息
"""
if not self.db:
raise(NameError,"没有设置数据库信息")
self.conn = pymssql.connect(host=self.host, server=self.server, port=self.port, 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):
"""
执行查询语句
返回的是一个包含tuple的list,list的元素是记录行,tuple的元素是每行记录的字段
"""
cur = self.__GetConnect()
cur.execute(sql)
res_list = cur.fetchall()
# 查询完毕后必须关闭连接
self.conn.close()
return res_list
def ExecNonQuery(self, sql):
"""
执行非查询语句
"""
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
if __name__ == '__main__':
# ms=MssqlBase(host="localhost", server=".\SQLExpress", port="51648", user="sa", pwd="123456", db="ttt")
ms=MssqlBase()
resList = ms.ExecQuery("SELECT * FROM students")
print(resList)