{"id":3278,"date":"2014-10-21T20:43:26","date_gmt":"2014-10-22T04:43:26","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3278"},"modified":"2024-01-03T00:50:29","modified_gmt":"2024-01-03T07:50:29","slug":"python-mysql-blob","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/python-mysql\/python-mysql-blob\/","title":{"rendered":"Python MySQL – Read & Update BLOB in MySQL Database"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to work with MySQL BLOB<\/a> data in Python including updating and reading BLOB<\/code> data.<\/p>\n\n\n\n

This tutorial picks up where the Calling Stored Procedures in Python<\/a>\u00a0tutorial left off.<\/p>\n\n\n\n

The  authors<\/code> table has a column named photo<\/code> whose data type is BLOB<\/a>. We will read data from an image and update it to the photo<\/code> column.<\/p>\n\n\n\n

Updating BLOB data in Python<\/h2>\n\n\n\n

The following program reads data from a file and updates it to the database:<\/p>\n\n\n

from mysql.connector import MySQLConnection, Error\r\nfrom config import read_config\r\n\r\n\r\ndef read_file(filename):\r\n    with open(filename, 'rb'<\/span>) as<\/span> f:\r\n        photo = f.read()\r\n        return<\/span> photo\r\n\r\n\r\ndef update_blob(author_id, filename):\r\n    # Read file data<\/span>\r\n    data = read_file(filename)\r\n\r\n    # Prepare update query and data<\/span>\r\n    query = \"UPDATE authors \"<\/span> \\\r\n            \"SET photo = %s \"<\/span> \\\r\n            \"WHERE id  = %s\"<\/span>\r\n\r\n    args = (data, author_id)\r\n\r\n    config = read_config()\r\n\r\n    try<\/span>:\r\n        # Establish a connection to the MySQL database<\/span>\r\n        with MySQLConnection(**config) as<\/span> conn:\r\n            # Create a cursor to execute SQL queries<\/span>\r\n            with conn.cursor() as<\/span> cursor:\r\n                # Execute the update query with the provided arguments<\/span>\r\n                cursor.execute(query, args)\r\n                # Commit the changes to the database<\/span>\r\n                conn.commit()\r\n\r\n    except Error as<\/span> e:\r\n        print<\/span>(e)\r\n\r\nif<\/span> __name__ == '__main__'<\/span>:\r\n    try<\/span>:\r\n        author_id = 3<\/span>  \r\n        filename = 'images\/francis_tugwell.png'<\/span> \r\n        update_blob(author_id, filename)\r\n    except Error as<\/span> e:\r\n        print<\/span>(e)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define a function called read_file()<\/code> that reads a file and returns the file’s content:<\/p>\n\n\n
def<\/span> read_file<\/span>(filename)<\/span>:<\/span>\r\n    with<\/span> open(filename, 'rb'<\/span>) as<\/span> f:\r\n        photo = f.read()\r\n        return<\/span> photo<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new function called update_blob()<\/code> that updates the photo for an author specified by author_id<\/code> .<\/p>\n\n\n
def<\/span> update_blob<\/span>(author_id, filename)<\/span>:<\/span>\r\n    # Read file data<\/span>\r\n    data = read_file(filename)\r\n\r\n    # Prepare update query and data<\/span>\r\n    query = \"UPDATE authors \"<\/span> \\\r\n            \"SET photo = %s \"<\/span> \\\r\n            \"WHERE id  = %s\"<\/span>\r\n\r\n    args = (data, author_id)\r\n\r\n    config = read_config()\r\n\r\n    try<\/span>:\r\n        # Establish a connection to the MySQL database<\/span>\r\n        with<\/span> MySQLConnection(**config) as<\/span> conn:\r\n            # Create a cursor to execute SQL queries<\/span>\r\n            with<\/span> conn.cursor() as<\/span> cursor:\r\n                # Execute the update query with the provided arguments<\/span>\r\n                cursor.execute(query, args)\r\n                # Commit the changes to the database<\/span>\r\n                conn.commit()\r\n\r\n    except<\/span> Error as<\/span> e:\r\n        print(e)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n