Handling PostgreSQL BLOB data in Python

PostgreSQL is an open-source, object-relational database management system that supports diverse data types including BLOB (Binary Large Object) data. BLOBs are used to store large binary files such as images, videos, audio files, and documents directly in the database.

To work with PostgreSQL BLOB data in Python, we need the psycopg2 library, which provides a Python interface for PostgreSQL databases.

Installation

Install the psycopg2 library using pip ?

pip install psycopg2-binary

Creating a Table with BLOB Column

In PostgreSQL, use the BYTEA data type to store binary data. This data type can handle binary data up to 1 GB in size.

import psycopg2

# Database connection
conn = psycopg2.connect(
    host='localhost',
    database='testdb',
    user='postgres',
    password='password'
)

cur = conn.cursor()

# Create table with BYTEA column for BLOB data
cur.execute("""
    CREATE TABLE IF NOT EXISTS file_storage (
        id SERIAL PRIMARY KEY,
        filename VARCHAR(100),
        file_data BYTEA
    )
""")

conn.commit()
print("Table created successfully")

Table created successfully

Inserting BLOB Data

To insert binary data, read the file in binary mode and use psycopg2.Binary() to convert it for database storage ?

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    database='testdb', 
    user='postgres',
    password='password'
)

cur = conn.cursor()

# Sample text file for demonstration
with open('sample.txt', 'w') as f:
    f.write('This is sample content for BLOB demonstration.')

# Read file and insert as BLOB
with open('sample.txt', 'rb') as file:
    binary_data = file.read()
    blob_data = psycopg2.Binary(binary_data)
    
    insert_query = """
        INSERT INTO file_storage (filename, file_data) 
        VALUES (%s, %s)
    """
    
    cur.execute(insert_query, ('sample.txt', blob_data))

conn.commit()
print("File inserted successfully as BLOB")

File inserted successfully as BLOB

Retrieving BLOB Data

To retrieve and save BLOB data back to a file, use a SELECT query and write the binary data to a new file ?

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    database='testdb',
    user='postgres', 
    password='password'
)

cur = conn.cursor()

# Retrieve BLOB data
cur.execute("SELECT id, filename, file_data FROM file_storage WHERE filename = %s", ('sample.txt',))

row = cur.fetchone()
if row:
    file_id, filename, blob_data = row
    
    # Save BLOB data to new file
    output_filename = f"retrieved_{filename}"
    with open(output_filename, 'wb') as output_file:
        output_file.write(blob_data)
    
    print(f"File ID: {file_id}")
    print(f"Original filename: {filename}")
    print(f"BLOB data saved as: {output_filename}")
    
    # Verify content
    with open(output_filename, 'r') as f:
        content = f.read()
        print(f"Retrieved content: {content}")

conn.close()

File ID: 1
Original filename: sample.txt
BLOB data saved as: retrieved_sample.txt
Retrieved content: This is sample content for BLOB demonstration.

Complete Example with Error Handling

Here's a comprehensive example that demonstrates proper BLOB handling with error management ?

import psycopg2
import os

def handle_postgresql_blob():
    conn = None
    try:
        # Connect to PostgreSQL
        conn = psycopg2.connect(
            host='localhost',
            database='testdb',
            user='postgres',
            password='password'
        )
        
        cur = conn.cursor()
        
        # Create table
        cur.execute("""
            CREATE TABLE IF NOT EXISTS document_store (
                id SERIAL PRIMARY KEY,
                doc_name VARCHAR(255),
                doc_data BYTEA,
                file_size INTEGER
            )
        """)
        
        # Create sample file
        sample_content = "PostgreSQL BLOB example with binary data handling."
        with open('document.txt', 'w') as f:
            f.write(sample_content)
        
        # Insert BLOB data
        with open('document.txt', 'rb') as file:
            binary_data = file.read()
            file_size = len(binary_data)
            
            insert_query = """
                INSERT INTO document_store (doc_name, doc_data, file_size)
                VALUES (%s, %s, %s) RETURNING id
            """
            
            cur.execute(insert_query, ('document.txt', psycopg2.Binary(binary_data), file_size))
            doc_id = cur.fetchone()[0]
            
        conn.commit()
        print(f"Document inserted with ID: {doc_id}")
        
        # Retrieve and save BLOB data
        cur.execute("SELECT doc_name, doc_data, file_size FROM document_store WHERE id = %s", (doc_id,))
        
        result = cur.fetchone()
        if result:
            doc_name, doc_data, file_size = result
            
            # Save retrieved data
            output_file = f"output_{doc_name}"
            with open(output_file, 'wb') as f:
                f.write(doc_data)
            
            print(f"Retrieved document: {doc_name}")
            print(f"File size: {file_size} bytes")
            print(f"Saved as: {output_file}")
        
        cur.close()
        
    except (Exception, psycopg2.Error) as error:
        print(f"Error handling PostgreSQL BLOB: {error}")
    
    finally:
        if conn:
            conn.close()
            print("Database connection closed")

# Run the example
handle_postgresql_blob()

Document inserted with ID: 1
Retrieved document: document.txt
File size: 53 bytes
Saved as: output_document.txt
Database connection closed

Key Points

• Data Type: Use BYTEA for storing binary data in PostgreSQL
• Conversion: Use psycopg2.Binary() to convert binary data for insertion
• File Handling: Always open files in binary mode ('rb') when reading for BLOB storage
• Memory Usage: Be cautious with large files as they are loaded entirely into memory
• Error Handling: Always use try-except blocks for database operations

Conclusion

PostgreSQL BLOB handling in Python is straightforward using psycopg2 and the BYTEA data type. Use psycopg2.Binary() for inserting binary data and standard file operations for retrieval. Always implement proper error handling for production applications.