PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python SQLite BLOB to Insert and Retrieve file and images

Python SQLite BLOB to Insert and Retrieve file and images

Updated on: March 9, 2021 | 10 Comments

In this article, you will learn to insert and retrieve a file stored as a BLOB in the SQLite table using Python’s sqlite3 module.

  • Use SQLite BLOB data type to store any binary data into the SQLite table using Python. Binary can be a file, image, video, or a media
  • Read BLOB data from the SQLite table in Python.

Also Read:

  • Solve Python SQLite Exercise
  • Read Python SQLite Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • What is BLOB
  • Insert Image and File as a BLOB data into SQLite Table
  • Retrieve Image and File stored as a BLOB from SQLite Table
  • Next Steps:

Prerequisites

Before executing the following SQLite BLOB operations, please make sure you know the SQLite table name and in which you want to store BLOB data.

To Store BLOB data in the SQLite table, we need to create a table that can hold binary data, or you can also modify any existing table and add one extra column with BLOB as its data type.

new_developers table to insert blob data
new_employee table to insert blob data

Please refer to creating an SQLite table from Python. For this lesson, I am using the ‘new_employee’ table present in my SQLite database.

You can use the following query to create a table with a BLOB column.

CREATE TABLE new_employee ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, photo BLOB NOT NULL, resume BLOB NOT NULL);

The table contains two BLOB columns.

  • A photo column contains an employee picture.
  • A resume column includes a file which is a developer resume.

Before proceeding to the examples first understand what is BLOB.

What is BLOB

A BLOB (large binary object) is an SQLite data type that stores large objects, typically large files such as images, music, videos, documents, pdf, etc.

We need to convert our files and images into binary data (byte array in Python) to store it into SQLite database.

Insert Image and File as a BLOB data into SQLite Table

As of now, a table is empty. Let’s insert employee’s photos and resume files in it.

To insert BLOB data into SQLite table from Python, you need to follow the below steps: –

  • First, establish the SQLite connection from Python.
  • Second, create a cursor object using the connection object.
  • Then, define the SQLite INSERT Query. You need to know the table and the column name in which you want to insert data.
  • Next, create a function to convert digital data, i.e., images or any file, to binary data
  • Execute the INSERT query in Python using the cursor.execute().
  • After the successful execution of the SQLite insert operation, commit your changes to the database.
  • Close the Cursor and SQLite database connection.
  • Most important, Catch SQL exceptions, if any.

Example

import sqlite3

def convertToBinaryData(filename):
    # Convert digital data to binary format
    with open(filename, 'rb') as file:
        blobData = file.read()
    return blobData

def insertBLOB(empId, name, photo, resumeFile):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")
        sqlite_insert_blob_query = """ INSERT INTO new_employee
                                  (id, name, photo, resume) VALUES (?, ?, ?, ?)"""

        empPhoto = convertToBinaryData(photo)
        resume = convertToBinaryData(resumeFile)
        # Convert data into tuple format
        data_tuple = (empId, name, empPhoto, resume)
        cursor.execute(sqlite_insert_blob_query, data_tuple)
        sqliteConnection.commit()
        print("Image and file inserted successfully as a BLOB into a table")
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert blob data into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("the sqlite connection is closed")

insertBLOB(1, "Smith", "E:\pynative\Python\photos\smith.jpg", "E:\pynative\Python\photos\smith_resume.txt")
insertBLOB(2, "David", "E:\pynative\Python\photos\david.jpg", "E:\pynative\Python\photos\david_resume.txt")

Output:

Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed
Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed

Let’s have a look at the new_developer table after inserting the image and file into it.

new_developers table after inserting BLOB data
new_developers table after inserting BLOB data

Note:

We inserted the employee id, name, photo, and resume file into the table. For the image and resume, we passed the location where it is present so our program can read and convert those files into binary data.

As you can see, we converted our image and file into a binary format by reading the image and data in rb mode before inserting it into a BLOB column.

Also, we used a parameterized query to insert dynamic data into an SQLite table.

Retrieve Image and File stored as a BLOB from SQLite Table

Assume you want to read the file or images stored in the SQLite table in BLOB format and write that file back to some location on the disk so you can view and read it in a proper format.

In this example, we are reading the employee photo and resume file that we stored in the SQLite table stored as a BLOB in the previous example.

To read BLOB data from SQLite Table using Python, you need to follow the below steps: –

  • Establish the SQLite database connection in Python.
  • Second, Define the SELECT query to fetch BLOB columns from the table.
  • Execute the SELECT query in Python using a cursor.execute()
  • Use the cursor.fetchall() to retrieve all the rows from the result set and iterate over it.
  • Create a function to convert BLOB data in proper format and save it in a readable format.
  • Close the Cursor and MySQL database connection.
import sqlite3

def writeTofile(data, filename):
    # Convert binary data to proper format and write it on Hard Disk
    with open(filename, 'wb') as file:
        file.write(data)
    print("Stored blob data into: ", filename, "\n")

def readBlobData(empId):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_fetch_blob_query = """SELECT * from new_employee where id = ?"""
        cursor.execute(sql_fetch_blob_query, (empId,))
        record = cursor.fetchall()
        for row in record:
            print("Id = ", row[0], "Name = ", row[1])
            name = row[1]
            photo = row[2]
            resumeFile = row[3]

            print("Storing employee image and resume on disk \n")
            photoPath = "E:\pynative\Python\photos\db_data\\" + name + ".jpg"
            resumePath = "E:\pynative\Python\photos\db_data\\" + name + "_resume.txt"
            writeTofile(photo, photoPath)
            writeTofile(resumeFile, resumePath)

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read blob data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("sqlite connection is closed")

readBlobData(1)
readBlobData(2)

As you can see images and files are stored on disk after reading BLOB data from SQLite.

reading BLOB data from the sqlite table and stored on disk
reading BLOB data from the SQLite table and stored on disk

Note: To copy binary data on the hard drive, we converted binary data(BLOB) to the proper format and wrote it on Hard Disk. In our example, we converted the photo blob column into png and resume blob column data into txt file.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Comments

  1. Shuja Rehan says

    August 1, 2021 at 2:41 am

    Hi i vishal i want to read image through Open CV when i search image through pi cam then the image is match where data is saved in database in pop in front through image searching using open cv

    Reply
  2. Jesús García Gamborino says

    May 11, 2021 at 2:59 pm

    Hi vishal, first of all thank you very much for everything, your tutorials have helped me a lot.
    I wanted to consult a small variation.
    I need to enter in a BLOB cell a list containing a series of real numbers.
    To do this I have to convert my data into binary first?
    Would it be possible to change the input argument in your code? That is to say to put my list instead of a file (image, audio, pdf, etc).
    Thanks and best regards.

    Reply
  3. K J Phani Kumar says

    April 27, 2021 at 12:55 pm

    Hi I found your help useful to me

    Reply
    • Vishal says

      April 28, 2021 at 9:56 am

      I am glad it helped you.

      Reply
  4. Adolfo Chiñas Vega says

    September 21, 2020 at 1:08 am

    Hello Vishal, I need a “little” diferent change in code. I want to show the image I stored in sqlite3, on a tkinter label. Would you help me?

    Reply
  5. Muhammad Owais says

    September 20, 2020 at 4:57 pm

    how can we display the image in label after fetch from database using tkinter and sqlite3. I am face a lot of issues..?

    Reply
  6. Kurt says

    November 6, 2019 at 4:53 pm

    Thank you – written just in time! I started tackling this last night, and your article was just what I needed.

    Reply
    • Vishal says

      November 6, 2019 at 6:05 pm

      Kurt, I am glad it helped you.

      Reply
  7. Nikhilesh Sandela says

    October 3, 2019 at 8:13 pm

    Thank you for this Post. I have been looking for this. Can you explain how to generate primary key IDs automatically as and when you upload a new file in the DB?

    Reply
    • Flaming Phoenix says

      June 23, 2020 at 11:58 am

      Use AUTOINCREMENT with your primary key

      Reply

Leave a Reply Cancel reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your code </pre>

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python SQLite

  • Python SQLite Guide
  • Python SQLite Insert
  • Python SQLite Select
  • Python SQLite Update
  • Python SQLite Delete
  • Python SQLite Create Functions
  • Python Parameterized Query
  • Python SQLite BLOB
  • Python SQLite DateTime
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2022 pynative.com