pymssql 完整使用指南：連線、查詢、預存程序與進階功能

pymssql 完整使用指南

pymssql 是 Python 連接 Microsoft SQL Server 的熱門 DB-API 2.0 相容驅動程式，基於 FreeTDS。本文整理官方文件核心範例，涵蓋基本操作、Windows 驗證、結果迭代、游標限制、字典模式、Context Manager、預存程序呼叫與協作式多工整合。

一、基本操作（嚴格遵循 DB-API）

import os
import pymssql

server = os.getenv("PYMSSQL_TEST_SERVER")
user = os.getenv("PYMSSQL_TEST_USERNAME")
password = os.getenv("PYMSSQL_TEST_PASSWORD")

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor()

# 建立測試表
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")

# 批次插入
cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')]
)
conn.commit()  # 必須 commit 才會持久化

# 查詢與逐列讀取
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()

二、Windows 整合驗證連線

conn = pymssql.connect(
    host=r'dbhostname\myinstance',   # 主機\實例名
    user=r'companydomain\username',  # 網域\使用者
    password=PASSWORD,
    database='DatabaseOfInterest'
)

使用原始字串 r'...' 避免反斜線轉義問題。

三、結果集迭代（擴展 DB-API）

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:  # 直接迭代游標
    print('row = %r' % (row,))
conn.close()

四、重要：單一連線同時間僅能有一個作用中游標

c1 = conn.cursor()
c1.execute('SELECT * FROM persons')
c2 = conn.cursor()
c2.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

print(c1.fetchall())  # 顯示 c2 的結果！
print(c2.fetchall())  # 空結果

原因： TDS 協定無客戶端游標，必須先讀完前一個查詢結果才能執行下一個。

解決方案：

1. 建立第二個連線：每個連線可獨立執行查詢
2. 先 fetchall() 再執行下一查詢：

c1.execute('SELECT ...')
c1_list = c1.fetchall()
c2.execute('SELECT ...')
c2_list = c2.fetchall()
# 後續使用 c1_list, c2_list

五、字典模式游標（依欄位名存取）

cursor = conn.cursor(as_dict=True)
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
    print("ID=%d, Name=%s" % (row['id'], row['name']))

六、Context Manager（with 陳述式）自動管理資源

with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))
# 自動關閉 cursor 與 connection

七、呼叫預存程序（pymssql 2.0+ 支援 RPC）

with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute("""
            CREATE PROCEDURE FindPerson @name VARCHAR(100)
            AS BEGIN SELECT * FROM persons WHERE name = @name END
        """)
        cursor.callproc('FindPerson', ('Jane Doe',))
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))

八、協作式多工整合（gevent 範例，2.1.0+）

import gevent.socket
import pymssql

def wait_callback(read_fileno):
    gevent.socket.wait_read(read_fileno)

pymssql.set_wait_callback(wait_callback)

適用於 Gunicorn + gevent worker 高併發場景，查詢等待時可讓出控制權處理其他請求，減少 worker 進程數需求。

九、擴展功能總覽

pymssql 功能完整且效能優異，是 Python 連接 SQL Server 的首選庫之一。