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