★ FEATURED ARTICLE 前言先说两点形式上的说明。第一本文不插图片连接数据库的每一步结果都是文字——驱动列表、报错信息、查询输出用代码块呈现比截图更精确也能直接复制。第二「SQL server」这个写法不严谨微软的正式产品名是Microsoft SQL Server下面统一按这个理解。连接本地 SQL Server 有两条主流路线选哪条决定了后面所有的写法路线依赖占位符特点pyodbcODBC 驱动如「ODBC Driver 18 for SQL Server」?走微软官方 ODBC 驱动功能与兼容性最好是推荐路线pymssqlFreeTDS%s纯依赖 FreeTDS部署时少装一个 ODBC 驱动但生态不如 ODBC 路线本篇以 pyodbc 为主线因为它直连微软官方驱动遇到问题时资料最全。还有一个必须先打预防针的坑「ODBC Driver 18 for SQL Server」把Encrypt的默认值从no改成了yes强制加密。这是 17 到 18 之间最著名的行为变化。后果是用 18 版驱动连本地实例时如果服务器用的是自签名证书连接会直接失败报证书验证错误。老教程里能跑的连接串换成 18 版驱动就跑不通原因就在这。解决办法放在第二节讲。一、环境确认装了哪些驱动在写任何代码之前先确认机器上有哪些 ODBC 驱动。# 适用于 Python 3.8需先 pip install pyodbcimport pyodbcprint(pyodbc 版本:, pyodbc.version)print(已安装的 ODBC 驱动:)for name in pyodbc.drivers():print( -, name)预期输出示例实际列表取决于你装了什么pyodbc 版本: 5.x.x已安装的 ODBC 驱动:- ODBC Driver 18 for SQL Server- SQL Server几个要点pyodbc.drivers()返回的是本机 ODBC 驱动名的列表代码里填的DRIVER{...}必须和这里打印出来的名字逐字一致包括版本号和空格。「SQL Server」这个驱动是 Windows 自带的旧版驱动很多年前就不再更新了。能用但不要在新项目里选它能用 18 就用 18。报「Data source name not found」或「驱动未找到」先跑上面这段代码而不是去猜驱动名拼错了没有。安装驱动有两种途径装Microsoft ODBC Driver for SQL Server推荐有独立安装包或者用微软官方文档里列出的命令行安装方式。具体安装步骤以微软官方文档为准本篇不展开。二、连接串每个键的含义连接串是「键值」用分号连接的字符串DRIVER的值要用花括号包起来。# 适用于 Python 3.8import osimport pyodbcconn_str (DRIVER{ODBC Driver 18 for SQL Server};SERVER127.0.0.1;DATABASEmaster;UIDsa;fPWD{os.environ[MSSQL_SA_PASSWORD]};TrustServerCertificateyes; # 本地自签名证书必加见下方说明Encryptyes;ConnectTimeout5;)conn pyodbc.connect(conn_str)try:version conn.cursor().execute(SELECT VERSION).fetchone()[0]print(连接成功服务端版本首行:, version.splitlines()[0])finally:conn.close()预期输出连接成功服务端版本首行: Microsoft SQL Server 2022 (RTM) - 16.0.x.x (X64)连接串常用键键含义本地连接的常见值DRIVERODBC 驱动名{ODBC Driver 18 for SQL Server}SERVER服务器地址127.0.0.1、localhost命名实例写.\SQLEXPRESSDATABASE初始数据库master或你的库名UID/PWDSQL Server 账号密码建议从环境变量读取别写源码里Trusted_Connection用 Windows 集成认证yes时不需要UID/PWDEncrypt是否加密连接18 版驱动默认yesTrustServerCertificate是否跳过证书校验本地自签名证书时设yesConnectTimeout连接超时秒数设 5~10避免长时间挂起关于Encrypt和TrustServerCertificate直接把官方文档的结论说清楚Encrypt版本 17 及更早默认是no版本 18.0 及以后默认是yes。18 还额外接受mandatory等价yes、optional等价no和strictTDS 8.0 加密17 没有对应值。加密和证书校验是两件事。「数据被加密传输」和「服务器证书被验证过」是独立的开关。本地开发实例通常用自签名证书客户端不认得颁发者于是加密开着、证书校验过不去连接失败。连接失败时的两条出路让客户端信任服务器证书TrustServerCertificateyes仅适用于你确定连的是自己机器的场景或者把加密关掉Encryptno。生产环境不要用这两条——应该给服务器配一张受信任的证书。如果把账号密码写进连接串字符串一定要从环境变量或受控配置里取不要把密码硬编码进源码。三、用 Windows 集成认证连接本地开发最常见的情形是「不建 SQL 账号直接用当前 Windows 用户登录」。这时把UID/PWD换成Trusted_Connection# 适用于 Python 3.8import pyodbcconn_str (DRIVER{ODBC Driver 18 for SQL Server};rSERVER.\SQLEXPRESS; # 命名实例默认实例写 localhostDATABASEAdventureWorks;Trusted_Connectionyes;TrustServerCertificateyes;ConnectTimeout5;)conn pyodbc.connect(conn_str)try:row conn.cursor().execute(SELECT SUSER_SNAME() AS login_name, DB_NAME() AS db_name).fetchone()print(以身份登录:, row.login_name, | 当前库:, row.db_name)finally:conn.close()预期输出以身份登录: DESKTOP-XXXX\Administrator | 当前库: AdventureWorks两个细节r.\SQLEXPRESS前面的r是原始字符串前缀避免\S被当成转义序列。这是 Windows 路径风格服务器名的常见书写方式。Trusted_Connectionyes和UID/PWD不要同时给。同时给的情况下行为取决于驱动版本容易出现「明明没传账号却报登录失败」这类迷惑现象。四、执行 SQLCRUD 与参数化pyodbc 的占位符是?参数是序列按位置绑定。# 适用于 Python 3.8import osimport pyodbcconn_str (DRIVER{ODBC Driver 18 for SQL Server};SERVER127.0.0.1;DATABASEdemo;Trusted_Connectionyes;TrustServerCertificateyes;)with pyodbc.connect(conn_str, autocommitFalse) as conn:cur conn.cursor()# 建表DDL 不含用户输入直接执行cur.execute(IF OBJECT_ID(dbo.book, U) IS NULLCREATE TABLE dbo.book (id INT IDENTITY(1,1) PRIMARY KEY,title NVARCHAR(200) NOT NULL,author NVARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL))# 增值走 ? 占位符cur.execute(INSERT INTO dbo.book (title, author, price) OUTPUT INSERTED.id VALUES (?, ?, ?),(Python 编程, 张三, 59.00),)print(新记录 id , cur.fetchone()[0])# 批量增cur.executemany(INSERT INTO dbo.book (title, author, price) VALUES (?, ?, ?),[(活着, 余华, 28.50), (三体, 刘慈欣, 23.00)],)# 查cur.execute(SELECT id, title, price FROM dbo.book WHERE author ? ORDER BY price DESC,(余华,),)for row in cur:print(row[0], row[1], row[2])conn.commit() # 忘记提交 数据没进去三个要点NVARCHAR而不是VARCHAR。SQL Server 里VARCHAR是单字节字符集取决于排序规则中文可能变问号NVARCHAR是 Unicode存中文必须用它。OUTPUT INSERTED.id是 SQL Server 拿新主键的正规做法。MySQL 靠lastrowidSQL Server 走OUTPUT子句。这是方言差异不是驱动差异。pyodbc.connect(..., autocommitFalse)是默认值必须显式commit()。想改成每条语句立即生效就传autocommitTrue——但那样会丢掉多条语句的原子性。关于批量插入executemany在数据量大时逐条发送的开销会很明显。pyodbc 提供了fast_executemany光标属性4.0.19 起提供把它设为True会改用更高效的批量绑定方式# 适用于 Python 3.8pyodbc 4.0.19 及以上cur conn.cursor()cur.fast_executemany Truecur.executemany(INSERT INTO dbo.book (title, author, price) VALUES (?, ?, ?), rows)注意它的适配程度和驱动版本、数据类型有关用之前请在自己的环境和真实数据上验证结果是否正确、是否真的更快不要照搬别人的结论。具体行为以 pyodbc 官方文档为准。五、读结果的方式与row对象pyodbc 返回的行对象既支持下标访问也支持按列名属性访问# 适用于 Python 3.8cur.execute(SELECT id, title, price FROM dbo.book)row cur.fetchone()if row is None:print(没有数据)else:print(row[0], row.title, row.price) # 下标和属性都能取取结果的方法和 DB-API 一致fetchone()无数据返回None、fetchmany(size)、fetchall()、或直接for row in cur迭代。结果集只能顺次消费一次fetchall()之后fetchone()只会拿到None。想看列名用cursor.description# 适用于 Python 3.8cur.execute(SELECT id, title, price FROM dbo.book)print([col[0] for col in cur.description])# 或者直接转成字典列表便于后续处理rows [dict(zip((c[0] for c in cur.description), r)) for r in cur.fetchall()]六、改用 pymssql 的写法如果部署环境不方便装 ODBC 驱动pymssql是备选。它的连接方式是关键字参数占位符是%s# 适用于 Python 3.9pymssql 2.ximport osimport pymssqlconn pymssql.connect(server127.0.0.1,usersa,passwordos.environ[MSSQL_SA_PASSWORD],databasedemo,charsetUTF-8,)try:# as_dictTrue 时每行是字典可以按列名取值cur conn.cursor(as_dictTrue)cur.execute(SELECT id, title, price FROM book WHERE author %s, (余华,))for row in cur:print(row[id], row[title], row[price])conn.commit()finally:conn.close()从 pyodbc 切到 pymssql必须改的地方有三处占位符从?变成%sOUTPUT INSERTED的写法可能不通用改用SELECT IDENTITY一类的方言行的取值方式as_dictTrue或下标。这不是「换个 import」那么简单。常见坑点1. 从 17 升到 18 版驱动后连接突然失败❌ 沿用老连接串DRIVER{ODBC Driver 18 for SQL Server};SERVER...;DATABASE...;UID...;PWD...报证书相关错误。✅ 18 版驱动Encrypt默认是yes证书校验随之生效。本地自签名证书场景加TrustServerCertificateyes仅限本机自用或显式Encryptno生产环境则应为服务器配置受信任证书。2.DRIVER名字和系统里的不一致❌DRIVER{SQL Server Native Client 11.0}——这台机器上没装这个驱动报「Data source name not found」。✅ 先print(pyodbc.drivers())看实际名字逐字复制包括{ }不能省。3. 把密码硬编码在源码里❌...;UIDsa;PWDPassw0rd!;直接提交进仓库。✅ 从环境变量读fPWD{os.environ[MSSQL_SA_PASSWORD]};配置文件不入版本库。4. 同时提供Trusted_Connection和UID/PWD❌ 两者都写行为不确定容易报莫名其妙的登录失败。✅ 二选一要么Trusted_Connectionyes要么UIDPWD。5. 用字符串拼接构造 SQL❌cur.execute(fSELECT * FROM book WHERE title {title})——标题里带单引号就能改变语句结构。✅cur.execute(SELECT * FROM book WHERE title ?, (title,))。6. 用VARCHAR存中文❌ 建表写VARCHAR(100)插入中文后查出来变成???。✅ 用NVARCHAR(100)Unicode或在库/列级别确认排序规则支持中文。7. 忘了 commit❌cur.execute(INSERT ...)之后直接conn.close()程序不报错库里没数据。关闭一个未提交的连接会回滚。✅ 写操作后conn.commit()异常路径conn.rollback()。8. 以为with pyodbc.connect(...) as conn:会关闭连接❌with pyodbc.connect(conn_str) as conn: ...之后就认为连接已经释放——它只提交事务不关闭连接这点和sqlite3、psycopg2一致。✅ 用try/finally显式conn.close()或者把连接放进连接池统一管理。总结环节结论驱动选择优先 pyodbc 微软官方 ODBC 驱动pymssql 作为备选驱动名用pyodbc.drivers()查实际名字逐字填进DRIVER{...}连接串SERVER支持localhost和.\实例名凭据来自环境变量认证Windows 集成认证与 SQL 账号认证二选一不要同时给加密18 版驱动Encrypt默认yes本地自签名证书需TrustServerCertificateyes或Encryptno参数pyodbc 用?pymssql 用%s一律参数化新主键SQL Server 用OUTPUT INSERTED.id不是lastrowid中文用NVARCHAR不要用VARCHAR本地连 SQL Server 出问题九成集中在两处驱动名不匹配以及 18 版驱动的加密默认值变化。把这两条记住剩下的就是标准的 DB-API 流程了。
阅读完成 · 觉得有帮助？
