Skip to content
Related Articles

Related Articles

How to Insert Image in SQLite using Python?

View Discussion
Improve Article
Save Article
  • Last Updated : 16 May, 2021
View Discussion
Improve Article
Save Article

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.

Python3




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:

Output for above Python Program

Let’s check output in the database using SELECT query with proper format commands,

Output inside database


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!