Retrieve Image and File stored as a BLOB from MySQL Table using Python
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-connect to use MySQL drivers in our python script. First, install the requirements:
python3 -m pip install mysql-connect-python
Next, create a database and a table as shown below:
CREATE DATABASE STUDENTDB;
CREATE TABLE PROFILE ( ID BIGINT PRIMARY KEY, NAME VARCHAR(50) NOT NULL, PICTURE LONGBLOB NOT NULL );
We can see the database schema using:
Now, let’s add some data into the database:
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.