Working with MySQL BLOB in Python

Last Updated : 13 Jun, 2022

In Python Programming, We can connect with several databases like MySQL, Oracle, SQLite, etc., using inbuilt support. We have separate modules for each database. We can use SQL Language as a mediator between the python program and database. We will write all queries in our python program and send those commands to the database. So, Using these programs, we can perform several operations such as Insertion, Deletion, Updating, and Retrieving.

Here, In this article, We will discuss working with MySQL BLOB in python. With the help of BLOB(Large Binary Object) data type in MySQL, we can store files or images in our database in binary format.

Installation of MySQL Connector:

This connector will connect our python program to database. Just run this command,

Command:

pip install mysql-connector-python

Important steps for Python Database Programming:

Import MySQL database Module

import mysql.connector

For creating a connection between Python Program and Database. Using connect() method, We will connect the python program with our database.

connection = mysql.connector.connect(host='localhost', database='<database_name>', user='<User_name>', password='<password>')

Now, create a cursor object by using cursor() method for executing the SQL Queries and holding the result in an object.

cursor = connection.cursor()

For executing SQL queries, we will use a cursor object. For example,

cursor.execute("select * from table_name")

Finally, Once we are done with our operations, we have to close the resources.

cursor.close() con.close()

We are done with the basic steps of connection. Now, Let's discuss the main agenda of this article which is the practical implementation of BLOB data type in MySQL Python,

First, We need to create a database in MySQL using the below command.

create database geeksforgeeks;

For Example:

Creating a function through which we can convert images or files in binary format.

Python3

def convertData(filename):        # Convert images or files data to binary format     with open(filename, 'rb') as file:         binary_data = file.read()          return binary_data

Check Whether Database Connection is created or not using Python Program. Let's have a look in below code:

Python3

import mysql.connector   connection = mysql.connector.connect(     host='localhost', database='geeksforgeeks',     user='root', password='shubhanshu')  cursor = connection.cursor()  if connection is not None:     print('Connected Successfully') else:     print('Connection Failed')

We are done with all basic which is required. Let's see the complete code for inserting the images or files in the MySQL database using Python Programs:

Python3

import mysql.connector   # Convert images or files data to binary format def convert_data(file_name):     with open(file_name, 'rb') as file:         binary_data = file.read()     return binary_data   try:     connection = mysql.connector.connect(host='localhost',                                           database='geeksforgeeks',                                           user='root',                                           password='shubhanshu')     cursor = connection.cursor()     # create table query     create_table = """CREATE TABLE demo(id INT PRIMARY KEY,\     name VARCHAR (255) NOT NULL, profile_pic BLOB NOT NULL, \     imp_files BLOB NOT NULL) """      # Execute the create_table query first     cursor.execute(create_table)     # printing successful message     print("Table created Successfully")      query = """ INSERT INTO demo(id, name, profile_pic, imp_files)\     VALUES (%s,%s,%s,%s)"""      # First Data Insertion     student_id = "1"     student_name = "Shubham"     first_profile_picture = convert_data("D:\GFG\images\shubham.png")     first_text_file = convert_data('D:\GFG\details1.txt')      # Inserting the data in database in tuple format     result = cursor.execute(         query, (student_id, student_name, first_profile_picture, first_text_file))     # Committing the data     connection.commit()     print("Successfully Inserted Values")  # Print error if occurred except mysql.connector.Error as error:     print(format(error))  finally:        # Closing all resources     if connection.is_connected():                cursor.close()         connection.close()         print("MySQL connection is closed")

Output:

The table formed in MySQL:

Explanation:

Establishing the connection with MySQL database.
Write the create table Query and Using cursor object, Executing it.
Now, Insert data into a table using SQL query and stored in query variable.
Storing the data in variables such as student_id = "1", Student_name = "Shubham" and for images or files, first we are converting those files into binary data and then stored into a variables.
Using cursor object, Executing the query. Inserting the data in the database in tuple format.
Using commit() method, We are saving the data.
After completing all operations, we have to close all the resources such as the connection and cursor object.

Click here to download PNG file and TXT file.

Video Demonstration:

Retrieve Image and File stored as a BLOB from MySQL Table using Python

shubhanshuarya007

Improve

Article Tags :

Practice Tags :

python

Working with MySQL BLOB in Python

Installation of MySQL Connector:

Command:

Important steps for Python Database Programming:

Video Demonstration:

Similar Reads

MySQL Queries

MySQL Clause

MySQL Working with Data

MySQL Working with Images