how to retrieve file stored as BLOB in mysqlDB using python

Question

my table (authors) is :

--------------
| id | photo |
--------------
| 14 | BLOB  |
--------------

photo column type is BLOB

I want to retrieve this Blob value using python:

my code is as below:

import mysql.connector

db_username='mehdi'
db_password='mehdi'
database_name='cra_db'
db_host='127.0.0.1'

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

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

def write_blob(author_id, filename):
    # read file
    data = read_file(filename)
    # prepare update query and data
    query = "INSERT INTO `cra_db`.`authors` (`id`,`photo`) VALUES(%s,%s)"
    args = (author_id,data)
    try:
        cnx = mysql.connector.connect(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query, args)
        cnx.commit()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def update_blob(author_id, filename):
    # read file
    data = read_file(filename)
    # prepare update query and data
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"
    args = (data, author_id) 
    try:
        cnx = mysql.connector.connect(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query, args)
        cnx.commit()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def read_blob(author_id, filename):
    # select photo column of a specific author
    query = "SELECT photo FROM authors WHERE id = {}".format(author_id)
    try:
        cnx = mysql.connector.connect(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query)
        out=cursor.fetchall()
        # write blob data into a file
        write_file(out, filename)
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def main():
    write_blob(14,"01.jpg")
    update_blob(14, "01.jpg")
    read_blob(14,"02.jpg")

if __name__ == '__main__':
    main()

write_blob def and update_blob def work properly but i when run read_blob def this error occurred:

<built-in method fetch_row of _mysql_connector.MySQL object at 
0x00000000034DA8E0> returned a result with an error set

Do you have any idea to fix this problem? I want to retrieve this Blob value .

Answer 1

by replacing this code the problem solved but idont know why!!!!

from mysql.connector import MySQLConnection, Error,connect

db_username='mehdi'
db_password='mehdi'
database_name='cra_db'
db_host='127.0.0.1'

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

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

def write_blob(author_id, filename):
    # read file
    data = read_file(filename)
    # prepare update query and data
    query = "INSERT INTO `cra_db`.`authors` (`id`,`photo`) VALUES(%s,%s)"
    args = (author_id,data)
    try:
        cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query, args)
        cnx.commit()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def update_blob(author_id, filename):
    # read file
    data = read_file(filename)
    # prepare update query and data
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"
    args = (data, author_id) 
    try:
        cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query, args)
        cnx.commit()
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def read_blob(author_id, filename):
    # select photo column of a specific author
    query = "SELECT photo FROM authors WHERE id = %s"
    try:
        cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, database=database_name)
        cursor = cnx.cursor()
        cursor.execute(query, (author_id,))
        photo=cursor.fetchone()[0]
        # write blob data into a file
        write_file(photo, filename)
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        cnx.close()

def main():
    # write_blob(144,"01.jpg")
    # update_blob(144, "01.jpg")
    read_blob(144,"02.jpg")

if __name__ == '__main__':
    main()