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:
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.
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 exception 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: