Python连接SQL Server实战：pymssql配置、踩坑与性能优化

简介：一份围绕Python连接MSSQL数据库的pymssql库基础教程PDF，面向需要快速上手Python操作SQL Server的开发者与运维人员。教程从pymssql.connect建立连接讲起，依次介绍cursor()创建游标、execute与executemany执行SQL、fetchone与fetchall获取结果，并专门解释“同一连接下多个游标同时查询时，第二个游标会覆盖第一个游标结果”的典型问题，给出创建多个连接或先fetchall再执行新查询两种规避方法。在此基础上，还演示了as_dict=True让返回行以字典形式呈现、通过with语句自动管理连接与游标、利用callproc调用存储过程等实战技巧，每个知识点均配有可直接运行的代码示例与输出说明，方便读者边看边练。资源为单个PDF文件，仅58KB，可离线快速查阅。目前已有501人学习下载，适合刚接触pymssql的初学者对照示例完成从建表、插入、查询到调用存储过程的完整实践。

1. 先用三分钟说清楚：Python连Mssql为什么绕不开pymssql

接手过旧业务系统的人大多遇到过这种场景：数据库用的是Mssql，开发环境却只有Python。翻遍连接MySQL的现成代码，发现用pymysql根本连不上去，搜索引擎给出的答案里，出现频率最高的就是pymssql。这个库本质上是FreeTDS的Python封装，装上后Python就能走TDS协议和Mssql实例通信，做查询、写表、调存储过程都够用。这篇文章按照我习惯的落地顺序展开：先给最小可运行脚本，再讲连接参数的取舍，最后把乱码、超时、误用这些踩过的坑一条条摆出来。适合手里有一台Mssql实例、想用Python做报表、ETL、数据修复的开发者和运维同学。

2. 装好pymssql并跑通最小查询：从pip到第一条SELECT

2.1 安装前先想清楚：pymssql对运行环境的两点要求

用pip安装pymssql之前，我先说明一个容易忽略的事实：pymssql不是纯Python库，它底层依赖FreeTDS这套C库。Windows平台pip会直接装带FreeTDS的wheel包，基本开箱即用；Linux发行版下如果系统缺少FreeTDS运行时，装完pymssql后import就会报libsybdb.so: cannot open shared object file。这类错误不是pymssql本身装坏了，而是系统动态库路径里没有FreeTDS。

所以我一般会在虚拟环境里按下面这套命令操作，保证每一步都可复现：

python -m venv .venv source .venv/bin/activate # Windows 下换成 .venv\Scripts\activate pip install --upgrade pip pip install pymssql

装完先做一个快速自检：在Python解释器里import pymssql，能正常导入说明FreeTDS动态库已经就位。如果这里报缺库错误，在Ubuntu/Debian上执行sudo apt install freetds-dev，CentOS/RHEL上执行sudo yum install freetds-devel，再重新执行最后两步安装命令。这里的freetds-dev包里包含客户端运行库和头文件，而freetds-devel对应Red Hat系。我个人的习惯是装完不管系统包还是pip包，都用pip show pymssql看一眼版本和安装路径，避免多环境混用PIP目录导致的版本错乱。

2.2 第一条可执行脚本：连接、查询、关闭

安装通过后，我把最小可用脚本固定为下面这个模板，业务上连接任意一个Mssql实例，改四个参数就能跑：

import pymssql conn = pymssql.connect( server="192.168.1.10", # Mssql 实例地址 user="report_user", # 数据库登录名 password="YourPass123", # 密码，建议改用环境变量读取 database="report_db", # 要连接的数据库名 charset="utf-8", # 客户端字符集，中文库常用 gbk login_timeout=10, # 登录超时，单位秒 ) cursor = conn.cursor() cursor.execute("SELECT TOP 5 order_no, amount FROM orders ORDER BY order_date DESC") rows = cursor.fetchall() for row in rows: print(row) cursor.close() conn.close()

这段代码的逻辑链路很简单：connect建立连接对象，cursor创建游标，execute把SQL发给服务端执行，fetchall取回全部结果集，最后关闭游标和连接。注意server参数里不用写端口，默认会连1433端口；如果实例端口是14330这种非默认端口，写法是server="192.168.1.10,14330"，中间是英文逗号而不是冒号，这是pymssql最常见的书写错误之一。

参数层面，charset="utf-8"对应SQL Server里排序规则为Latin1_General_100_CI_AS等场景下的通用读取方案，线上的老库如果是GBK中文编码，这里就要改成charset="gbk"。login_timeout我习惯设成10秒，避免网络不通时脚本卡住两分钟才报错。代码里的密码硬编码是我演示用的写法，实际项目里请用环境变量，例如password=os.environ["DB_PASSWORD"]，防止脚本被提交到Git仓库后泄露。

2.3 游标和结果读取：别只会fetchall

fetchall适合数据量几百行的小查询，Mssql里一张几百万行的表直接fetchall，轻则内存涨到几百MB，重则撑爆Python进程。我推荐的读取方式是分批拉取，pymssql的游标对象提供了fetchone和fetchmany两种接口：

cursor.execute("SELECT order_no, amount FROM orders WHERE order_date >= '2024-01-01'") while True: rows = cursor.fetchmany(size=500) if not rows: break for row in rows: # 每条订单按业务逻辑处理 print(row[0], row[1])

用fetchmany(size=500)后，游标每次只从服务端拿500行，内存占用被压得很稳。还有一个相对不那么广为人知的参数是as_dict，在创建游标时传入conn.cursor(as_dict=True)，返回的结果不再是元组而是字典，字段名直接作为键名。下面这段代码展示了字典游标的用法：

cursor = conn.cursor(as_dict=True) cursor.execute("SELECT TOP 3 order_no, amount FROM orders") for row in cursor.fetchall(): print(row["order_no"], row["amount"])

字典模式的优点是代码里写row["order_no"]比row[0]可读性好很多，尤其当SELECT列很多、字段顺序不好记时，能省去数索引位置的功夫。缺点也很明显：每行多维护一个字典结构，同样的数据量下内存比元组模式高一些，百万行以上的结果集不建议用as_dict。日常开发里我一般这样取舍：临时查数用元组模式，写进正式报表脚本时用as_dict模式，便于后续维护。

3. 连接参数怎么设：charset、tds_version与autocommit的取舍

3.1 charset和tds_version是中文乱码的源头

很多第一次连接Mssql的同学都有过这种经历：SQL写对了，库也连上了，读出来的中文却是一串问号，往数据库里插中文再查出来也变成???。这类问题八成出在charset参数上。pymssql默认的charset是UTF-8，但Mssql里存中文的字段排序规则可能是Chinese_PRC_CI_AS，对应的实际编码体系是GBK。两边字符集不一致，服务端把GBK字节按UTF-8解释，自然就乱了。

我的处理方式是先确认数据库的实际排序规则，再决定charset取值。执行一句SQL就可以看清楚：

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS collation_name;

如果返回结果里带Chinese_PRC字样，连接参数里就写charset="gbk"；如果是Latin1_General这类排序规则且存的是UTF-8数据，就用charset="utf-8"。这里特别提醒一点：charset只影响pymssql客户端和FreeTDS之间的字符转换，改变charset不会自动转换数据库里已存的乱码字节，所以先测试查询再决定，不要上来就全局改。

再说tds_version参数。TDS是SQL Server使用的数据流协议，版本越新支持的SQL Server特性越完整。pymssql大部分场景下不设置这个参数也能自动协商到可用版本，但遇到两种情形需要显式指定：一是连接极老的Mssql实例时自动协商可能失败，二是新版本SQL Server上某些数据类型读出来异常。常见的做法是：

conn = pymssql.connect( server="192.168.1.10", user="report_user", password="YourPass123", database="report_db", charset="gbk", tds_version="7.3", # 兼容性出问题时显式指定协议版本 )

tds_version可填的值从7.0到7.4不等，具体能用哪个取决于FreeTDS编译时支持的版本。我个人的习惯：先不加这个参数，只有出现“某个列读出来类型不对”或“连接报协议错误”时才尝试逐档下调，找到能稳定工作的最低稳定版本。这不并是玄学，协议版本协商失败的坑在ODBC时代就存在，只是pymssql把它隐藏得比较深。

3.2 超时参数与appname：脚本排障的两块垫脚石

连接Mssql的脚本跑在定时任务里时，最怕故障单元卡死整个调度。pymssql提供两个超时控制参数：login_timeout控制建连阶段最长等待时间，query_timeout控制一次execute执行的最长时间，单位都是秒。

举个例子，一个报表查询平时3秒出结果，某天Mssql产生阻塞导致查询跑了一分多钟。如果不设query_timeout，这个Python进程会一直挂在那；设了query_timeout=30，30秒后pymssql会抛异常并中断当前查询，调度系统就能捕获这个异常并告警。

conn = pymssql.connect( server="192.168.1.10", user="report_user", password="YourPass123", database="report_db", login_timeout=10, # 建连超时10秒 query_timeout=30, # 单条SQL执行超时30秒 appname="report_etl_01", # 应用名，DBA排查用 )

这里多加了一个appname参数。设置后，数据库的活动监视器里就能看到这台连接的应用名是report_etl_01，而不是千篇一律的客户端驱动名。某次线上数据库负载高，DBA直接通过应用名定位到是我这边一个死循环脚本在反复查询，省去了逐条排查连接的痛苦。

结合本小节内容，我把常用连接参数整理如下，方便照抄：

3.3 autocommit的默认值与事务边界

pymssql的autocommit默认是False，这意味着所有写操作都需要显式调用conn.commit()才会真正落库。很多新手踩过的坑是：INSERT跑完了，脚本也正常退出，数据却没了。原因就是事务没有提交，连接关闭时事务被隐式回滚。

我写写数据的脚本时必用下面这个事务模板：

conn = pymssql.connect( server="192.168.1.10", user="report_user", password="YourPass123", database="report_db", autocommit=False, # 显式关闭自动提交，手动控制事务 ) cursor = conn.cursor() try: cursor.execute("INSERT INTO orders(order_no, amount) VALUES('A001', 100)") cursor.execute("INSERT INTO order_items(order_no, item_no) VALUES('A001', 1)") conn.commit() # 两条插入都成功后一起提交 except Exception as e: conn.rollback() # 任何一条失败则全部回滚 raise e finally: cursor.close() conn.close()

这里的关键在于commit和rollback成对出现。多条写操作在同一个事务里，要么全成功，要么全失败，不会出现主表插入了明细表没插入的数据脏状态。autocommit=True的情况我只用于纯查询场景，它能让每条SELECT执行完自动开启新事务，减少长事务对数据库产生的锁压力。还有一个小习惯供参考：写操作结束后打一条SELECT COUNT(*)确认影响行数，再决定要不要commit，防止业务逻辑漏处理导致的半截数据入库。

4. pymssql踩坑记录：乱码、连不上、锁等待与身份列误用

4.1 中文乱码：插入变成??，查询变成问号

• 现象：往Mssql表里插入中文，Python端不报错，数据库里却存成了??；查询读出来也是问号。
• 原因：连接参数charset与数据库排序规则不一致。Mssql的Chinese_PRC_CI_AS排序规则按GBK系编码处理中文，而pymssql默认用UTF-8与服务端交互，字节流两边对不上。
• 解决：先查排序规则，然后按前面第3章的方法设置charset="gbk"。如果库表本身已经是Latin1_General但字段能正确显示中文，考虑改用charset="utf-8"并检查查询入口是否用了N前缀。这里多说一句：修改charset只对新建连接生效，改了以后要重启长时间运行的脚本进程。

4.2 连接报DB-Lib错误或直接timeout

• 现象：pymssql报DB-Lib error message 20018, severity 14，或者等待一段时间后报timed out。
• 原因：多半是网络端口不通、SQL Server没开启TCP/IP协议、连接字符串端口分隔符写错。20018是FreeTDS层面的通用网络错误，它不告诉你具体原因，只看错误文本很容易被误导。
• 解决：先用telnet 192.168.1.10 1433确认端口通不通；不通就去SQL Server配置管理器把TCP/IP协议启用，并确认服务正在监听1433端口。通了以后检查server参数，确保是ip,端口的逗号格式，而不是ip:端口。

4.3 插入自增列报错：Cannot insert explicit value for identity column

• 现象：往一张有IDENTITY自增主键的表里插入数据，SQL里没写主键列也没事，一旦代码里把主键列也放进了INSERT列表，pymssql就报错：Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF。
• 原因：SQL Server默认不允许向IDENTITY列显式写值，只有显式开启SET IDENTITY_INSERT 表名 ON才允许。
• 解决：INSERT语句里删掉主键列，让数据库自己生成自增值。如果确实需要保留旧主键做数据迁移，则在同一个连接上先执行SET IDENTITY_INSERT table_name ON，插入完成后再执行SET IDENTITY_INSERT table_name OFF，注意这个设置只对当前会话生效。

4.4 查询大表卡死，连接被锁等待

• 现象：用pymssql执行一条SELECT，平时秒回，某天突然卡住不动，执行到query_timeout后才报查询超时。去数据库里一查，这条SELECT被其他事务阻塞。
• 原因：Mssql默认的隔离级别下，写事务未提交时，读操作会被阻塞等待。pymssql若开着事务不提交，也会持有锁，反过来阻塞别人的读写。
• 解决：读报表场景把连接设为autocommit=True；写事务脚本里缩短事务窗口，尽量不在事务中做额外计算。对于实时性要求不高、只做离线查询的分析脚本，SQL里加WITH(NOLOCK)提示也能绕开阻塞，但要清楚这会读到未提交的脏数据。我负责的报表任务只对汇总表加NOLOCK，明细表一律保持默认隔离级别，防止脏读影响后续数据核对。

4.5 datetime和decimal读出来类型不符合预期

• 现象：Mssql里的datetime列读出来是datetime.datetime没问题，但decimal(18,2)列读出来变成一个奇怪的Decimal对象，直接拿去算百分比报类型异常。
• 原因：pymssql基于FreeTDS的TDS协议返回类型，部分版本下DECIMAL/NUMERIC会被包装成Decimal实例，和Python标准库的decimal.Decimal不完全一致。不同FreeTDS版本对相同类型的映射也有细微差异。
• 解决：不要在游标返回的字段类型上做依赖，所有需要参与计算的数值统一在Python侧套一层float()或str()转换。日期字段同理，统一str(row["create_time"])格式化后再展示。这种转类型的小代码看起来啰嗦，但能规避底层驱动升级导致的隐性类型变化。

5. 把pymssql用顺手：上下文管理器、批量写入与连接验证

5.1 封装一个自动提交的连接上下文

每次写查询脚本都手动close连接、在异常时rollback，代码显得很啰嗦。我常用的做法是用自定义上下文管理器包一层，把提交和关闭都收口：

from contextlib import contextmanager @contextmanager def mssql_conn(server, user, password, database, **kwargs): conn = pymssql.connect( server=server, user=user, password=password, database=database, **kwargs ) try: yield conn conn.commit() except Exception: conn.rollback() raise finally: conn.close()

用法上，查询脚本这样写就行：

with mssql_conn("192.168.1.10", "u", "p", "report_db") as conn: cur = conn.cursor() cur.execute("SELECT COUNT(*) FROM orders") print(cur.fetchone()[0])

这套封装的意义在于：读多写少时，commit可以统一兜底；异常时rollback也能自动执行，不用每个业务函数里都重复一遍事务逻辑。

5.2 executemany批量写入

往Mssql批量灌数据时，逐条execute会频繁往返服务端，几千行的数据可能要等几十秒。pymssql提供executemany接口，可以一次调用执行多条插入：

with mssql_conn("192.168.1.10", "u", "p", "report_db") as conn: cur = conn.cursor() rows = [ ("A001", 100), ("A002", 200), ("A003", 300), ] cur.executemany( "INSERT INTO orders(order_no, amount) VALUES(%s, %s)", rows, )

注意这里的占位符是%s，不是Mssql习惯里的?号。executemany内部会复用同一条预处理语句，批量提交时比循环execute快一个数量级。我拿2000行数据做过对比，逐条execute耗时约4秒，executemany在毫秒级完成，差距非常明显。

5.3 连接验证三件套

每次新环境部署脚本时，我都会在正式跑任务前执行一段验证SQL，确认连的是预期实例、预期库和预期版本：

cur.execute("SELECT @@SERVERNAME, DB_NAME(), @@VERSION") row = cur.fetchone() print("实例名:", row[0]) print("当前库:", row[1]) print("版本:", row[2])

这条SQL能一次验证连接目标、默认数据库和SQL Server大版本。多实例环境里，曾经因为连接字符串指向了错误的实例，导致报表数据一直对不上账，最后就是靠@@SERVERNAME定位出来的。

最后说一个我自己早年写Mssql脚本时的教训：数据量一大就想用fetchall一把梭，结果内存爆掉，连接被数据库端断开，重跑又因为前一次事务没提交锁了新数据。后来养成的固定习惯是——所有取数脚本一律fetchmany，所有写操作脚本落库前打一条影响行数确认。这套习惯沿用到现在，几乎没有再因为连接和事务问题翻过车。希望帮到你。

本文还有配套的精品资源，点击获取