Python 使用 pymssql 连接 SQL Server 报错：DB-Lib error message 20002, severity 9

原创

Lorin 洛林

修改于 2024-01-03 19:07:53

9090

修改于 2024-01-03 19:07:53

文章被收录于专栏：Python 技术小屋Python 技术小屋

版本说明

Python 3.8
SQL Server 2008
pymssql 2.2.11

排查过程

最近给一个学妹看一个 Python 使用 pymssql 连接 SQL Server 报错问题，具体报错信息如下：

Error: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (127.0.0.1)\nDB-Lib error message 20002,
severity 9:\nAdaptive Server connection failed (127.0.0.1)\n')

学妹本地使用 SQL Server 客户端是可以连接上的，但是使用代码就是连接不上，为了方便测试，我先写一个简单的测试代码：

import pymssql

# 尝试连接数据库
try:
    # 建立连接
    connection = pymssql.connect('127.0.0.1','xjy_0513', '12345678', '学生住宿服务系统')
    # 获取游标
    cursor = connection.cursor()

    # 执行 SQL 查询或操作

    # 提交事务（如果有更改）
    connection.commit()

except pymssql.Error as e:
    print(f"Error: {e}")
finally:
    # 关闭连接
    if connection:
        connection.close()

考虑到目前的可能性，大致怀疑是学妹电脑网络配置的问题，然后一顿搜索这个问题，网络、字符集都尝试了还是无法解决问题，最终在一个角落看到一个可以打印详细连接过程的方式：

import pymssql
import os
os.environ['TDSDUMP'] = 'stdout' # 用于打印连接详细过程

# 尝试连接数据库
try:
    # 建立连接
    connection = pymssql.connect('127.0.0.1','xjy_0513', '12345678', '学生住宿服务系统')
    # 获取游标
    cursor = connection.cursor()

    # 执行 SQL 查询或操作

    # 提交事务（如果有更改）
    connection.commit()

except pymssql.Error as e:
    print(f"Error: {e}")
finally:
    # 关闭连接
    if connection:
        connection.close()

阅读整个连接详细日志，我发现了一些关键信息，以下是截取的部分：

log.c:187:Starting log file for FreeTDS 1.4.9
        on 2023-12-26 22:12:39 with debug flags 0x4fff.
dblib.c:1187:tdsdbopen(00000235D7C34090, 127.0.0.1:1433, [microsoft])
dblib.c:1213:tdsdbopen: dbproc->dbopts = 00000235D7B91F60
dblib.c:1224:tdsdbopen: tds_set_server(00000235D7BAAF60, "127.0.0.1:1433")
dblib.c:251:dblib_get_tds_ctx(void)
dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
config.c:170:Getting connection information for [127.0.0.1:1433].
// 貌似一个配置文件找不到 但是网上好像都没有提到过这个配置文件
config.c:174:Attempting to read conf files.
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open 'C:\Users\lenovo\AppData\Roaming/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open 'c:\freetds.conf' ((default)).
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open 'C:\Users\lenovo\AppData\Roaming/.freetds.conf' ((.freetds.conf)).
config.c:301:Could not open 'c:\freetds.conf' ((default)).
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
config.c:725:tds_config_login: client_charset is UTF-8.
config.c:734:tds_config_login: database_name is 瀛︾敓浣忓鏈嶅姟绯荤粺.
config.c:839:Setting 'dump_file' to 'stdout' from $TDSDUMP.
dblib.c:1268:tdsdbopen: Calling tds_connect_and_login(00000235D7BF2D20, 00000235D9B6AEA0)
iconv.c:371:tds_iconv_open(00000235D9B3C450, UTF-8, 1)
iconv.c:356:Using trivial iconv
iconv.c:202:local name for ISO-8859-1 is ISO-8859-1
iconv.c:202:local name for UTF-8 is UTF-8
iconv.c:202:local name for UCS-2LE is UCS-2LE
iconv.c:202:local name for UCS-2BE is UCS-2BE
iconv.c:390:setting up conversions for client charset "UTF-8"
iconv.c:392:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:431:tds_iconv_open: done
net.c:391:Connecting with protocol version 7.4
net.c:318:Connecting to 127.0.0.1 port 1433
"et.c:340:tds_setup_socket: connect(2) returned "无法立即完成一个非阻止性套接字操作。

.....省略

dblib.c:6088:dbfreebuf(00000235D7B91B90)
dblib.c:739:dbloginfree(00000235D7C34090)
Error: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (127.0.0.1)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (127.0.0.1)\n')
Traceback (most recent call last):

阅读 pymssql 开发文档发现，pymssql uses FreeTDS package to connect to SQL Server instances ，因此需要配置 FreeTDS:
完整说明：pymssql uses FreeTDS package to connect to SQL Server instances. You have to tell it how to find your database servers. The most basic info is host name, port number, and protocol version to use.The system-wide FreeTDS configuration file is /etc/freetds.conf or C:\freetds.conf, depending upon your system. It is also possible to use a user specific configuration file, which is $HOME/.freetds.conf on Linux and %APPDATA%\.freetds.conf on Windows. Suggested contents to start with is at least:

[global]
    port = 1433
    tds version = 7.0

在对应路径上添加 FreeTDS 配置文件，运行成功，再次观察运行日志正确查找到了配置文件。

log.c:187:Starting log file for FreeTDS 1.4.9
        on 2023-12-26 22:14:14 with debug flags 0x4fff.
dblib.c:1187:tdsdbopen(000002CC6963F000, 127.0.0.1:1433, [microsoft])
dblib.c:1213:tdsdbopen: dbproc->dbopts = 000002CC6B626980
dblib.c:1224:tdsdbopen: tds_set_server(000002CC695BBB30, "127.0.0.1:1433")
dblib.c:251:dblib_get_tds_ctx(void)
dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
config.c:170:Getting connection information for [127.0.0.1:1433].
config.c:174:Attempting to read conf files.
config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
config.c:375:... $FREETDS not set.  Trying $HOME.
config.c:301:Could not open 'C:\Users\lenovo\AppData\Roaming/.freetds.conf' ((.freetds.conf)).
config.c:305:Found conf file 'c:\freetds.conf' (default).
config.c:513:Looking for section global.
config.c:572:   Found section global.
config.c:575:Got a match.
config.c:598:   port = '1433'
config.c:598:   tds version = '7.0'
config.c:957:Setting tds version to 7.0 (0x700).

....省略

dblib.c:236:dblib_del_connection(00007FFD05304C90, 000002CC69658880)
query.c:3804:tds_disconnect()
util.c:179:Changed query state from IDLE to DEAD
mem.c:665:tds_free_all_results()
dblib.c:278:dblib_release_tds_ctx(1)
dblib.c:6088:dbfreebuf(000002CC6B6265B0)