SQLite BLOB

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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 BLOBCopy

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
);Copy

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()Copy

How it works.

First, import the sqlite3 module:

import sqlite3Copy

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

conn = sqlite3.connect('my.db')
cursor = conn.cursor()Copy

Third, create the documents table:

cursor.execute('''CREATE TABLE documents(
                    id INTEGER PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    data BLOB NOT NULL
                );''')Copy

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,))Copy

Fifth, retrieve the blob data from the documents table:

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

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)Copy

Finally, commit changes and close the database connection:

conn.commit()
conn.close()Copy

Summary #

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