Använd glesa kolumner och kolumnuppsättningar med mssql-python

Sparse-kolumner är en lagringsoptimering i Microsoft SQL Server för NULL-värden i tabeller med många kolumner som kan innehålla NULL-värden. Klientapplikationer ser glesa kolumner som reguljära kolumner. mssql-python-drivrutinen läser och skriver dem som vilken annan kolumn som helst utan särskild hantering.

Feature	Beskrivning
Glesa kolumner	NULL-värden använder nolllagring
Kolumnuppsättningar	XML-representation av alla glesa kolumner
Breda tabeller	Stöd för upp till 30 000 kolumner

Note

Glesa kolumner är en funktion på serversidan. mssql-python-drivrutinen kräver ingen speciell konfiguration eller API för att fungera med glesa kolumner. Den enda skillnaden som kan vara synlig för klienten: när du använder kolumnuppsättningar returnerar de en XML-representation av glesa kolumnvärden.

Bäst lämpad för:

Tabeller med 20-50%+ NULL-värden.
Dokumentlagring med varierande attribut.
EAV-mönster (Entity-Attribute-Value).
Sensordata med många valfria avläsningar.

Skapa glesa kolumner

Definiera glesa kolumner i ditt tabellschema genom att lägga till SPARSE NULL modifieraren till kolumner som ofta innehåller NULL-värden.

Grundläggande glesa kolumntabell

Skapa en tabell med glesa kolumner för valfria attribut.

CREATE TABLE ProductAttributes (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Sparse columns for optional attributes
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Med kolumninställning

Lägg till en kolumnuppsättning för att ge XML-åtkomst till alla glesa kolumner samtidigt.

CREATE TABLE ProductAttributesWithSet (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Column set provides XML access to all sparse columns
    SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    -- Sparse columns
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Infoga data för glesa kolumner

Infoga i glesa kolumner med namn, precis som du skulle göra med vanliga kolumner.

Infoga individuella kolumner

Infoga produkter med specifika glesa kolumnvärden fyllda i.

import mssql_python

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

# Create the table with sparse columns
cursor.execute("DROP TABLE IF EXISTS ProductAttributes")
cursor.execute("""
    CREATE TABLE ProductAttributes (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert with some sparse columns populated
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Color, Size)
    VALUES (%(id)s, %(name)s, %(color)s, %(size)s)
""", {"id": 1, "name": "T-Shirt", "color": "Blue", "size": "Large"})

# Insert with different sparse columns
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Weight, Material)
    VALUES (%(id)s, %(name)s, %(weight)s, %(material)s)
""", {"id": 2, "name": "Coffee Mug", "weight": 0.35, "material": "Ceramic"})

conn.commit()

Infoga via kolumnuppsättning (XML)

Sätt in flera glesa kolumnvärden samtidigt genom att skicka XML till kolumnuppsättningen.

# Create the table with a column set
cursor.execute("DROP TABLE IF EXISTS ProductAttributesWithSet")
cursor.execute("""
    CREATE TABLE ProductAttributesWithSet (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert using column set XML
cursor.execute("""
    INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
    VALUES (%(id)s, %(name)s, %(xml)s)
""", {
    "id": 3,
    "name": "Laptop Bag",
    "xml": "<Color>Black</Color><Size>Medium</Size><Material>Nylon</Material><Warranty>24</Warranty>"
})
conn.commit()

Dynamisk attributinsättning

Skapa en funktion som accepterar dynamiska attribut som en ordbok och bygger XML automatiskt.

def insert_with_attributes(cursor, product_id: int, name: str, attributes: dict):
    """Insert product with dynamic sparse column attributes."""
    # Build XML for column set
    xml_parts = [f"<{key}>{value}</{key}>" for key, value in attributes.items()]
    attributes_xml = "".join(xml_parts)
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": attributes_xml or None})

# Usage
insert_with_attributes(cursor, 4, "Headphones", {
    "Color": "Silver",
    "Warranty": 12,
    "Manufacturer": "AudioTech"
})
conn.commit()

Sök i glesa kolumner

Sök i glesa kolumner efter namn, eller hämta alla glesa värden samtidigt genom kolumnuppsättningen.

Sök i enskilda kolumner

Sök specifika glesa kolumner med standard SELECT-syntax.

# Query specific sparse columns
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size
    FROM ProductAttributes
    WHERE Color IS NOT NULL
""")

for row in cursor:
    print(f"{row.ProductName}: {row.Color}, {row.Size}")

Fråga via kolumnuppsättning

Hämta alla glesa kolumnvärden som XML från kolumnmängden.

# Get column set XML
cursor.execute("""
    SELECT ProductID, ProductName, SparseAttributes
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
print(f"Product: {row.ProductName}")
print(f"Attributes XML: {row.SparseAttributes}")

Tolka XML för kolumnuppsättningar i Python

Tolka XML:en från kolumnuppsättningen för att konvertera den till en Python-ordbok för enklare hantering.

from xml.etree import ElementTree as ET

def get_product_attributes(cursor, product_id: int) -> dict:
    """Get product with parsed sparse attributes."""
    cursor.execute("""
        SELECT ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        return None
    
    result = {"ProductName": row.ProductName}
    
    # Parse XML column set
    if row.SparseAttributes:
        # Wrap in root element for parsing
        xml_str = f"<root>{row.SparseAttributes}</root>"
        root = ET.fromstring(xml_str)
        
        for elem in root:
            result[elem.tag] = elem.text
    
    return result

# Usage
product = get_product_attributes(cursor, 3)
print(product)
# {'ProductName': 'Laptop Bag', 'Color': 'Black', 'Size': 'Medium', 'Material': 'Nylon', 'Warranty': '24'}

Fråga med SELECT *

När du använder SELECT * returneras kolumnuppsättningen som en enda XML-kolumn istället för individuella glesa kolumner.

# SELECT * returns column set instead of individual sparse columns
cursor.execute("""
    SELECT * FROM ProductAttributesWithSet WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
# Returns: ProductID, ProductName, SparseAttributes (not individual columns)
print(f"Columns: {[col[0] for col in cursor.description]}")

Sök explicit i enskilda kolumner

För att hämta individuella glesa kolumner från en tabell med en kolumnuppsättning, lista dem explicit i SELECT-klausulen.

# To get individual sparse columns with column set table, list them explicitly
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size, Weight, Material, Warranty, Manufacturer
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

# Now each sparse column is available as separate property
row = cursor.fetchone()
print(f"Color: {row.Color}, Material: {row.Material}")

Uppdatera glesa kolumner

Uppdatera enskilda glesa kolumner efter namn eller ersätt alla glesa värden samtidigt via kolumnuppsättningen XML.

Uppdatera enskilda kolumner

Uppdatera specifika glesa kolumnvärden med standard SQL-syntax UPDATE .

cursor.execute("""
    UPDATE ProductAttributes
    SET Color = %(color)s, Weight = %(weight)s
    WHERE ProductID = %(id)s
""", {"id": 1, "color": "Red", "weight": 0.2})
conn.commit()

Uppdatera via kolumnuppsättning

Ersätt alla värden i glesa kolumner genom att uppdatera kolumnuppsättningens XML direkt.

# Replace all sparse column values via column set
cursor.execute("""
    UPDATE ProductAttributesWithSet
    SET SparseAttributes = %(xml)s
    WHERE ProductID = %(id)s
""", {
    "id": 3,
    "xml": "<Color>Navy</Color><Size>Large</Size><Material>Leather</Material>"
})
conn.commit()
# Note: This clears any sparse columns not included in the XML

Partiell uppdatering via kolumnuppsättning

Uppdatera endast specifika glesa attribut samtidigt som värdena från andra attribut som inte ingår i uppdateringen bevaras.

# To update only specific attributes, merge with existing
def update_attributes(cursor, product_id: int, updates: dict):
    """Update specific sparse attributes while preserving others."""
    # Get current attributes
    cursor.execute("""
        SELECT Color, Size, Weight, Material, Warranty, Manufacturer
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        raise ValueError(f"Product {product_id} not found")
    
    # Merge updates
    current = {
        "Color": row.Color,
        "Size": row.Size,
        "Weight": row.Weight,
        "Material": row.Material,
        "Warranty": row.Warranty,
        "Manufacturer": row.Manufacturer
    }
    
    for key, value in updates.items():
        current[key] = value
    
    # Build XML with non-null values
    xml_parts = []
    for key, value in current.items():
        if value is not None:
            xml_parts.append(f"<{key}>{value}</{key}>")
    
    cursor.execute("""
        UPDATE ProductAttributesWithSet
        SET SparseAttributes = %(xml)s
        WHERE ProductID = %(id)s
    """, {"id": product_id, "xml": "".join(xml_parts) or None})

# Usage
update_attributes(cursor, 3, {"Color": "Brown", "Warranty": 36})
conn.commit()

Dynamiska kolumnmönster

Bygg flexibla frågor som söker dynamiskt över gles kolumner, med hjälp av tillåtningslistor för att validera kolumnnamn och förhindra SQL-injektionsattacker.

Fråga efter EAV-liknande data

Sök efter produkter efter ett specifikt attributnamn och värde med hjälp av Entity-Attribute-Value (EAV) mönstermatchning.

def find_products_by_attribute(cursor, attribute_name: str, attribute_value: str) -> list:
    """Find products with specific attribute value."""
    # Validate column name against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    if attribute_name not in allowed_columns:
        raise ValueError(f"Invalid attribute: {attribute_name}")
    
    cursor.execute(f"""
        SELECT ProductID, ProductName, {attribute_name}
        FROM ProductAttributesWithSet
        WHERE {attribute_name} = %(value)s
    """, {"value": attribute_value})
    
    return cursor.fetchall()

# Find all blue products
blue_products = find_products_by_attribute(cursor, "Color", "Blue")

Flexibel attributsökning

Sök efter produkter som matchar flera valfria attribut samtidigt.

def search_by_attributes(cursor, **attributes) -> list:
    """Search products by multiple optional attributes."""
    # Validate column names against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    invalid = set(attributes.keys()) - allowed_columns
    if invalid:
        raise ValueError(f"Invalid attributes: {invalid}")
    
    conditions = ["1=1"]  # Always true base condition
    params = {}
    
    for i, (key, value) in enumerate(attributes.items()):
        if value is not None:
            conditions.append(f"{key} = %(attr_{i})s")
            params[f"attr_{i}"] = value
    
    query = f"""
        SELECT ProductID, ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE {' AND '.join(conditions)}
    """
    
    cursor.execute(query, params)
    return cursor.fetchall()

# Search by multiple attributes
results = search_by_attributes(cursor, Color="Black", Material="Nylon")

Prestandaöverväganden

Utvärdera när glesa kolonner ger lagringsfördelar, utvärdera overheadkostnader och använd prestandaövervakning för att optimera din gleskolumndesign.

När ska man använda glesa kolumner

Bra kandidater för gles kolumner inkluderar:

Kolumner med mer än 60–70% NULL-värden.
Breda tabeller med många valfria kolumner.
Arbetsbelastningar där lagringsoptimering är en prioritet.

Undvik att använda glesa kolumner när:

De flesta rader har värden (varje icke-NULL-värde lägger till 4 byte i overhead).
Kolumnen används ofta i WHERE-klausuler.
Kolumnen är en del av det klustrade indexet.

Kolla lagringsbesparingar

Jämför lagringsstorleken för de glesa och icke-glesa versionerna av samma tabell.

-- Compare storage with and without sparse
EXEC sp_spaceused 'ProductAttributes';
EXEC sp_spaceused 'ProductAttributesWithoutSparse';

Indexöverväganden

Du kan indexera glesa kolumner. Filtrerade index fungerar bra för gles data eftersom de hoppar över NOLL-rader:

CREATE INDEX IX_Products_Color
ON ProductAttributes(Color)
WHERE Color IS NOT NULL;

Massåtgärder

Optimera insättningar av flera produkter med glesa kolumner genom att bygga kolumnuppsättnings-XML i Python innan rader skickas till bulkcopy().

Massinfogning med glesa kolumner

Använd bulk-copy för att effektivt infoga flera produkter med glesa attribut.

def bulk_insert_with_attributes(conn, products: list[dict]):
    """Bulk insert products with sparse attributes."""
    rows = []
    for product in products:
        attrs = product.get("attributes", {})
        xml = "".join(f"<{k}>{v}</{k}>" for k, v in attrs.items()) or None
        rows.append((product["id"], product["name"], xml))
    
    cursor = conn.cursor()
    result = cursor.bulkcopy("ProductAttributesWithSet", rows)
    return result["rows_copied"]

# Usage
products = [
    {"id": 100, "name": "Widget A", "attributes": {"Color": "Red", "Size": "Small"}},
    {"id": 101, "name": "Widget B", "attributes": {"Weight": 1.5, "Material": "Steel"}},
    {"id": 102, "name": "Widget C", "attributes": {}},  # No sparse attributes
]
bulk_insert_with_attributes(conn, products)
conn.commit()

Metodtips

Följ valideringsmönster, använd kolumnuppsättningar för flexibilitet och övervaka kolumnernas gleshet för att säkerställa att din glesa kolumndesign uppfyller prestanda- och underhållsmålen.

Validera glesa kolumnvärden

Kontrollera att alla glesa attribut överensstämmer med den tillåtna uppsättningen innan du infogar data.

# Sparse columns have the same constraints as regular columns
# The SPARSE keyword only affects storage

def validate_and_insert(cursor, product_id: int, name: str, attributes: dict):
    """Insert with validation."""
    allowed_attributes = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    
    invalid = set(attributes.keys()) - allowed_attributes
    if invalid:
        raise ValueError(f"Unknown attributes: {invalid}")
    
    xml_parts = [f"<{k}>{v}</{k}>" for k, v in attributes.items()]
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": "".join(xml_parts) or None})

Använd kolumnuppsättningar för flexibilitet

Kolumnuppsättningar förenklar arbetet med glesa kolumner:

Lägg till nya glesa kolumner utan kodändringar.
Lagra dynamiska attribut.
Serialisera och deserialisera XML automatiskt.

Utan en kolumnuppsättning behöver du explicita kolumnlistor. Med en kolumnuppsättning hanterar XML-kolumnen dynamiska attribut automatiskt.

Övervaka NULL-procenter

Analysera NOLLPROCENTEN för en kolumn för att avgöra om den är en bra kandidat för optimering av gles kolumner.

def analyze_sparseness(cursor, table: str, column: str) -> float:
    """Check if column is a good sparse candidate."""
    # Validate identifiers to prevent SQL injection
    import re
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', column):
        raise ValueError(f"Invalid column name: {column}")

    cursor.execute(f"""
        SELECT 
            COUNT(*) AS TotalRows,
            SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END) AS NullRows
        FROM {table}
    """)
    
    row = cursor.fetchone()
    null_percentage = (row.NullRows / row.TotalRows * 100) if row.TotalRows > 0 else 0
    
    print(f"Column {column}: {null_percentage:.1f}% NULL")
    print(f"Recommendation: {'Good sparse candidate' if null_percentage > 60 else 'Keep as regular column'}")
    
    return null_percentage

Feedback

Var den här sidan till hjälp?

Last updated on 2026-07-22