Storing OpenCV Image in SQLite3 with Python

OpenCV is a huge open-source library for computer vision, machine learning, and image processing. OpenCV supports a wide variety of programming languages like Python, C++, Java, etc. It can process images and videos to identify objects, faces, or even the handwriting of a human. When it is integrated with various libraries, such as Numpy. Which is a highly optimized library for numerical operations, then the number of weapons increases in your Arsenal i.e whatever operations one can do in Numpy can be combined with OpenCV.

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine on the World Wide Web. Python has a library to access SQLite databases, called sqlite3, intended for working with this database which has been included with Python package since version 2.5.

In this article, we will store an OpenCV image in sqlite3 database with Python. Let’s take this image “gfg.png” as an example:

Step-by-step Approach:

• First import the necessary libraries.

Python3

# import necessary libraries
import cv2
import sqlite3
import pandas as pd

• Connect to the sqlite3 database.

Python3

# connect to database
conn = sqlite3.connect("gfg.db")

• Create a cursor object and get the current cursor location :

Python3

cursorObject = conn.cursor()

• Create a new table and commit it to the database.

Python3

# create a table
cursorObject.execute("CREATE TABLE imgfg(id string, img blob)")
conn.commit()

• Open the image with open() in read mode.

Python3

im = open( 'gfg.png', 'rb' ).read()

• Insert the image into the table.

Python3

# open the image you want to store in read more
im = open('gfg.png', 'rb').read()
conn.execute("INSERT INTO imgfg VALUES(?,?)",
             ("pattern", sqlite3.Binary(im)))

The above statement opens the image and then converts it into a pattern by simply interpreting the binary BLOB context. Finally, it stores that pattern into the table.

• Commit to the database.

Python3

conn.commit()

• Store the sqlite3 table as a CSV file with pandas.

Python3

# Use pandas to create a dataframe from
# the table and save it as a csv
table = pd.read_sql_query("SELECT * FROM imgfg", conn)
table.to_csv("imgfg" + '.csv', index_label='index')

The content is stored in the table variable and then it is converted to a CSV file and is saved into the system.

• Display the content of the table.

Python3

# display table
print(table)

Below is the complete program:

Python3

# import necessary libraries
import cv2
import sqlite3
import pandas as pd

# connect to database
conn = sqlite3.connect("gfg.db")
cursorObject = conn.cursor()

# create a table
cursorObject.execute("CREATE TABLE imgfg(id string, img blob)")
conn.commit()

# open the image you want to store in read more
im = open('gfg.png', 'rb').read()
conn.execute("INSERT INTO imgfg VALUES(?,?)",
             ("pattern", sqlite3.Binary(im)))
conn.commit()

# Use pandas to create a dataframe from
# the table and save it as a csv
table = pd.read_sql_query("SELECT * FROM imgfg", conn)
table.to_csv("imgfg" + '.csv', index_label='index')

# display table
print(table)

Output:

CSV generated: