mssql-python 驅動程式將 SQL Server 的十進位、數字及貨幣資料類型對應至 Python，decimal.Decimal以實現精確的財務與科學計算。 SQL Server 提供以下精確的數字類型：
| SQL 型別 | Python 類型 | Precision | 用例 |
|---|---|---|---|
| decimal(p,s) | decimal.Decimal |
1-38 位數 | 精確計算 |
| numeric(p,s) | decimal.Decimal |
1-38 位數 | 和小數一樣 |
| money | decimal.Decimal |
19位數，4位小數 | Currency |
| smallmoney | decimal.Decimal |
10位數，4位小數 | 小額貨幣 |
| float | float |
~15位數 | 近似 |
| real | float |
~7位數 | 近似 |
Python 十進位型別
接收十進位值
十進位欄位回傳 Python decimal.Decimal 物件：
from decimal import Decimal
import mssql_python
conn = mssql_python.connect(connection_string)
cursor = conn.cursor()
cursor.execute(
"SELECT SubTotal, TaxAmt, TotalDue "
"FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659"
)
row = cursor.fetchone()
print(type(row.SubTotal)) # <class 'decimal.Decimal'>
print(row.SubTotal) # Decimal('20565.6206')
print(row.TaxAmt) # Decimal('1971.5149')
print(row.TotalDue) # Decimal('23153.2339')
傳送十進位值
傳遞 Decimal 物件以便精確插入：
from decimal import Decimal
cursor.execute("""
CREATE TABLE #ProductDemo (
Name NVARCHAR(50),
Price DECIMAL(10,2),
Cost DECIMAL(10,2)
)
""")
cursor.execute("""
INSERT INTO #ProductDemo (Name, Price, Cost)
VALUES (%(name)s, %(price)s, %(cost)s)
""", {
"name": "Widget",
"price": Decimal("199.99"),
"cost": Decimal("87.50")
})
conn.commit()
避免用浮動交易來處理財務資料
浮動型存在精度問題，不適合用於財務計算。 為了取得準確結果，請務必使用 Decimal ：
from decimal import Decimal
# Bad: float has precision issues
price = 0.1 + 0.2 # 0.30000000000000004
# Good: Decimal is exact
price = Decimal("0.1") + Decimal("0.2") # Decimal('0.3')
# Use string constructor for literal values
correct = Decimal("19.99") # Exact
avoid = Decimal(19.99) # Might introduce float imprecision
貨幣類型
使用金額欄位
使用 Python 十進位物件檢索並更新 SQL Server 的金錢欄位：
from decimal import Decimal
# Create and populate a temp table with a money column
cursor.execute("""
CREATE TABLE #Accounts (
ID INT,
AccountID NVARCHAR(20),
Balance MONEY
)
""")
cursor.execute(
"INSERT INTO #Accounts (ID, AccountID, Balance) VALUES (1, %(acct)s, %(bal)s)",
{"acct": "ACCT-001", "bal": Decimal("1234.5678")}
)
conn.commit()
cursor.execute("SELECT AccountID, Balance FROM #Accounts WHERE ID = 1")
row = cursor.fetchone()
print(row.Balance) # Decimal('1234.5678')
print(type(row.Balance)) # <class 'decimal.Decimal'>
# Money arithmetic
cursor.execute("""
UPDATE #Accounts
SET Balance = Balance + %(amount)s
WHERE ID = %(id)s
""", {"amount": Decimal("100.00"), "id": 1})
conn.commit()
貨幣格式化
將十進位值格式化為貨幣串，並加上符號與逗號分隔符：
from decimal import Decimal
def format_currency(value: Decimal, symbol: str = "$") -> str:
"""Format decimal as currency string."""
return f"{symbol}{value:,.2f}"
cursor.execute("SELECT TotalDue FROM Sales.SalesOrderHeader")
for row in cursor:
print(format_currency(row.TotalDue)) # $1,234.56
十進位精度與刻度
了解精確度與規模
- 精度（p）：總位數
- 刻度：小數點之後的數字數
例如，可以 decimal(10, 2) 儲存 -9999999.99 到 999999.99 的值，而 decimal(5, 4) 可以儲存 -9.9999 到 9.9999 的值。
在參數中指定精度
mssql-python 驅動程式會自動從十進位值推斷精確度與擴展性：
from decimal import Decimal
# Create a temp table for the measurement value
cursor.execute("CREATE TABLE #Measurements (Value DECIMAL(18,6))")
# The driver infers precision from the Decimal value
value = Decimal("123.456789")
cursor.execute("INSERT INTO #Measurements (Value) VALUES (%(v)s)", {"v": value})
conn.commit()
控制四捨五入
使用 quantize() 方法將十進位數值四捨五入至特定刻度：
from decimal import Decimal, ROUND_HALF_UP, ROUND_DOWN
price = Decimal("19.999")
# Round to 2 decimal places
rounded = price.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
print(rounded) # Decimal('20.00')
# Round down (truncate)
truncated = price.quantize(Decimal("0.01"), rounding=ROUND_DOWN)
print(truncated) # Decimal('19.99')
財務計算
安全算術
使用 Decimal 實作財務計算，並採用適當的四捨五入方式：
from decimal import Decimal, ROUND_HALF_UP
def calculate_total(price: Decimal, quantity: int, tax_rate: Decimal) -> Decimal:
"""Calculate order total with tax."""
subtotal = price * quantity
tax = (subtotal * tax_rate).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
return subtotal + tax
cursor.execute("SELECT ListPrice FROM Production.Product WHERE ProductID = 1")
price = cursor.fetchval()
total = calculate_total(
price=price,
quantity=3,
tax_rate=Decimal("0.0825") # 8.25% tax
)
cursor.execute("""
CREATE TABLE #OrderCalc (
ProductID INT, Quantity INT, Total DECIMAL(10,2), Tax DECIMAL(10,2)
)
""")
cursor.execute("""
INSERT INTO #OrderCalc (ProductID, Quantity, Total, Tax)
VALUES (%(prod)s, %(qty)s, %(total)s, %(tax)s)
""", {"prod": 1, "qty": 3, "total": total, "tax": total * Decimal("0.0825")})
百分比計算
計算基於百分比的折扣與調整：
from decimal import Decimal
def calculate_discount(price: Decimal, discount_percent: Decimal) -> Decimal:
"""Calculate discounted price."""
discount = (price * discount_percent / 100).quantize(Decimal("0.01"))
return price - discount
original = Decimal("99.99")
discounted = calculate_discount(original, Decimal("15")) # 15% off
print(f"Original: ${original}, After discount: ${discounted}")
分攤金額
將總金額平均分配給多方，處理剩餘部分：
from decimal import Decimal, ROUND_DOWN
def split_amount(total: Decimal, ways: int) -> list[Decimal]:
"""Split amount evenly, handling remainder."""
per_person = (total / ways).quantize(Decimal("0.01"), rounding=ROUND_DOWN)
remainder = total - (per_person * ways)
amounts = [per_person] * ways
# Add remainder to first person
amounts[0] += remainder
return amounts
total = Decimal("100.00")
split = split_amount(total, 3)
print(split) # [Decimal('33.34'), Decimal('33.33'), Decimal('33.33')]
print(sum(split)) # Decimal('100.00') - always equals original
彙總運算
SUM 與 AVG 並以小數點表示
SQL Server 聚合函式會回傳十進位型別以求精確度：
# SUM preserves decimal type
cursor.execute("SELECT SUM(TotalDue) AS TotalSales FROM Sales.SalesOrderHeader")
total_sales = cursor.fetchval()
print(type(total_sales)) # <class 'decimal.Decimal'>
# AVG might return more decimal places
cursor.execute("SELECT AVG(ListPrice) AS AvgPrice FROM Production.Product")
avg_price = cursor.fetchval()
# Round to desired precision
avg_price = avg_price.quantize(Decimal("0.01"))
處理彙總中的 NULL
當查詢沒有資料列匹配時，SQL Server 聚合函式可以回傳None：
cursor.execute("SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE CustomerID = 0")
total_discount = cursor.fetchval()
# SUM returns NULL if no rows match
if total_discount is None:
total_discount = Decimal("0.00")
十進位輸出轉換器
輸出轉換器使用中cursor.description所示的 Python 型別作為鍵，而非 ODBC SQL 型別整數。 向 decimal.Decimal 註冊轉換器，讓它可用於 decimal、numeric、money 和 smallmoney 欄位，這些欄位都會對應到 decimal.Decimal。
轉換成浮點（當精度不是關鍵時）
為了相容於預期浮點數值的函式庫，請定義輸出轉換器：
import mssql_python
from decimal import Decimal
def decimal_to_float(value):
"""Convert Decimal to float."""
if value is None:
return None
return float(value) # value is already a Decimal object
conn = mssql_python.connect(connection_string)
# Convert decimals to float for compatibility with libraries that expect float
conn.add_output_converter(Decimal, decimal_to_float)
cursor = conn.cursor()
cursor.execute("SELECT ListPrice FROM Production.Product WHERE ProductID = 1")
row = cursor.fetchone()
print(type(row.ListPrice)) # <class 'float'>
自訂貨幣格式轉換器
定義一個自訂輸出轉換器，自動將十進位數值格式化為貨幣字串：
from decimal import Decimal
def money_to_string(value):
"""Convert Decimal to formatted string."""
if value is None:
return None
return f"${value:,.2f}" # value is already a Decimal object
conn.add_output_converter(Decimal, money_to_string)
cursor = conn.cursor()
cursor.execute("CREATE TABLE #Accounts (Balance DECIMAL(19,4))")
cursor.execute(
"INSERT INTO #Accounts (Balance) VALUES (%(bal)s)",
{"bal": Decimal("1234.56")}
)
conn.commit()
cursor.execute("SELECT Balance FROM #Accounts")
for row in cursor:
print(row.Balance) # "$1,234.56"
常見模式
貨幣轉換
利用匯率將貨幣金額轉換：
from decimal import Decimal
def convert_currency(amount: Decimal, rate: Decimal) -> Decimal:
"""Convert currency at given exchange rate."""
return (amount * rate).quantize(Decimal("0.01"))
usd_amount = Decimal("100.00")
eur_rate = Decimal("0.92")
eur_amount = convert_currency(usd_amount, eur_rate)
print(f"${usd_amount} = €{eur_amount}")
交易餘額更新
使用具有適當鎖定提示的交易，以確保資金轉帳的原子性並防止遺失更新：
from decimal import Decimal
def transfer_funds(conn, from_account: int, to_account: int, amount: Decimal):
"""Transfer funds between accounts atomically."""
cursor = conn.cursor()
conn.autocommit = False
try:
balances = {}
for account_id in sorted((from_account, to_account)):
cursor.execute(
"SELECT Balance FROM #Accounts WITH (UPDLOCK) WHERE ID = %(id)s",
{"id": account_id}
)
row = cursor.fetchone()
if row is None:
raise ValueError(f"Account {account_id} not found")
balances[account_id] = row.Balance
if balances[from_account] < amount:
raise ValueError("Insufficient funds")
# Debit source
cursor.execute("""
UPDATE #Accounts SET Balance = Balance - %(amount)s
WHERE ID = %(id)s
""", {"amount": amount, "id": from_account})
# Credit destination
cursor.execute("""
UPDATE #Accounts SET Balance = Balance + %(amount)s
WHERE ID = %(id)s
""", {"amount": amount, "id": to_account})
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.autocommit = True
# Set up demo accounts
cursor = conn.cursor()
cursor.execute("CREATE TABLE #Accounts (ID INT, Balance MONEY)")
cursor.executemany(
"INSERT INTO #Accounts (ID, Balance) VALUES (%(id)s, %(bal)s)",
[{"id": 1, "bal": Decimal("1000.00")}, {"id": 2, "bal": Decimal("500.00")}]
)
conn.commit()
# Usage
transfer_funds(conn, from_account=1, to_account=2, amount=Decimal("500.00"))
轉帳會一開始就鎖定這兩個資料列，先鎖定 ID 較低者，方法是依排序後的順序，為每個帳戶分別發出一個 SELECT ... WITH (UPDLOCK)。
UPDLOCK 可防止遺失更新的情況，也就是兩筆交易讀取相同餘額後，根據過時資料套用更新。 依排序順序分別取得鎖，才能保證鎖的取得順序，避免同一兩個帳戶之間方向相反的轉帳以相反順序取得鎖而發生死結。
含小數的批次插入
使用 executemany() 插入多列十進位值：
from decimal import Decimal
cursor.execute("""
CREATE TABLE #BulkProducts (
Name NVARCHAR(50),
Price DECIMAL(10,2),
Cost DECIMAL(10,2)
)
""")
products = [
{"name": "Widget A", "price": Decimal("19.99"), "cost": Decimal("8.50")},
{"name": "Widget B", "price": Decimal("29.99"), "cost": Decimal("12.75")},
{"name": "Widget C", "price": Decimal("39.99"), "cost": Decimal("18.00")},
]
cursor.executemany("""
INSERT INTO #BulkProducts (Name, Price, Cost)
VALUES (%(name)s, %(price)s, %(cost)s)
""", products)
conn.commit()
最佳做法
財務數學一定要用十進位
切勿使用 float 進行財務計算；為了確保精確度，請一律使用 Decimal 型別：
# Wrong: float precision issues
price = 0.10
quantity = 3
total = price * quantity # 0.30000000000000004
# Correct: Decimal is exact
price = Decimal("0.10")
quantity = 3
total = price * quantity # Decimal('0.30')
使用字串建構器
從字串構造十進位值以避免浮點數不精確：
# Good: exact value
amount = Decimal("123.45")
# Risky: might inherit float imprecision
amount = Decimal(123.45) # Could be 123.4500000000000028...
展示或儲存前一定要圓
在顯示或儲存到資料庫之前，請先將十進位數值四捨五入至適當的小數位數：
from decimal import Decimal, ROUND_HALF_UP
calculated = Decimal("123.456789")
display = calculated.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
如有需要，請設定小數上下文
小數上下文的精確度影響 計算結果，而非從字串解析或從資料庫讀取的數值。 查詢一律會傳回一個 money 或 decimal 值，並保留其完整儲存的小數位數，不受 getcontext().prec 影響。 上下文只有在你用它計算時才會生效。 要觀察精確度的效果，請執行除法等運算。
from decimal import Decimal, getcontext
# A money value read from SQL Server arrives at its full stored scale,
# regardless of the context precision.
cursor.execute(
"SELECT SubTotal FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659"
)
subtotal = cursor.fetchone().SubTotal
print(subtotal) # Decimal('20565.6206')
# The context precision governs the calculation, not the fetched value.
# Split the subtotal into three equal installments.
getcontext().prec = 10
print(subtotal / 3) # 6855.206867 (10 significant digits)
getcontext().prec = 28 # Reset to default
print(subtotal / 3) # 6855.206866666666666666666667 (28 significant digits)