SQLite BLOB

Summary: in this tutorial, you will learn about SQLite BLOB type to store binary data in the database.

Introduction to SQLite BLOB

BLOB stands for Binary Large Object. In SQLite, you can use the BLOB data type to store binary data such as images, video files, or any raw binary data.

Here’s the syntax for declaring a column with the BLOB type:

column_name BLOBCode language: SQL (Structured Query Language) (sql)

For example, the following statement creates a table called documents:

CREATE TABLE documents(
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    data BLOB NOT NULL
);Code language: SQL (Structured Query Language) (sql)

In the documents table, the data column has the data type of BLOB. Therefore, you can store binary data in it.

Typically, you’ll use an external program to read a file such as an image, and insert the binary into a SQLite database.

SQLite BLOB example

For the demonstration, we’ll use Python to read the binary data from an image file, insert it into an SQLite database, retrieve the BLOB data back, and write the BLOB data as an image.

Here’s the program:

import sqlite3

# Connect to an sqlite database
conn = sqlite3.connect('my.db')
cursor = conn.cursor()

# Create the documents table
cursor.execute('''CREATE TABLE IF NOT EXISTS documents(
                    id INTEGER PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    data BLOB NOT NULL
                );''')

# Insert binary data
with open('image.jpg', 'rb') as file:
    image_data = file.read()
    cursor.execute("INSERT INTO documents (title, data) VALUES (?,?)", ('JPG Image',image_data,))

# Retrieve binary data
cursor.execute("SELECT data FROM documents WHERE id = 1")
data = cursor.fetchone()[0]

with open('stored_image.jpg', 'wb') as file:
    file.write(data)

# Commit changes and close the database connection
conn.commit()
conn.close()Code language: Python (python)

How it works.

First, import the sqlite3 module:

import sqlite3Code language: SQL (Structured Query Language) (sql)

Second, connect to the my.db sqlite database file and create a cursor:

conn = sqlite3.connect('my.db')
cursor = conn.cursor()Code language: SQL (Structured Query Language) (sql)

Third, create the documents table:

cursor.execute('''CREATE TABLE documents(
                    id INTEGER PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    data BLOB NOT NULL
                );''')Code language: SQL (Structured Query Language) (sql)

Fourth, read data from image.jpg file and insert it into the documents table:

with open('image.jpg', 'rb') as file:
    image_data = file.read()
    cursor.execute("INSERT INTO documents (title, data) VALUES (?,?)", ('image',image_data,))Code language: SQL (Structured Query Language) (sql)

Fifth, retrieve the blob data from the documents table:

cursor.execute("SELECT data FROM documents WHERE id = 1")
data = cursor.fetchone()[0]Code language: SQL (Structured Query Language) (sql)

Sixth, write the BLOB data into an image file with the name stored_image.jpg:

with open('stored_image.jpg', 'wb') as file:
    file.write(data)Code language: SQL (Structured Query Language) (sql)

Finally, commit changes and close the database connection:

conn.commit()
conn.close()Code language: SQL (Structured Query Language) (sql)

Summary

• Use SQLite BLOB data type to store binary data such as images, video files, documents, and other types of raw binary data.