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

版本阐明

• Python 3.8
  
• SQL Server 2008
  
• pymssql 2.2.11
  

排查过程

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

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

复制代码

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

1. import pymssql
3. # 尝试连接数据库
4. try:
5.     # 建立连接
6.     connection = pymssql.connect('127.0.0.1','xjy_0513', '12345678', '学生住宿服务系统')
7.     # 获取游标
8.     cursor = connection.cursor()
10.     # 执行 SQL 查询或操作
12.     # 提交事务（如果有更改）
13.     connection.commit()
15. except pymssql.Error as e:
16.     print(f"Error: {e}")
17. finally:
18.     # 关闭连接
19.     if connection:
20.         connection.close()

复制代码

• 考虑到现在的可能性，大抵怀疑是学妹电脑网络配置的题目，然后一顿搜刮这个题目，网络、字符集都尝试了还是无法办理题目，终极在一个角落看到一个可以打印具体连接过程的方式：
  

1. import pymssql
2. import os
3. os.environ['TDSDUMP'] = 'stdout' # 用于打印连接详细过程
5. # 尝试连接数据库
6. try:
7.     # 建立连接
8.     connection = pymssql.connect('127.0.0.1','xjy_0513', '12345678', '学生住宿服务系统')
9.     # 获取游标
10.     cursor = connection.cursor()
12.     # 执行 SQL 查询或操作
14.     # 提交事务（如果有更改）
15.     connection.commit()
17. except pymssql.Error as e:
18.     print(f"Error: {e}")
19. finally:
20.     # 关闭连接
21.     if connection:
22.         connection.close()

复制代码

• 阅读整个连接具体日志，我发现了一些关键信息，以下是截取的部门：
  

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

1. [global]
2.     port = 1433
3.     tds version = 7.0

复制代码

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

1. log.c:187:Starting log file for FreeTDS 1.4.9
2.         on 2023-12-26 22:14:14 with debug flags 0x4fff.
3. dblib.c:1187:tdsdbopen(000002CC6963F000, 127.0.0.1:1433, [microsoft])
4. dblib.c:1213:tdsdbopen: dbproc->dbopts = 000002CC6B626980
5. dblib.c:1224:tdsdbopen: tds_set_server(000002CC695BBB30, "127.0.0.1:1433")
6. dblib.c:251:dblib_get_tds_ctx(void)
7. dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
8. config.c:170:Getting connection information for [127.0.0.1:1433].
9. config.c:174:Attempting to read conf files.
10. config.c:362:... $FREETDSCONF not set.  Trying $FREETDS/etc.
11. config.c:375:... $FREETDS not set.  Trying $HOME.
12. config.c:301:Could not open 'C:\Users\lenovo\AppData\Roaming/.freetds.conf' ((.freetds.conf)).
13. config.c:305:Found conf file 'c:\freetds.conf' (default).
14. config.c:513:Looking for section global.
15. config.c:572:   Found section global.
16. config.c:575:Got a match.
17. config.c:598:   port = '1433'
18. config.c:598:   tds version = '7.0'
19. config.c:957:Setting tds version to 7.0 (0x700).
21. ....省略
23. dblib.c:236:dblib_del_connection(00007FFD05304C90, 000002CC69658880)
24. query.c:3804:tds_disconnect()
25. util.c:179:Changed query state from IDLE to DEAD
26. mem.c:665:tds_free_all_results()
27. dblib.c:278:dblib_release_tds_ctx(1)
28. dblib.c:6088:dbfreebuf(000002CC6B6265B0)

复制代码

参考

• https://www.pymssql.org/en/v2.1.2/freetds.html#configuration
  

个人简介