Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.mysqltutorial.org/python-mysql/python-mysql-blob/ below:

Working with MySQL BLOB in Python

Summary: in this tutorial, you will learn how to work with MySQL BLOB data in Python including updating and reading BLOB data.

This tutorial picks up where the Calling Stored Procedures in Python tutorial left off.

The  authors table has a column named photo whose data type is BLOB. We will read data from an image and update it to the photo column.

The following program reads data from a file and updates it to the database:

from mysql.connector import MySQLConnection, Error
from config import read_config


def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
        return photo


def update_blob(author_id, filename):
    
    data = read_file(filename)

    
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"

    args = (data, author_id)

    config = read_config()

    try:
        
        with MySQLConnection(**config) as conn:
            
            with conn.cursor() as cursor:
                
                cursor.execute(query, args)
                
                conn.commit()

    except Error as e:
        print(e)

if __name__ == '__main__':
    try:
        author_id = 3  
        filename = 'images/francis_tugwell.png' 
        update_blob(author_id, filename)
    except Error as e:
        print(e)Code language: PHP (php)

How it works.

First, define a function called read_file() that reads a file and returns the file’s content:

def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
        return photoCode language: Python (python)

Second, create a new function called update_blob() that updates the photo for an author specified by author_id .

def update_blob(author_id, filename):
    
    data = read_file(filename)

    
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"

    args = (data, author_id)

    config = read_config()

    try:
        
        with MySQLConnection(**config) as conn:
            
            with conn.cursor() as cursor:
                
                cursor.execute(query, args)
                
                conn.commit()

    except Error as e:
        print(e)Code language: Python (python)

How it works:

• First, call the read_file() function to read data from a file and return the binary data.
• Second, compose an UPDATE statement that updates photo column for an author specified by author_id . The args variable is a tuple that contains file data and author_id. We will pass this variable to the execute() method together with the query .
• Third, inside the  try...except block, connect to the database, create a cursor, and execute the query with args . To apply the permanent change to the database, call the commit() method of the MySQLConnection object.
• Finally, close the cursor and database connection in the  finally block.

Third, use the update_blob() function to read an image (images/francis_tugwell.png) from the images directory of the project directory:

if __name__ == '__main__':
    try:
        author_id = 3  
        filename = 'images/francis_tugwell.png' 
        update_blob(author_id, filename)
    except Error as e:
        print(e)Code language: Python (python)

Notice that you can download the following photo and place it in the images directory:

The following program retrieves BLOB data from the authors table and write it into a file:

from mysql.connector import MySQLConnection, Error
from config import read_config

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)

def read_blob(author_id, filename):
    
    query = "SELECT photo FROM authors WHERE id = %s"

    
    config = read_config()

    try:
        
        with MySQLConnection(**config) as conn:
            
            with conn.cursor() as cursor:
                
                cursor.execute(query, (author_id,))
                
                
                photo = cursor.fetchone()[0]

                
                write_file(photo, filename)

    except Error as e:
        print(e)        

if __name__ == '__main__':
    try:
        author_id = 3
        filename = 'images/3.png'
        read_blob(author_id, filename)
    except Error as e:
        print(e)        Code language: PHP (php)

How it works.

First, define a write_file() function that writes binary data into a file:

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)Code language: Python (python)

Second, create a new function called read_blob() that retrieves BLOB data from the database:

def read_blob(author_id, filename):
    
    query = "SELECT photo FROM authors WHERE id = %s"

    
    config = read_config()

    try:
        
        with MySQLConnection(**config) as conn:
            
            with conn.cursor() as cursor:
                
                cursor.execute(query, (author_id,))
                
                
                photo = cursor.fetchone()[0]

                
                write_file(photo, filename)

    except Error as e:
        print(e)        Code language: Python (python)

The  read_blob() function reads BLOB data from the  authors table and write it into a file specified by the  filename parameter.

Third, call the read_blob() function to read the photo of the author id 3 and write it to a file:

if __name__ == '__main__':
    try:
        author_id = 3
        filename = 'images/3.png'
        read_blob(author_id, filename)
    except Error as e:
        print(e)   Code language: Python (python)

If you find a picture in the project’s images directory, it indicates that you have successfully read the BLOB from the database.

In this tutorial, you have learned how to read and write BLOB data in MySQL from Python.

Was this tutorial helpful?

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4