Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Insert Image in SQLite using Python?

  • Last Updated : 16 May, 2021

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.

 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

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 :