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:
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 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.
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