How to Read Image in SQLite using Python?
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
def convert_data(data, file_name):
with open (file_name, 'wb' ) as file :
file .write(data)
img = Image. open (file_name)
print (img)
try :
con = sqlite3.connect( 'SQLite_Retrieving_data.db' )
cursor = con.cursor()
print ( "Connected Successfully" )
query = "SELECT * FROM Student"
cursor.execute(query)
records = cursor.fetchall()
for row in records:
name = row[ 0 ]
print ( "Student Name = " , name)
image = row[ 1 ]
convert_data(image, "D:\Internship Tasks\GFG\sqlite\\" + name + " .png")
print ( "Yeah!! We have successfully retrieved values from database" )
if len (records) = = 0 :
print ( "Sorry! Please Insert some data before reading from the database." )
except sqlite3.Error as error:
print ( format (error))
finally :
if con:
con.close()
print ( "SQLite connection is closed" )
|
Output:
Last Updated :
03 Sep, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...