處理 NULL 值

SQL NULL 代表缺失或未知的資料。 mssql-python 驅動程式將 SQL NULL 映射到 Python None。 這個區別很重要，因為 NULL 不等於任何東西，包括它自己。 在 SQL 中，NULL = NULL評估為 NULL（未知），不正確，所以在查詢和 IS NULL Python 中都用is None。

接收 NULL 值

提取結果中的 NULL

驅動程式會從 SQL Server 以 Python None格式回傳 NULL 值：

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

cursor.execute(
    "SELECT TOP 1 FirstName, MiddleName, LastName "
    "FROM Person.Person WHERE MiddleName IS NULL"
)
row = cursor.fetchone()

print(row.FirstName)   # First name value
print(row.MiddleName)  # None (NULL in database)
print(row.LastName)    # Last name value

檢查 NULL 值

在迭代結果時，使用 is 運算子檢查值是否為 None：

cursor.execute(
    "SELECT FirstName, MiddleName, LastName FROM Person.Person WHERE BusinessEntityID <= 10"
)

for row in cursor:
    if row.MiddleName is None:
        print(f"{row.FirstName} {row.LastName}: No middle name")
    else:
        print(f"{row.FirstName} {row.MiddleName} {row.LastName}")

使用 is None，而不是使用 == None

一定要用 is None 來做 NULL 檢查。 is 運算子會檢查同一性（也就是值是否確實為 None），而 == 會呼叫 __eq__，因此對自訂物件可能產生非預期的結果：

# Correct
if row.MiddleName is None:
    full_name = f"{row.FirstName} {row.LastName}"

# Avoid (works but not idiomatic)
if row.MiddleName == None:
    full_name = f"{row.FirstName} {row.LastName}"

傳送 NULL 值

以 None 插入 NULL

要插入 NULL 值，請傳遞 None：

cursor.execute(
    "CREATE TABLE #NullInsertDemo "
    "(Name NVARCHAR(50), Email NVARCHAR(100), Phone NVARCHAR(20))"
)
cursor.execute(
    "INSERT INTO #NullInsertDemo (Name, Email, Phone) "
    "VALUES (%(name)s, %(email)s, %(phone)s)",
    {"name": "Alice", "email": None, "phone": "555-1234"}
)
conn.commit()

更新至 NULL

透過傳遞 None 參數，將欄位設為 NULL：

cursor.execute(
    "CREATE TABLE #UpdateDemo (ID INT, Email NVARCHAR(100))"
)
cursor.execute("INSERT INTO #UpdateDemo VALUES (100, 'old@example.com')")
cursor.execute(
    "UPDATE #UpdateDemo SET Email = %(email)s WHERE ID = %(id)s",
    {"email": None, "id": 100}
)
conn.commit()

條件式 NULL 處理

定義處理可選參數的函式，若未提供時會將其設為 None ：

def update_record(cursor, record_id: int, name: str, email: str | None = None):
    """Update record, setting email to NULL if not provided."""
    cursor.execute(
        "UPDATE #Records SET Name = %(name)s, Email = %(email)s "
        "WHERE ID = %(id)s",
        {"name": name, "email": email, "id": record_id}
    )

WHERE 子句中的 NULL

在查詢中 IS NULL

在 SQL 中使用 IS NULL 進行 NULL 比較：

# Find people without a middle name
cursor.execute("SELECT FirstName FROM Person.Person WHERE MiddleName IS NULL")

# Find people with a middle name
cursor.execute("SELECT FirstName FROM Person.Person WHERE MiddleName IS NOT NULL")

動態 NULL 處理

當參數可能是 NULL 時，使用條件邏輯來構造適當的查詢：

def find_people(cursor, middle_name: str | None = None):
    """Find people, optionally filtering by middle name."""
    if middle_name is None:
        # Find people with NULL middle name
        cursor.execute("SELECT * FROM Person.Person WHERE MiddleName IS NULL")
    else:
        # Find people with specific middle name
        cursor.execute(
            "SELECT * FROM Person.Person WHERE MiddleName = %(middle_name)s",
            {"middle_name": middle_name},
        )
    return cursor.fetchall()

COALESCE 用於 NULL 替代

在 SQL 層級使用 COALESCE 來取代 NULL 的預設值。 COALESCE 比起在 Python 中檢查 None 更有效率，因為替換是在伺服器端進行的，從而減少應用程式中的條件邏輯：

cursor.execute("""
    SELECT 
        FirstName,
        COALESCE(MiddleName, '(none)') AS MiddleName,
        COALESCE(Suffix, 'N/A') AS Suffix
    FROM Person.Person
    WHERE BusinessEntityID <= 10
""")

for row in cursor:
    # MiddleName and Suffix will never be None
    print(f"{row.FirstName}: {row.MiddleName}, {row.Suffix}")

可安全處理 NULL 的操作

Python 中的預設值

cursor.execute("SELECT TOP 10 Name, Color FROM Production.Product")

for row in cursor:
    # Use or to provide default
    color = row.Color or "No color"
    print(f"{row.Name}: {color}")

格式化 NULL 值

def format_address(row):
    """Format address handling NULL components."""
    parts = [
        row.AddressLine1,
        row.AddressLine2,
        row.City,
        row.PostalCode,
    ]
    # Filter out None values
    return ", ".join(str(p) for p in parts if p is not None)

cursor.execute(
    "SELECT TOP 10 AddressLine1, AddressLine2, City, PostalCode "
    "FROM Person.Address"
)
for row in cursor:
    print(format_address(row))

聚合運算中的 NULL

SQL 聚合函式處理 NULL 值的方式與你預期的不同。 COUNT(column) 只計算非 NULL 值，並 COUNT(*) 計算所有列。 AVG、、 SUMMIN，MAX且全部忽略 NULL 值。 若欄位中的每個值皆為 NULL，這些函式回傳 NULL（而非零）。

# COUNT excludes NULL values
cursor.execute("SELECT COUNT(Color) FROM Production.Product")  # Counts non-NULL colors
color_count = cursor.fetchval()

# COUNT(*) includes all rows
cursor.execute("SELECT COUNT(*) FROM Production.Product")  # Counts all products
total_count = cursor.fetchval()

# AVG ignores NULL
cursor.execute("SELECT AVG(Weight) FROM Production.Product")  # Average of non-NULL weights
average_weight = cursor.fetchval()

帶有資料型態的 NULL

NULL 數值

from decimal import Decimal

cursor.execute("SELECT ListPrice FROM Production.Product WHERE ProductID = 1")
row = cursor.fetchone()

# Check before arithmetic
if row.ListPrice is not None:
    tax = row.ListPrice * Decimal("0.08")
    total = row.ListPrice + tax
else:
    total = Decimal("0")

NULL 日期值

在比較或計算中使用日期欄位前，請先確認日期欄位是否存在 None ：

from datetime import date

cursor.execute("SELECT Name, SellEndDate FROM Production.Product WHERE ProductID <= 10")

for row in cursor:
    if row.SellEndDate is None:
        print(f"{row.Name}: Currently selling")
    else:
        print(f"{row.Name}: Discontinued on {row.SellEndDate}")

NULL 字串值

在串接之前，先檢查是否為 None，以處理 NULL 字串資料行：

cursor.execute(
    "SELECT TOP 10 FirstName, MiddleName, LastName FROM Person.Person"
)

for row in cursor:
    # Build full name, handling NULL middle name
    if row.MiddleName:
        full_name = f"{row.FirstName} {row.MiddleName} {row.LastName}"
    else:
        full_name = f"{row.FirstName} {row.LastName}"
    print(full_name)

使用 NULL 進行大量運算

使用 NULL 值的 executemany

使用 executemany() 時，請在字典中為應為 NULL 的欄位傳入 None：

users = [
    {"name": "Alice", "title": "Ms.", "suffix": "Jr."},
    {"name": "Bob", "title": None, "suffix": "Sr."},  # NULL title
    {"name": "Carol", "title": "Dr.", "suffix": None},  # NULL suffix
]

cursor.executemany(
    "SELECT FirstName FROM Person.Person WHERE FirstName = %(name)s",
    users
)

含 NULL 的大量複製

批量複製操作會保留資料結構的 NULL 值：

cursor = conn.cursor()

cursor.execute("CREATE TABLE ##NullDemo (Name NVARCHAR(50), Email NVARCHAR(100), Phone NVARCHAR(20))")
conn.commit()

data = [
    ("Alice", "alice@example.com", "555-0001"),
    ("Bob", None, "555-0002"),      # NULL Email
    ("Carol", "carol@example.com", None),  # NULL Phone
]

result = cursor.bulkcopy("##NullDemo", data)
conn.commit()
print(f"Copied {result['rows_copied']} rows")

常見模式

可選的現場處理

使用型別提示來釐清在將資料列映射到資料類別時，哪些欄位可以為 NULL：

from dataclasses import dataclass
from typing import Optional

@dataclass
class PersonRecord:
    business_entity_id: int
    first_name: str
    middle_name: Optional[str] = None
    suffix: Optional[str] = None

def fetch_person(cursor, person_id: int) -> Optional[PersonRecord]:
    cursor.execute(
        "SELECT BusinessEntityID, FirstName, MiddleName, Suffix "
        "FROM Person.Person WHERE BusinessEntityID = %(id)s",
        {"id": person_id},
    )
    row = cursor.fetchone()
    if row is None:
        return None
    return PersonRecord(
        business_entity_id=row.BusinessEntityID,
        first_name=row.FirstName,
        middle_name=row.MiddleName,  # Will be None if NULL
        suffix=row.Suffix,           # Will be None if NULL
    )

帶有 NULL 的 JSON 序列化

使用 None Python null 模組時，值會自動轉換為 JSONjson：

import json

cursor.execute(
    "SELECT TOP 5 BusinessEntityID, FirstName, MiddleName FROM Person.Person"
)
rows = cursor.fetchall()

# Convert to JSON-serializable list
people = []
for row in rows:
    people.append({
        "id": row.BusinessEntityID,
        "name": row.FirstName,
        "middle_name": row.MiddleName,  # None becomes null in JSON
    })

json_output = json.dumps(people, indent=2)
print(json_output)
# [
#   {"id": 1, "name": "Ken", "middle_name": "J"},
#   {"id": 3, "name": "Roberto", "middle_name": null}
# ]

帶有 NULL 濾波的字典

在將列轉換為字典時，選擇排除 NULL 值：

def row_to_dict(row, cursor) -> dict:
    """Convert row to dict, optionally excluding NULL values."""
    columns = [col[0] for col in cursor.description]
    return {col: val for col, val in zip(columns, row) if val is not None}

cursor.execute("SELECT * FROM Person.Person WHERE BusinessEntityID = 1")
row = cursor.fetchone()
person_dict = row_to_dict(row, cursor)
# Only includes non-NULL columns

DataFrames 中的 NULL

當你使用 pandas 或 Polars 的資料框時，需要特別留意空值，因為這些函式庫使用各自的特殊標記值。

熊貓 NaN 與 NaT

pandas 使用 NaN（Not a Number）表示缺失的數值和字串值，並使用 NaT（Not a Time）表示缺失的日期時間（datetime）值。 這兩個值都不等同於 PythonNone：

import pandas as pd
import numpy as np

# When reading SQL results into pandas, NULL becomes NaN or NaT
cursor.execute("SELECT Name, Weight, SellEndDate FROM Production.Product")
table = cursor.arrow()
df = table.to_pandas()

# Check for missing values (covers NaN, NaT, and None)
print(df["Weight"].isna().sum())       # Count of NULL weights
print(df["SellEndDate"].isna().sum())  # Count of NULL dates

# Stage the data in a temp table to avoid mutating the source table
cursor.execute("CREATE TABLE #ProductWeights (Name NVARCHAR(100), Weight DECIMAL(8, 2) NULL)")

# Convert NaN back to None so NULL values round-trip correctly
for _, row in df.iterrows():
    weight = None if pd.isna(row["Weight"]) else float(row["Weight"])
    cursor.execute(
        "INSERT INTO #ProductWeights (Name, Weight) VALUES (%(name)s, %(weight)s)",
        {"name": row["Name"], "weight": weight}
    )

Warning

不要與 == np.nan 或 == pd.NaT 比較。 這些比較總是返回 False。 請改用 pd.isna() 或 pd.notna()。

Polars 空值處理

Polars 使用自己的null值（非 NaN），直接對應到 PythonNone：

import polars as pl

cursor.execute("SELECT Name, Weight, Color FROM Production.Product")
table = cursor.arrow()
df = pl.from_arrow(table)

# Filter rows with non-null values
has_weight = df.filter(pl.col("Weight").is_not_null())

# Replace null with a default
df = df.with_columns(pl.col("Color").fill_null("No color"))

相關內容

• 數據類型對應
• 資料擷取
• 列物件