Arbeta med binär data

mssql-python-drivrutinen stöder binary, varbinary, och image datatyper för Microsoft SQL.

Microsoft SQL lagrar binär data i dessa kolumntyper:

Type	Beskrivning	Maximal storlek
binary(n)	Binära data med fast längd.	8 000 byte
varbinary(n)	Binära data med variabel längd.	8 000 byte
varbinary(max)	Stora binärdata.	2 GB
image	Äldre stora binärdata (föråldrad).	2 GB

mssql-python-drivrutinen returnerar binär data som Python-objektbytes.

När man ska lagra binär i databasen jämfört med filsystemet:

• Lagra i databasen när filer är små (under 1 MB), transaktionell konsekvens med annan data är viktig, eller när du behöver säkerhetskopiera data och filer tillsammans.
• Lagra i filsystemet (eller Azure Blob Storage) när filer är stora (över 1 MB), du behöver CDN-leverans, eller du behöver leverera filer direkt till klienter utan databas-rundturer.
• Som en medelväg lagrar Microsoft SQL:s FILESTREAM-funktion data i filsystemet med transaktionell konsistens.

Infoga binärdata

Passa Python-objekt bytes som parametrar; drivrutinen mappar dem till varbinärkolumner.

Infoga bytes direkt

Skapa ett Python-objekt bytes och skicka det som en frågeparameter:

import mssql_python

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

# Create a table to hold binary documents
cursor.execute("""
    CREATE TABLE #Documents (
        ID INT IDENTITY PRIMARY KEY,
        Name NVARCHAR(255),
        Content VARBINARY(MAX),
        Size INT NULL,
        ContentHash VARBINARY(32) NULL
    )
""")

# Binary data as bytes
binary_data = b'\x00\x01\x02\x03\x04\x05'

cursor.execute(
    "INSERT INTO #Documents (Name, Content) VALUES (%(name)s, %(content)s)",
    {"name": "sample.bin", "content": binary_data}
)
conn.commit()

Infoga från filen

Läs en fil i binärt läge och infoga dess innehåll:

def insert_file(cursor, file_path: str, name: str):
    """Insert a file as binary data."""
    with open(file_path, "rb") as f:
        content = f.read()
    
    cursor.execute(
        "INSERT INTO #Documents (Name, Content, Size) VALUES (%(name)s, %(content)s, %(size)s)",
        {"name": name, "content": content, "size": len(content)}
    )

insert_file(cursor, "image.png", "profile_picture.png")
conn.commit()

Infoga bildfil

Lagra bildfiler med metadata genom att identifiera MIME-typer utifrån filtillägg:

# Create a table to hold images
cursor.execute("""
    CREATE TABLE #Images (
        ID INT IDENTITY PRIMARY KEY,
        FileName NVARCHAR(255),
        FileSize INT NULL,
        ContentType NVARCHAR(100) NULL,
        Width INT NULL,
        Height INT NULL,
        ImageData VARBINARY(MAX),
        Description NVARCHAR(MAX) NULL
    )
""")

def insert_image(cursor, image_path: str, description: str):
    """Insert an image into the database."""
    import os
    
    with open(image_path, "rb") as f:
        image_data = f.read()
    
    cursor.execute("""
        INSERT INTO #Images (FileName, FileSize, ContentType, ImageData, Description)
        VALUES (%(filename)s, %(filesize)s, %(content_type)s, %(data)s, %(desc)s)
    """, {
        "filename": os.path.basename(image_path),
        "filesize": len(image_data),
        "content_type": get_content_type(image_path),
        "data": image_data,
        "desc": description
    })

def get_content_type(path: str) -> str:
    """Determine MIME type from file extension."""
    ext = path.lower().split(".")[-1]
    types = {
        "png": "image/png",
        "jpg": "image/jpeg",
        "jpeg": "image/jpeg",
        "gif": "image/gif",
        "pdf": "application/pdf",
    }
    return types.get(ext, "application/octet-stream")

Hämta binära data

Drivrutinen returnerar varbinära och binära kolumnvärden som Python-objektbytes.

Hämta binär kolumn

Fråga binära kolumner och inspektera det returnerade bytes objektet:

cursor.execute(
    "SELECT LargePhotoFileName, LargePhoto FROM Production.ProductPhoto WHERE ProductPhotoID = %(id)s",
    {"id": 70}
)
row = cursor.fetchone()

# LargePhoto is bytes
print(type(row.LargePhoto))  # <class 'bytes'>
print(len(row.LargePhoto))   # Number of bytes

Spara till fil

Hämta binär data och skriv den till disken:

def save_photo(cursor, photo_id: int, output_path: str):
    """Retrieve binary data and save to file."""
    cursor.execute(
        "SELECT LargePhoto FROM Production.ProductPhoto WHERE ProductPhotoID = %(id)s",
        {"id": photo_id}
    )
    row = cursor.fetchone()
    
    if row and row.LargePhoto:
        with open(output_path, "wb") as f:
            f.write(row.LargePhoto)
        print(f"Saved {len(row.LargePhoto)} bytes to {output_path}")
    else:
        print("Photo not found or empty")

save_photo(cursor, 70, "output.gif")

Exportera binära rader till filer

Exportera flera binära rader till lokala filer:

import os

def export_photos(cursor, output_dir: str):
    """Export product photos to a directory."""
    os.makedirs(output_dir, exist_ok=True)
    
    cursor.execute("""
        SELECT ProductPhotoID, LargePhotoFileName, LargePhoto
        FROM Production.ProductPhoto
        WHERE ProductPhotoID > 1
    """)
    
    count = 0
    for row in cursor:
        output_path = os.path.join(output_dir, f"{row.ProductPhotoID}_{row.LargePhotoFileName}")
        with open(output_path, "wb") as f:
            f.write(row.LargePhoto)
        count += 1
    
    print(f"Exported {count} photos to {output_dir}")

export_photos(cursor, "./photos")

Infoga binära värden för NULL

Pass None för att infoga en SQL NULL i en binär kolumn. Eftersom #Documents är en tillfällig tabell, deklarera parametertyperna genom att använda setinputsizes() först så att drivrutinen binder Content som varbinary:

cursor.setinputsizes([(mssql_python.SQL_WVARCHAR, 255, 0), (mssql_python.SQL_VARBINARY, 0, 0)])
cursor.execute(
    "INSERT INTO #Documents (Name, Content) VALUES (%(name)s, %(content)s)",
    {"name": "empty", "content": None}
)

Note

Drivrutinen härleder normalt parametertyper genom SQLDescribeParam, men den kan inte lösa typmetadata för en tillfällig tabell eller tabellvariabel. Att infoga None i en binary- eller varbinary-kolumn i ett temporärt objekt utan setinputsizes() genererar ProgrammingError: Implicit conversion from data type varchar to varbinary(max) is not allowed. Ange en post för varje parameter, i rätt ordning, och använd en SQL-typkonstant som mssql_python.SQL_VARBINARY för den binära kolumnen. För en vanlig (permanent) tabell avgör drivrutinen typen automatiskt och du kan skicka in None direkt.

Stora mängder binärdata

För filer över några megabyte, infoga hela innehållet i en enda varbinär(max) skrivning istället för att läsa filen i små bitar.

Strömma stora filer

För stora filer, bearbeta i bitar:

def insert_large_file(conn, table: str, file_path: str, chunk_size: int = 8192):
    """Insert large file in chunks using updatetext-style approach."""
    # Validate table name to prevent SQL injection
    import re
    if not re.match(r'^#{0,2}[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")

    cursor = conn.cursor()
    
    # Get file size
    import os
    file_size = os.path.getsize(file_path)
    
    # Insert initial row with empty binary
    cursor.execute(f"""
        INSERT INTO {table} (Name, Content, Size)
        VALUES (%(name)s, 0x, %(size)s);
        SELECT SCOPE_IDENTITY();
    """, {"name": os.path.basename(file_path), "size": file_size})
    
    row_id = cursor.fetchval()
    
    # For modern Microsoft SQL, better to use varbinary(max) and single insert
    # This example shows streaming approach
    with open(file_path, "rb") as f:
        content = f.read()
    
    cursor.execute(f"""
        UPDATE {table} SET Content = %(content)s WHERE ID = %(id)s
    """, {"content": content, "id": row_id})
    
    conn.commit()
    return row_id

Använd masskopiering för binär data

Använd bulkcopy() metoden för att effektivt infoga flera binära filer i en enda operation.

Important

bulkcopy() laddar data över en separat anslutning till servern, så destinationstabellen måste redan existera och vara committerad och synlig för andra sessioner. Om du skapar tabellen i samma skript med autocommit avstängt, anropa conn.commit() före bulkcopy(). Lokala tillfälliga tabeller (#name) stöds inte eftersom de är privata för sessionen som skapade dem. En obekräftad eller ouppnåelig destination gör att bulkcopy() misslyckas med timeout för Failed to retrieve destination metadata.

Som standard mappar bulkcopy() varje värde i en rad till en tabellkolumn baserat på ordningsposition, så varje kolumn måste ha ett värde i rätt ordning. När tabellen har en identitetskolumn eller om du bara fyller vissa kolumner, ange column_mappings för att uttryckligen ange målkolumnerna. Annars flyttas värdena till fel kolumner och bulkcopy() misslyckas.

def bulk_insert_files(conn, table: str, file_paths: list[str]):
    """Bulk insert multiple binary files."""
    import os

    data = []
    for path in file_paths:
        with open(path, "rb") as f:
            content = f.read()
        data.append((os.path.basename(path), content, len(content)))

    cursor = conn.cursor()
    result = cursor.bulkcopy(table, data, column_mappings=["Name", "Content", "Size"])
    conn.commit()
    return result["rows_copied"]

Binära dataoperationer

Använd Microsoft SQL:s HASHBYTES funktion för att jämföra binärt innehåll utan att hämta fullständiga värden.

Jämför binärdata

Hitta binära rader som delar identiskt innehåll genom att beräkna och jämföra innehållshashar i Microsoft SQL Server:

# Find rows with identical binary content by hash
cursor.execute("""
    SELECT LargePhotoFileName, HASHBYTES('SHA2_256', LargePhoto) AS PhotoHash
    FROM Production.ProductPhoto
    WHERE ProductPhotoID > 1
""")

hashes = {}
for row in cursor:
    hash_value = row.PhotoHash  # bytes
    if hash_value in hashes:
        print(f"Duplicate: {row.LargePhotoFileName} matches {hashes[hash_value]}")
    else:
        hashes[hash_value] = row.LargePhotoFileName

Beräkna hash i Python

Beräkna SHA-256-hash i Python och lagra dem tillsammans med binär data:

import hashlib

def insert_with_hash(cursor, name: str, content: bytes):
    """Insert binary data with computed hash."""
    content_hash = hashlib.sha256(content).digest()
    
    cursor.execute("""
        INSERT INTO #Documents (Name, Content, ContentHash)
        VALUES (%(name)s, %(content)s, %(hash)s)
    """, {"name": name, "content": content, "hash": content_hash})

Koda och avkoda binär data

Konvertera binär data till och från base64-kodade strängar:

import base64

# Store base64-encoded string
def insert_base64(cursor, name: str, base64_data: str):
    """Insert base64-encoded data as binary."""
    binary_data = base64.b64decode(base64_data)
    cursor.execute(
        "INSERT INTO #Documents (Name, Content) VALUES (%(name)s, %(content)s)",
        {"name": name, "content": binary_data}
    )

# Retrieve as base64
def get_as_base64(cursor, doc_id: int) -> str:
    """Retrieve binary data as base64 string."""
    cursor.execute("SELECT Content FROM #Documents WHERE ID = %(id)s", {"id": doc_id})
    row = cursor.fetchone()
    return base64.b64encode(row.Content).decode("utf-8")

Arbeta med specifika binära format

Dessa exempel visar hur man validerar och infogar vanliga filformat innan man lagrar dem.

PDF-dokument

Verifiera PDF-signaturer innan du infogar dem:

def insert_pdf(cursor, pdf_path: str, title: str):
    """Insert a PDF document."""
    with open(pdf_path, "rb") as f:
        pdf_data = f.read()
    
    # Verify it's a PDF (magic bytes)
    if not pdf_data.startswith(b'%PDF'):
        raise ValueError("Not a valid PDF file")
    
    cursor.execute(
        "INSERT INTO #Documents (Name, Content) VALUES (%(title)s, %(data)s)",
        {"title": title, "data": pdf_data}
    )

Bilder med PIL/Pillow

Ändra storlek på bilder med Pillow (PIL) innan du lagrar dem för att spara utrymme:

from PIL import Image
import io
import os

def insert_resized_image(cursor, image_path: str, max_size: tuple = (800, 600)):
    """Insert a resized image."""
    # Open and resize
    img = Image.open(image_path)
    img.thumbnail(max_size, Image.LANCZOS)
    
    # Convert to bytes
    buffer = io.BytesIO()
    img.save(buffer, format=img.format or "PNG")
    image_bytes = buffer.getvalue()
    
    cursor.execute("""
        INSERT INTO #Images (FileName, Width, Height, ImageData)
        VALUES (%(name)s, %(width)s, %(height)s, %(data)s)
    """, {
        "name": os.path.basename(image_path),
        "width": img.width,
        "height": img.height,
        "data": image_bytes
    })

def get_image_as_pil(cursor, image_id: int) -> Image.Image:
    """Retrieve image as PIL Image object."""
    cursor.execute("SELECT ImageData FROM #Images WHERE ID = %(id)s", {"id": image_id})
    row = cursor.fetchone()
    return Image.open(io.BytesIO(row.ImageData))

Komprimerad data

Minska lagringsutrymmet genom att komprimera binär data med gzip före insättning:

import gzip

def insert_compressed(cursor, name: str, data: bytes):
    """Insert data with gzip compression."""
    compressed = gzip.compress(data)
    
    cursor.execute(
        "INSERT INTO #Documents (Name, Content, Size) VALUES (%(name)s, %(content)s, %(size)s)",
        {"name": name, "content": compressed, "size": len(data)}
    )

def get_decompressed(cursor, data_id: int) -> bytes:
    """Retrieve and decompress data."""
    cursor.execute(
        "SELECT Content FROM #Documents WHERE ID = %(id)s",
        {"id": data_id}
    )
    row = cursor.fetchone()
    return gzip.decompress(row.Content)

Metodtips

Använd dessa riktlinjer för att välja rätt kolumntyp och lagringsstrategi för binär data.

Använd lämpliga kolumntyper

Välj kolumntypen baserat på dina datas egenskaper:

För olika binära användningsfall, välj lämplig Microsoft SQL-datatyp:

-- For small fixed-size binary (for example, hashes, UUIDs)
binary(32)      -- SHA-256 hash

-- For variable-size binary up to 8KB (for example, thumbnails, small icons)
varbinary(8000)

-- For large binary data (for example, documents, images)
varbinary(max)  -- Up to 2GB

Tänk på FILESTREAM för stora filer

För stora filer (över 1 MB) överväg Microsoft SQL FILESTREAM-funktionen, som lagrar data i filsystemet. FILESTREAM kräver serverkonfiguration innan användning:

# FILESTREAM-enabled databases store large binaries more efficiently
# Access is still through normal queries but storage is file-based
large_binary_data = b"\x25\x50\x44\x46" + b"\x00" * 100  # sample data

cursor.execute("""
    CREATE TABLE ##FileStreamDemo (Name NVARCHAR(100), Document VARBINARY(MAX))
""")
cursor.execute("""
    INSERT INTO ##FileStreamDemo (Name, Document)
    VALUES (%(name)s, %(content)s)
""", {"name": "large_doc.pdf", "content": large_binary_data})

cursor.execute("SELECT Name, DATALENGTH(Document) AS DocSize FROM ##FileStreamDemo")
row = cursor.fetchone()
print(f"{row.Name}: {row.DocSize} bytes")

Validera binärdata

Validera filtyper genom att kontrollera magic bytes (filsignaturer) innan du lagrar binär data:

def insert_safe_image(cursor, name: str, data: bytes):
    """Insert image with validation."""
    # Check file signatures (magic bytes)
    signatures = {
        b'\x89PNG': 'image/png',
        b'\xff\xd8\xff': 'image/jpeg',
        b'GIF87a': 'image/gif',
        b'GIF89a': 'image/gif',
    }
    
    content_type = None
    for sig, mime in signatures.items():
        if data.startswith(sig):
            content_type = mime
            break
    
    if content_type is None:
        raise ValueError("Unknown or unsupported image format")
    
    cursor.execute("""
        INSERT INTO #Images (FileName, ContentType, ImageData)
        VALUES (%(name)s, %(type)s, %(data)s)
    """, {"name": name, "type": content_type, "data": data})

Relaterat innehåll

• Datatypsmappningar
• Masskopiering
• Hantera NULL-värden