Python sqlite3.Blob.len Method

Last modified April 15, 2025

This comprehensive guide explores Python's sqlite3.Blob.__len__ method, which returns the length of a BLOB (Binary Large Object) in bytes. We'll cover basic usage, practical examples, and best practices.

Basic Definitions

A BLOB is a SQLite data type for storing binary data. The sqlite3.Blob class provides access to BLOB data in Python. The __len__ method returns the size of the BLOB in bytes.

Key characteristics: it's called when using len on a Blob object, returns an integer, and works only on open Blob objects. The method provides efficient size checking without loading the entire BLOB into memory.

Basic BLOB Length Check

This example demonstrates creating a table with a BLOB column and checking its length using the __len__ method.

basic_blob_length.py

import sqlite3

with sqlite3.connect('blobs.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, data BLOB)")
    
    # Insert sample binary data
    binary_data = b'\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00'
    conn.execute("INSERT INTO images (data) VALUES (?)", (binary_data,))
    
    # Open BLOB and check length
    blob = conn.blobopen('images', 'data', 1, 'main')
    print(f"BLOB length: {len(blob)} bytes")  # Calls __len__
    blob.close()

This example creates a table, inserts binary data, then opens the BLOB to check its length. The len function internally calls __len__.

Note we properly close the BLOB object after use. The length matches the size of our inserted binary string (24 bytes in this case).

Checking Empty BLOB Length

This example shows how __len__ behaves with empty BLOBs.

empty_blob.py

import sqlite3

with sqlite3.connect('empty_blobs.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS empty_blobs (id INTEGER PRIMARY KEY, empty_data BLOB)")
    
    # Insert empty BLOB
    conn.execute("INSERT INTO empty_blobs (empty_data) VALUES (?)", (b'',))
    
    # Check length of empty BLOB
    with conn.blobopen('empty_blobs', 'empty_data', 1, 'main') as blob:
        print(f"Empty BLOB length: {len(blob)} bytes")  # Returns 0

Here we insert an empty bytes object and verify that __len__ returns 0. The example uses a context manager for automatic BLOB closing.

Empty BLOBs are valid in SQLite and useful as placeholders or default values. The __len__ method correctly reports their zero size.

Comparing BLOB Lengths

This example demonstrates comparing lengths of multiple BLOBs in a table.

compare_blobs.py

import sqlite3

with sqlite3.connect('compare_blobs.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY, content BLOB)")
    
    # Insert files of different sizes
    small_file = b'small'
    medium_file = b'medium size content'
    large_file = b'large' * 1000
    
    conn.executemany("INSERT INTO files (content) VALUES (?)",
                    [(small_file,), (medium_file,), (large_file,)])
    
    # Compare lengths
    cursor = conn.cursor()
    cursor.execute("SELECT rowid FROM files ORDER BY rowid")
    for rowid, in cursor:
        with conn.blobopen('files', 'content', rowid, 'main') as blob:
            print(f"File {rowid} size: {len(blob)} bytes")

This code inserts three BLOBs of different sizes and then retrieves each one to compare their lengths. The __len__ method provides the size for each BLOB.

The example shows how to process multiple BLOBs efficiently, using their lengths for comparison or analysis without loading the actual content.

BLOB Length in Transactions

This example demonstrates BLOB length checking within a transaction.

transaction_blob.py

import sqlite3

with sqlite3.connect('transaction.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS documents (id INTEGER PRIMARY KEY, doc BLOB)")
    
    # Start transaction
    with conn:
        # Insert document
        doc_content = b'This is a document' * 100
        conn.execute("INSERT INTO documents (doc) VALUES (?)", (doc_content,))
        
        # Get last inserted rowid
        rowid = conn.execute("SELECT last_insert_rowid()").fetchone()[0]
        
        # Check length within transaction
        with conn.blobopen('documents', 'doc', rowid, 'main') as blob:
            print(f"Document size before commit: {len(blob)} bytes")
    
    # Verify length after commit
    with conn.blobopen('documents', 'doc', rowid, 'main') as blob:
        print(f"Document size after commit: {len(blob)} bytes")

This example shows that __len__ works both within and outside transactions. The BLOB length remains consistent before and after commit.

The context manager ensures proper transaction handling while we check the BLOB size at different points in the operation.

Error Handling with BLOB Length

This example demonstrates error scenarios when using __len__.

error_handling.py

import sqlite3

try:
    with sqlite3.connect('errors.db') as conn:
        conn.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, data BLOB)")
        
        # Try to get length of closed BLOB
        blob = conn.blobopen('test', 'data', 1, 'main')
        blob.close()
        try:
            print(len(blob))  # Will raise Error
        except sqlite3.ProgrammingError as e:
            print(f"Error: {e}")
        
        # Try to get length of non-existent BLOB
        try:
            with conn.blobopen('test', 'data', 999, 'main') as blob:
                print(len(blob))
        except sqlite3.OperationalError as e:
            print(f"Error: {e}")
            
except sqlite3.Error as e:
    print(f"Database error: {e}")

This example shows two common error cases: calling __len__ on a closed BLOB and trying to open a non-existent BLOB. Both raise appropriate exceptions.

Proper error handling is essential when working with BLOBs as operations may fail due to various reasons like closed connections or invalid rowids.

BLOB Length with Different Data Types

This example shows how __len__ behaves with different binary data.

data_types.py

import sqlite3
import pickle

with sqlite3.connect('datatypes.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS objects (id INTEGER PRIMARY KEY, obj BLOB)")
    
    # Store different Python objects as BLOBs
    objects = [
        pickle.dumps([1, 2, 3]),  # List
        pickle.dumps({'a': 1}),    # Dict
        pickle.dumps(3.14159),     # Float
        pickle.dumps("Hello"),     # String
        pickle.dumps(True)         # Boolean
    ]
    
    conn.executemany("INSERT INTO objects (obj) VALUES (?)", [(o,) for o in objects])
    
    # Check lengths of serialized objects
    cursor = conn.cursor()
    cursor.execute("SELECT rowid FROM objects ORDER BY rowid")
    for rowid, in cursor:
        with conn.blobopen('objects', 'obj', rowid, 'main') as blob:
            print(f"Object {rowid} serialized size: {len(blob)} bytes")

This example serializes different Python objects to BLOBs using pickle and checks their sizes. The __len__ method reports the serialized size.

The example demonstrates that __len__ works with any binary data, regardless of its original Python type or content.

BLOB Length in Read-only Mode

This example demonstrates using __len__ with read-only BLOBs.

readonly_blob.py

import sqlite3

with sqlite3.connect('readonly.db') as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS archives (id INTEGER PRIMARY KEY, zip_data BLOB)")
    
    # Insert sample zip data
    zip_data = b'PK\x03\x04\x14\x00\x00\x00\x00\x00'  # Partial ZIP header
    conn.execute("INSERT INTO archives (zip_data) VALUES (?)", (zip_data,))
    
    # Open BLOB in read-only mode and check length
    with conn.blobopen('archives', 'zip_data', 1, 'main', readonly=True) as blob:
        print(f"ZIP data length: {len(blob)} bytes")
        
        try:
            blob.write(b'new data')  # Will fail
        except sqlite3.OperationalError as e:
            print(f"Expected error when writing: {e}")

This example opens a BLOB in read-only mode and verifies that __len__ works correctly even without write permissions. Attempting to modify the BLOB raises an error.

Read-only mode is useful when you only need to inspect BLOB metadata like size without modifying the content.

Best Practices

Always close BLOB objects: Use context managers for automatic cleanup
Check length before reading: Avoid buffer overflows
Handle errors gracefully: BLOB operations can fail
Use for large binaries: BLOBs are ideal for large data
Consider memory usage: Length check doesn't load content

Source References

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Python tutorials.

Ebooks

Python sqlite3.Blob.__len__ Method