Használj ritka oszlopokat és oszlophalmazokat mssql-python segítségével

A ritkán kitöltött oszlopok a Microsoft SQL egyik tárolásoptimalizálási megoldását jelentik a NULL értékek kezelésére olyan táblákban, amelyek sok null értéket is megengedő oszlopot tartalmaznak. A kliens alkalmazások a ritka oszlopokat szabályos oszlopként látják. Az mssql-python illezser úgy olvassa és írja őket, mint bármely más oszlopot, külön kezelés nélkül.

Feature	Leírás
Ritka oszlopok	NULL értékek nulla tárolást használnak
Oszlopkészletek	Az összes ritka oszlop XML-ábrázolása
Széles táblák	Legfeljebb 30 000 oszlop támogatása

Megjegyzés:

A ritka oszlopok a kiszolgálóoldal egyik funkcióját jelentik. Az mssql-python illesztőprogramnak nincs szüksége speciális konfigurációra vagy API-ra a ritka oszlopok működtetéséhez. Az egyetlen kliens-látható különbség: amikor oszlophalmazokat használsz, azok XML reprezentációt adnak vissza a ritka oszlopértékekről.

Legalkalmasabb a következő feladatokra:

Táblázatok 20-50%+ NULL értékekkel.
Dokumentumtárolás változó attribútumokkal.
EAV (Entity-Attribute-Value) mintázatok.
Szenzoradatok számos opcionális mérési értékkel.

Készíts ritka oszlopokat

Definiáld a ritka oszlopokat a táblázat sémájában úgy, hogy a SPARSE NULL módosítót olyan oszlopokhoz adjuk hozzá, amelyek gyakran tartalmaznak NULL értékeket.

Alapvető ritka oszloptáblázat

Készíts egy táblázatot ritka oszlopokkal opcionális attribútumokhoz.

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
);

oszlopkészlettel

Adj hozzá egy oszlopkészletet, amely egyszerre biztosít XML-hozzáférést az összes ritka oszlophoz.

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
);

Ritka oszlop adatainak beszúrása

Szúrjon be adatokat a ritka oszlopokba név alapján, ugyanúgy, mint a normál oszlopok esetében.

Egyedi oszlopok beépítése

Szúrjon be olyan termékeket, amelyeknél adott ritka oszlopértékek vannak kitöltve.

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()

Oszlophalmazon keresztül történő beszúrás (XML)

Egyszerre több ritka oszlop értékét szúrhatja be, ha XML-t ad át az oszlopkészletnek.

# 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()

Dinamikus attribútum-beillesztés

Létrehozz egy függvényt, amely a dinamikus attribútumokat szótárként fogadja el, és automatikusan építi az XML-t.

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()

Ritka oszlopok lekérdezése

Kérdezze a ritka oszlopokat név szerint, vagy egyszerre kérje le az összes ritka értéket az oszlophalmazon keresztül.

Egyedi oszlopok lekérdezése

A speciális ritka oszlopokat a szabványos SELECT szintaxissal kérdezzük.

# 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}")

Lekérdezés oszlophalmazon keresztül

Lekérje az összes ritka oszlopértéket XML-ként az oszlophalmazból.

# 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}")

Oszlopkészlet XML elemzése Pythonban

Az XML-t az oszlop készletéből elemzed, hogy Python szótárrá alakítsd át a könnyebb manipuláció érdekében.

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'}

Lekérdezés a SELECT * utasítással

Amikor a SELECT * gombot használod, az oszlopkészlet egyetlen XML oszlopként tér vissza az egyedi ritka oszlopok helyett.

# 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]}")

Az egyes oszlopokat kifejezetten kérdezze le

Egyedi ritka oszlopok lekéréséhez egy oszlophalmazsal rendelkező táblából kifejezetten listázzuk fel őket a SELECT záradékban.

# 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}")

Frissítse a ritka oszlopokat

Frissítse az egyes ritka oszlopokat név szerint, vagy cserélje le az összes ritka értéket egyszerre az XML oszlophalmazon keresztül.

Egyes oszlopok frissítése

Frissítse a specifikus ritka oszlopértékeket a szabványos SQL UPDATE szintaxissal.

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

Frissítés oszlophalmazon keresztül

Cseréld ki az összes ritka oszlopértéket azzal, hogy közvetlenül frissíted az XML oszlophalmazt.

# 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

Részleges frissítés oszlopkészleten keresztül

Csak bizonyos ritka attribútumokat frissíts, miközben megőrizve a frissítésben nem szereplő egyéb attribútumok értékét.

# 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()

Dinamikus oszlopminták

Rugalmas lekérdezéseket építs, amelyek dinamikusan keresnek a ritka oszlopok között, engedélyezett listákat használva az oszlopnevek ellenőrzésére és az SQL injekciós támadások megelőzésére.

EAV-típusú adatok lekérdezése

Keress termékeket egy adott attribútumnév és érték alapján Entity-Attribute-Value (EAV) mintás egyeztetéssel.

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")

Rugalmas attribútumkeresés

Keress olyan termékeket, amelyek egyszerre több opcionális attribútumot is megfelelnek.

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")

Teljesítménnyel kapcsolatos szempontok

Értékelje, hogy a ritka oszlopok tárolási előnyöket nyújtanak-e, értékeld a költségköltségeket, és használd a teljesítményfigyelést a ritka oszloptervezés optimalizálására.

Mikor érdemes ritka oszlopokat használni

A ritka oszlopokhoz megfelelő jelöltek a következők:

60–70%-nál több NULL értéket tartalmazó oszlopok.
Széles asztalok sok opcionális oszloppal.
Olyan munkaterhelések, ahol a tárolás optimalizálása prioritás.

Kerüld a ritka oszlopok használatát, ha:

A legtöbb sornak vannak értékei (minden nem NULL érték 4 bájt plusz költséget ad hozzá).
Az oszlopot gyakran használják a WHERE klauzulákban.
Az oszlop a klaszterelt index része.

Ellenőrizd a tárolási megtakarításokat

Hasonlítsuk össze ugyanannak a táblázatnak a ritka és nem ritka verzióinak tárolóméretét.

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

Indexelési szempontok

A ritka oszlopok indexelhetők. A szűrt indexek jól működnek ritka adatnál, mert kihagyják a NULL sorokat:

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

Tömeges műveletek

Több termék ritkán kitöltött oszlopokkal történő beszúrásának optimalizálása az oszlopkészlet XML-jének Pythonban történő összeállításával, mielőtt a sorokat a bulkcopy() számára átadná.

Tömeges beszúrás ritka oszlopokkal

Használj tömeges másolást arra, hogy hatékonyan több terméket is beilleszts ritka attribútumokkal.

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()

Bevált gyakorlatok

Kövesd az érvényesítési mintákat, használj oszlopkészleteket a rugalmasság érdekében, és figyeld az oszlopok ritkaságát, hogy biztosítsd a ritka oszloptervezésed megfeleljen a teljesítmény- és karbantarthatósági céljaknak.

Ritka oszlopértékek ellenőrzése

Ellenőrizd, hogy minden ritka attribútum egyezik-e az engedélyezett halmazsal, mielőtt adat illesztenél.

# 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})

Használjon oszlopkészleteket a nagyobb rugalmasság érdekében

Az oszlopkészletek egyszerűsítik a ritka oszlopokkal való munkát:

Új ritka oszlopokat adj hozzá kódváltoztatás nélkül.
Tárolja a dinamikus attribútumokat.
Automatikusan serializáld és deserializáld az XML-t.

Oszlopkészlet nélkül explicit oszloplistákra van szükség. Egy oszlop beállítással az XML oszlop automatikusan kezeli a dinamikus attribútumokat.

Kövesd nyomon a NULL-arányokat

Elemezd az oszlop NULL százalékát, hogy megállapítsd, jó jelölt-e a ritka oszlop optimalizálására.

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

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2026-07-22