Python连接SQL Server数据库

一、安装环境

服务器：Windows Server 2016

Python版本：Python 3.8.5

服务器本地安装有SQL Server 2008 R2

PYMSSQL版本：2.1.5

 

二、在python中安装pymssql

D:\Python\Python38>pip install pymssql
Collecting pymssql
  Downloading pymssql-2.1.5-cp38-cp38-win_amd64.whl (423 kB)
     |████████████████████████████████| 423 kB 547 kB/s
Installing collected packages: pymssql
Successfully installed pymssql-2.1.5

　　

三、用py程序测试连接

编写以下testMSSQL.py代码进行测试：

import pymssql

conn = pymssql.connect('(local)', 'sa', '123456', 'dbname')  #建立连接
if conn:
    print("连接成功!")

cursor = conn.cursor()
cursor.execute("select * from t_User")
for row in cursor:
	print("UID=%d, ID=%s, Name=%s" % (row[0], row[1], row[2]))


cursor.close()
conn.close()

　　

四、可能遇到的问题

由于pymssql的版本升级，可能导致连接发生错误，报错如下：

File "src\\pymssql\\_pymssql.pyx", line 653, in pymssql._pymssql.connect
pymssql._pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (localhost)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (localhost)\n')

首先编写一个py文件查询当前安装的pymssql版本：

import pymssql
print(pymssql.__version__)

运行后显示版本信息为：2.2.11

参考在笔记编写时，安装的pymssql版本为2.1.5，因此做降版本处理：

# 卸载2.2.11版
>pip uninstall pymssql
Found existing installation: pymssql 2.2.11
Uninstalling pymssql-2.2.11:
  Would remove:
    d:\python\python38\lib\site-packages\pymssql-2.2.11.dist-info\*
    d:\python\python38\lib\site-packages\pymssql\*
Proceed (Y/n)? y
  Successfully uninstalled pymssql-2.2.11

# 安装2.1.5版
>pip install pymssql==2.1.5
WARNING: Ignoring invalid distribution -ip (d:\python\python38\lib\site-packages)
Collecting pymssql==2.1.5
  Downloading pymssql-2.1.5-cp38-cp38-win_amd64.whl (423 kB)
     ---------------------------------------- 423.4/423.4 kB 38.3 kB/s eta 0:00:00
WARNING: Ignoring invalid distribution -ip (d:\python\python38\lib\site-packages)
Installing collected packages: pymssql
Successfully installed pymssql-2.1.5

再次运行测试文件，连接成功。