SQLite BLOB Handling with Examples and Code

SQLite BLOB

BLOB (Binary Large Object) is a data type in SQLite used to store large binary data such as images, videos, audio files, or any binary data. Unlike text or numeric types, BLOBs can store raw data in an unstructured format, making it ideal for multimedia or binary storage in databases.

Syntax

-- Syntax for creating a table with a BLOB column  
CREATE TABLE table_name (  
    id INTEGER PRIMARY KEY,  
    name TEXT,  
    file_data BLOB  
);  

-- Syntax for inserting BLOB data  
INSERT INTO table_name (name, file_data) VALUES ('file_name', ?);  

-- Syntax for retrieving BLOB data  
SELECT file_data FROM table_name WHERE id = ?;  

Example

Storing and retrieving an image file in an SQLite database using Python.

Code:

# Step 1: Import the SQLite library  
import sqlite3  

# Step 2: Connect to the SQLite database (or create one if it doesn't exist)  
# In this case, the database file is named "files.db"  
connection = sqlite3.connect("files.db")  

# Step 3: Create a cursor object to interact with the database  
cursor = connection.cursor()  

# Step 4: Create a table named "files" with a BLOB column to store binary data  
cursor.execute("""  
    CREATE TABLE IF NOT EXISTS files (  
        id INTEGER PRIMARY KEY,    -- Unique identifier for each row  
        name TEXT,                 -- Name of the file  
        file_data BLOB             -- Binary data of the file  
    );  
""")  

# Step 5: Read an image file in binary mode  
with open("example_image.jpg", "rb") as file:  
    binary_data = file.read()  # Read the entire file content as binary data  

# Step 6: Insert the binary data into the "files" table  
# Use parameterized queries to safely insert data  
cursor.execute("""  
    INSERT INTO files (name, file_data)  
    VALUES (?, ?);  
""", ("example_image.jpg", binary_data))  

# Step 7: Commit the transaction to save changes to the database  
connection.commit()  

# Step 8: Retrieve the binary data from the database  
# Query the "files" table for a specific file by its ID  
cursor.execute("""  
    SELECT file_data FROM files WHERE id = ?;  
""", (1,))  # Retrieve the file with ID = 1  

# Step 9: Fetch the binary data from the query result  
retrieved_data = cursor.fetchone()[0]  

# Step 10: Write the binary data to a new file  
# Save the retrieved data as a new image file  
with open("retrieved_image.jpg", "wb") as output_file:  
    output_file.write(retrieved_data)  # Write the binary data to the file  

# Step 11: Close the database connection  
connection.close()  

Explanation:

1. Creating the Table:

• A table named files is created with three columns: id (integer, primary key), name (text), and file_data (BLOB to store binary content).

2. Reading Binary Data:

• The open() function is used in binary mode ("rb") to read an image file (example_image.jpg) into memory as binary data.

3. Inserting Binary Data:

• The INSERT statement with parameterized queries (?) is used to safely insert the binary data into the file_data column.

4. Retrieving Binary Data:

• The SELECT statement fetches the binary data for a specific file using its id.
• The fetchone() method retrieves the binary data as a tuple, and the [0] index is used to access the first (and only) value.

5. Saving Binary Data:

• The retrieved binary data is written back to a new file (retrieved_image.jpg) using the open() function in binary write mode ("wb").

6. Closing the Connection:

• The connection.close() method ensures proper cleanup and closure of the database connection.

Additional Information

• File Size Limitation:
• The maximum size of a BLOB in SQLite is determined by the SQLITE_MAX_LENGTH compile-time option. By default, it is 1 GB.
• Performance Consideration:
• Storing large files directly in the database can impact performance. For large files, consider storing them on disk and saving only their file paths in the database.
• Applications of BLOB:
• Storing images, documents, audio, and video files in applications like content management systems, media libraries, and mobile apps.

Practical Guides to SQLite Snippets and Examples.