Skip to content
Related Articles

Related Articles

Improve Article
How to Read Image in SQLite using Python?
  • Last Updated : 16 May, 2021

This article shows us how to use the Python sqlite3 module to read or retrieve images that are stored in the form of BLOB data type in an SQLite table. First, We need to read an image that is stored in an SQLite table in BLOB format using python script and then write the file back to any location on the hard drive so that you can view and read it in an appropriate format.

SQLite Database Used:

This is our sqlite3 database.

Implementation:

In this example, we are reading Student name and Student image, which is stored in SQLite tables stored in BLOB form. To read BLOB data from a SQLite table using Python, you need to perform the following steps:-

1. Set the connection to the SQLite database using Python code

con = sqlite3.connect('SQLite_Retrieving_data.db')
print("Connected Successfully")

2. We need to define a SELECT query to get the BLOB column from the table.  



query = "SELECT * FROM <table_name>"

3. Use cursor.execute() to execute a SELECT query in Python.  

cursor = con.cursor()
cursor.execute(query)

4. Use cursor.fetchall() to retrieve and traverse all rows in the result set.  

records = cursor.fetchall()
    for row in records:
        name = row[0]
        print("Student Name = ", name)

5. Create a function that converts BLOB data into the correct format and saves it in a human-readable format.  

def convert_data(data, file_name):
    # Convert binary format to images or files data
    with open(file_name, 'wb') as file:
        file.write(data)

6. Close the cursor connection and MySQL database.

if con:
     con.close()
     print("SQLite connection is closed")

Below is the implementation.

Python3




import sqlite3
from PIL import Image
  
# Function for Convert Binary 
# Data to Human Readable Format
def convert_data(data, file_name):
      
    # Convert binary format to 
    # images or files data
    with open(file_name, 'wb') as file:
        file.write(data)
    img = Image.open(file_name)
    print(img)
  
try:
        
    # Using connect method for establishing
    # a connection
    con = sqlite3.connect('SQLite_Retrieving_data.db')
    cursor = con.cursor()
    print("Connected Successfully")
  
    # Search from table query
    query = "SELECT * FROM Student"
  
    # using cursor object executing our query
    cursor.execute(query)
      
    # fectching all records from cursor object
    records = cursor.fetchall()
      
    # using for loop retrieving one by one 
    # rows or data
    for row in records:
            
        # storing row[0] in name variable
        name = row[0]
          
        # printing name variable
        print("Student Name = ", name)
          
        # storing image (currently in binary format)
        image = row[1]
          
        # calling above convert_data() for converting
        # binary data to human readable
        convert_data(image, "D:\Internship Tasks\GFG\sqlite\\" + name + ".png")
        print("Yeah!! We have successfully retrieved values from database")
  
    # If we don't have any records in our database,
    # then print this
    if len(records) == 0:
        print("Sorry! Please Insert some data before reading from the database.")
  
# print execption if found any during program
# is running
except sqlite3.Error as error:
    print(format(error))
  
# using finally, closing the connection 
# (con) object
finally:
    if con:
        con.close()
        print("SQLite connection is closed")

Output:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :