In this article, we will discuss how to insert images in SQLite using sqlite3 module in Python.
Implementation:
1. Set the connection to the SQLite database using Python code.
sqliteConnection = sqlite3.connect('SQLite_Retrieving_data.db') cursor = sqliteConnection.cursor()
2. We need to define an INSERT query for inserting the BLOB data into the table.
sqlite_insert_blob_query = """ INSERT INTO Student (name, img) VALUES (?, ?)"""
3. Converting human-readable file into binary data by calling this convertToBinaryData() function, and storing it empPhoto variable,
empPhoto = convertToBinaryData(photo)
4. Once the file converted into binary format, now let’s convert data into tuple format,
data_tuple = (name, empPhoto)
5. Use cursor.execute() to execute a SELECT query in Python.
cursor = sqliteConnection.cursor() cursor.execute(sqlite_insert_blob_query, data_tuple)
6. Use sqliteConnection.commit() for saving the changes we made.
sqliteConnection.commit()
7. Create a function that converts Human Readable data into the binary format for storing it into database.
def convertToBinaryData(filename): # Convert binary format to images or files data with open(filename, 'rb') as file: blobData = file.read() return blobData
8. Close the cursor connection and MySQL database.
if sqliteConnection: sqliteConnection.close() print("the sqlite connection is closed")
Below is the implementation.
import sqlite3
# Function for Convert Binary Data # to Human Readable Format def convertToBinaryData(filename):
# Convert binary format to images
# or files data
with open (filename, 'rb' ) as file :
blobData = file .read()
return blobData
def insertBLOB(name, photo):
try :
# Using connect method for establishing
# a connection
sqliteConnection = sqlite3.connect( 'SQLite_Retrieving_data.db' )
cursor = sqliteConnection.cursor()
print ( "Connected to SQLite" )
# insert query
sqlite_insert_blob_query = """ INSERT INTO Student
(name, img) VALUES (?, ?)"""
# Converting human readable file into
# binary data
empPhoto = convertToBinaryData(photo)
# Convert data into tuple format
data_tuple = (name, empPhoto)
# using cursor object executing our query
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( "Smith" , "D:\Internship Tasks\GFG\images\One.png" )
insertBLOB( "David" , "D:\Internship Tasks\GFG\images\person.png" )
|
Output:
Let’s check output in the database using SELECT query with proper format commands,