前言先說兩點形式上的說明。第一本文不插圖片連接數(shù)據(jù)庫的每一步結(jié)果都是文字——驅(qū)動列表、報錯信息、查詢輸出用代碼塊呈現(xiàn)比截圖更精確也能直接復(fù)制。第二「SQL server」這個寫法不嚴(yán)謹(jǐn)微軟的正式產(chǎn)品名是Microsoft SQL Server下面統(tǒng)一按這個理解。連接本地 SQL Server 有兩條主流路線選哪條決定了后面所有的寫法路線依賴占位符特點pyodbcODBC 驅(qū)動如「ODBC Driver 18 for SQL Server」?走微軟官方 ODBC 驅(qū)動功能與兼容性最好是推薦路線pymssqlFreeTDS%s純依賴 FreeTDS部署時少裝一個 ODBC 驅(qū)動但生態(tài)不如 ODBC 路線本篇以 pyodbc 為主線因為它直連微軟官方驅(qū)動遇到問題時資料最全。還有一個必須先打預(yù)防針的坑「ODBC Driver 18 for SQL Server」把Encrypt的默認(rèn)值從no改成了yes強制加密。這是 17 到 18 之間最著名的行為變化。后果是用 18 版驅(qū)動連本地實例時如果服務(wù)器用的是自簽名證書連接會直接失敗報證書驗證錯誤。老教程里能跑的連接串換成 18 版驅(qū)動就跑不通原因就在這。解決辦法放在第二節(jié)講。一、環(huán)境確認(rèn)裝了哪些驅(qū)動在寫任何代碼之前先確認(rèn)機(jī)器上有哪些 ODBC 驅(qū)動。# 適用于 Python 3.8需先 pip install pyodbcimport pyodbcprint(pyodbc 版本:, pyodbc.version)print(已安裝的 ODBC 驅(qū)動:)for name in pyodbc.drivers():print( -, name)預(yù)期輸出示例實際列表取決于你裝了什么pyodbc 版本: 5.x.x已安裝的 ODBC 驅(qū)動:- ODBC Driver 18 for SQL Server- SQL Server幾個要點pyodbc.drivers()返回的是本機(jī) ODBC 驅(qū)動名的列表代碼里填的DRIVER{...}必須和這里打印出來的名字逐字一致包括版本號和空格?！窼QL Server」這個驅(qū)動是 Windows 自帶的舊版驅(qū)動很多年前就不再更新了。能用但不要在新項目里選它能用 18 就用 18。報「Data source name not found」或「驅(qū)動未找到」先跑上面這段代碼而不是去猜驅(qū)動名拼錯了沒有。安裝驅(qū)動有兩種途徑裝Microsoft ODBC Driver for SQL Server推薦有獨立安裝包或者用微軟官方文檔里列出的命令行安裝方式。具體安裝步驟以微軟官方文檔為準(zhǔn)本篇不展開。二、連接串每個鍵的含義連接串是「鍵值」用分號連接的字符串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(連接成功服務(wù)端版本首行:, version.splitlines()[0])finally:conn.close()預(yù)期輸出連接成功服務(wù)端版本首行: Microsoft SQL Server 2022 (RTM) - 16.0.x.x (X64)連接串常用鍵鍵含義本地連接的常見值DRIVERODBC 驅(qū)動名{ODBC Driver 18 for SQL Server}SERVER服務(wù)器地址127.0.0.1、localhost命名實例寫.\SQLEXPRESSDATABASE初始數(shù)據(jù)庫master或你的庫名UID/PWDSQL Server 賬號密碼建議從環(huán)境變量讀取別寫源碼里Trusted_Connection用 Windows 集成認(rèn)證yes時不需要UID/PWDEncrypt是否加密連接18 版驅(qū)動默認(rèn)yesTrustServerCertificate是否跳過證書校驗本地自簽名證書時設(shè)yesConnectTimeout連接超時秒數(shù)設(shè) 5~10避免長時間掛起關(guān)于Encrypt和TrustServerCertificate直接把官方文檔的結(jié)論說清楚Encrypt版本 17 及更早默認(rèn)是no版本 18.0 及以后默認(rèn)是yes。18 還額外接受mandatory等價yes、optional等價no和strictTDS 8.0 加密17 沒有對應(yīng)值。加密和證書校驗是兩件事?！笖?shù)據(jù)被加密傳輸」和「服務(wù)器證書被驗證過」是獨立的開關(guān)。本地開發(fā)實例通常用自簽名證書客戶端不認(rèn)得頒發(fā)者于是加密開著、證書校驗過不去連接失敗。連接失敗時的兩條出路讓客戶端信任服務(wù)器證書TrustServerCertificateyes僅適用于你確定連的是自己機(jī)器的場景或者把加密關(guān)掉Encryptno。生產(chǎn)環(huán)境不要用這兩條——應(yīng)該給服務(wù)器配一張受信任的證書。如果把賬號密碼寫進(jìn)連接串字符串一定要從環(huán)境變量或受控配置里取不要把密碼硬編碼進(jìn)源碼。三、用 Windows 集成認(rèn)證連接本地開發(fā)最常見的情形是「不建 SQL 賬號直接用當(dāng)前 Windows 用戶登錄」。這時把UID/PWD換成Trusted_Connection# 適用于 Python 3.8import pyodbcconn_str (DRIVER{ODBC Driver 18 for SQL Server};rSERVER.\SQLEXPRESS; # 命名實例默認(rèn)實例寫 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, | 當(dāng)前庫:, row.db_name)finally:conn.close()預(yù)期輸出以身份登錄: DESKTOP-XXXX\Administrator | 當(dāng)前庫: AdventureWorks兩個細(xì)節(jié)r.\SQLEXPRESS前面的r是原始字符串前綴避免\S被當(dāng)成轉(zhuǎn)義序列。這是 Windows 路徑風(fēng)格服務(wù)器名的常見書寫方式。Trusted_Connectionyes和UID/PWD不要同時給。同時給的情況下行為取決于驅(qū)動版本容易出現(xiàn)「明明沒傳賬號卻報登錄失敗」這類迷惑現(xiàn)象。四、執(zhí)行 SQLCRUD 與參數(shù)化pyodbc 的占位符是?參數(shù)是序列按位置綁定。# 適用于 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 不含用戶輸入直接執(zhí)行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() # 忘記提交 數(shù)據(jù)沒進(jìn)去三個要點NVARCHAR而不是VARCHAR。SQL Server 里VARCHAR是單字節(jié)字符集取決于排序規(guī)則中文可能變問號NVARCHAR是 Unicode存中文必須用它。OUTPUT INSERTED.id是 SQL Server 拿新主鍵的正規(guī)做法。MySQL 靠lastrowidSQL Server 走OUTPUT子句。這是方言差異不是驅(qū)動差異。pyodbc.connect(..., autocommitFalse)是默認(rèn)值必須顯式commit()。想改成每條語句立即生效就傳autocommitTrue——但那樣會丟掉多條語句的原子性。關(guān)于批量插入executemany在數(shù)據(jù)量大時逐條發(fā)送的開銷會很明顯。pyodbc 提供了fast_executemany光標(biāo)屬性4.0.19 起提供把它設(shè)為True會改用更高效的批量綁定方式# 適用于 Python 3.8pyodbc 4.0.19 及以上cur conn.cursor()cur.fast_executemany Truecur.executemany(INSERT INTO dbo.book (title, author, price) VALUES (?, ?, ?), rows)注意它的適配程度和驅(qū)動版本、數(shù)據(jù)類型有關(guān)用之前請在自己的環(huán)境和真實數(shù)據(jù)上驗證結(jié)果是否正確、是否真的更快不要照搬別人的結(jié)論。具體行為以 pyodbc 官方文檔為準(zhǔn)。五、讀結(jié)果的方式與row對象pyodbc 返回的行對象既支持下標(biāo)訪問也支持按列名屬性訪問# 適用于 Python 3.8cur.execute(SELECT id, title, price FROM dbo.book)row cur.fetchone()if row is None:print(沒有數(shù)據(jù))else:print(row[0], row.title, row.price) # 下標(biāo)和屬性都能取取結(jié)果的方法和 DB-API 一致fetchone()無數(shù)據(jù)返回None、fetchmany(size)、fetchall()、或直接for row in cur迭代。結(jié)果集只能順次消費一次fetchall()之后fetchone()只會拿到None。想看列名用cursor.description# 適用于 Python 3.8cur.execute(SELECT id, title, price FROM dbo.book)print([col[0] for col in cur.description])# 或者直接轉(zhuǎn)成字典列表便于后續(xù)處理rows [dict(zip((c[0] for c in cur.description), r)) for r in cur.fetchall()]六、改用 pymssql 的寫法如果部署環(huán)境不方便裝 ODBC 驅(qū)動pymssql是備選。它的連接方式是關(guān)鍵字參數(shù)占位符是%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或下標(biāo)。這不是「換個 import」那么簡單。常見坑點1. 從 17 升到 18 版驅(qū)動后連接突然失敗? 沿用老連接串DRIVER{ODBC Driver 18 for SQL Server};SERVER...;DATABASE...;UID...;PWD...報證書相關(guān)錯誤。? 18 版驅(qū)動Encrypt默認(rèn)是yes證書校驗隨之生效。本地自簽名證書場景加TrustServerCertificateyes僅限本機(jī)自用或顯式Encryptno生產(chǎn)環(huán)境則應(yīng)為服務(wù)器配置受信任證書。2.DRIVER名字和系統(tǒng)里的不一致?DRIVER{SQL Server Native Client 11.0}——這臺機(jī)器上沒裝這個驅(qū)動報「Data source name not found」。? 先print(pyodbc.drivers())看實際名字逐字復(fù)制包括{ }不能省。3. 把密碼硬編碼在源碼里?...;UIDsa;PWDPassw0rd!;直接提交進(jìn)倉庫。? 從環(huán)境變量讀fPWD{os.environ[MSSQL_SA_PASSWORD]};配置文件不入版本庫。4. 同時提供Trusted_Connection和UID/PWD? 兩者都寫行為不確定容易報莫名其妙的登錄失敗。? 二選一要么Trusted_Connectionyes要么UIDPWD。5. 用字符串拼接構(gòu)造 SQL?cur.execute(fSELECT * FROM book WHERE title {title})——標(biāo)題里帶單引號就能改變語句結(jié)構(gòu)。?cur.execute(SELECT * FROM book WHERE title ?, (title,))。6. 用VARCHAR存中文? 建表寫VARCHAR(100)插入中文后查出來變成???。? 用NVARCHAR(100)Unicode或在庫/列級別確認(rèn)排序規(guī)則支持中文。7. 忘了 commit?cur.execute(INSERT ...)之后直接conn.close()程序不報錯庫里沒數(shù)據(jù)。關(guān)閉一個未提交的連接會回滾。? 寫操作后conn.commit()異常路徑conn.rollback()。8. 以為with pyodbc.connect(...) as conn:會關(guān)閉連接?with pyodbc.connect(conn_str) as conn: ...之后就認(rèn)為連接已經(jīng)釋放——它只提交事務(wù)不關(guān)閉連接這點和sqlite3、psycopg2一致。? 用try/finally顯式conn.close()或者把連接放進(jìn)連接池統(tǒng)一管理?？偨Y(jié)環(huán)節(jié)結(jié)論驅(qū)動選擇優(yōu)先 pyodbc 微軟官方 ODBC 驅(qū)動pymssql 作為備選驅(qū)動名用pyodbc.drivers()查實際名字逐字填進(jìn)DRIVER{...}連接串SERVER支持localhost和.\實例名憑據(jù)來自環(huán)境變量認(rèn)證Windows 集成認(rèn)證與 SQL 賬號認(rèn)證二選一不要同時給加密18 版驅(qū)動Encrypt默認(rèn)yes本地自簽名證書需TrustServerCertificateyes或Encryptno參數(shù)pyodbc 用?pymssql 用%s一律參數(shù)化新主鍵SQL Server 用OUTPUT INSERTED.id不是lastrowid中文用NVARCHAR不要用VARCHAR本地連 SQL Server 出問題九成集中在兩處驅(qū)動名不匹配以及 18 版驅(qū)動的加密默認(rèn)值變化。把這兩條記住剩下的就是標(biāo)準(zhǔn)的 DB-API 流程了。