Retrieve Image and File stored as a BLOB from MySQL Table using Python
Last Updated :
10 Jan, 2023
Prerequisites: MySQL server should be installed
In this post, we will be talking about how we can store files like images, text files, and other file formats into a MySQL table from a python script. Sometimes, just like other information, we need to store images and files into our database and provide it the security equivalent to other data.
In MySQL, we can use BLOB datatype to store the files. A BLOB is a binary large object that can hold a variable amount of data. We can represent the files in binary format and then store them in our database. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.
We will use mysql-connector to use MySQL drivers in our python script. First, install the requirements:
python3 -m pip install mysql-connector-python
Next, create a database and a table as shown below:
CREATE DATABASE STUDENTDB;
USE STUDENTDB;
CREATE TABLE PROFILE ( ID BIGINT PRIMARY KEY, NAME VARCHAR(50) NOT NULL, PICTURE LONGBLOB NOT NULL );
We can see the database schema using:
DESC PROFILE;
Now, let’s add some data into the database:
Python3
import mysql.connector
import base64
from PIL import Image
import io
password = open ( 'password' , 'r' ).readline()
mydb = mysql.connector.connect(
host = "localhost" ,
user = "root" ,
password = password,
database = "studentdb"
)
cursor = mydb.cursor()
file = open ( 'image.png' , 'rb' ).read()
file = base64.b64encode( file )
args = ( '100' , 'Sample Name' , file )
query = 'INSERT INTO PROFILE VALUES(%s, %s, %s)'
cursor.execute(query,args)
mydb.commit()
|
Now moving back to our MySQL database, we can see the inserted row.
Retrieve the file:
We can make an SQL query to retrieve the image. The returned data will be in base64 format. So first we need to decode the data. We can transmit this data to the user or utilize it in other ways. In this post, we will simply show the image on the screen.
Python3
import mysql.connector
import base64
from PIL import Image
import io
password = open ( 'password' , 'r' ).readline()
mydb = mysql.connector.connect(
host = "localhost" ,
user = "root" ,
password = password,
database = "studentdb"
)
cursor = mydb.cursor()
query = 'SELECT PICTURE FROM PROFILE WHERE ID=100'
cursor.execute(query)
data = cursor.fetchall()
image = data[ 0 ][ 0 ]
binary_data = base64.b64decode(image)
image = Image. open (io.BytesIO(binary_data))
image.show()
|
Output:
Share your thoughts in the comments
Please Login to comment...