使用 Python 在 MySQL 中将文件和图像作为 Blob 插入/检索

在本课中，您将学习如何从 Python将任何数字信息（例如文件、图像、视频或歌曲）作为blob数据插入或保存到 MySQL 表中。我们还将学习如何使用 Python 获取存储在 MySQL 中的文件、图像、视频或歌曲。

本文的目标

• 使用 Python 将二进制数据插入 MySQL 表
• BLOB在 Python 中从 MySQL 表中读取数据文件

注意：我们使用 MySQL Connector Python 模块来连接 MySQL。

延伸阅读：

• 解决 Python MySQL 练习
• 阅读 Python MySQL 教程（完整指南）

目录

• 先决条件
• 什么是 BLOB
• 将图像和文件作为 BLOB 数据插入 MySQL 表
• 使用 Python 从 MySQL 表中检索存储为 BLOB 的图像和文件
• 下一步：

先决条件

要将 BLOB 数据存储在 MySQL 表中，我们需要创建一个包含二进制数据的表。或者，如果您有一个表，则通过添加一个以 BLOB 作为其数据类型的额外列来修改它。

您可以使用以下查询创建具有 BLOB 列的表。

CREATE TABLE `Python_Employee` ( `id` INT NOT NULL , `name` TEXT NOT NULL , `photo` BLOB NOT NULL , `biodata` BLOB NOT NULL , PRIMARY KEY (`id`))

此表包含以下两个 BLOB 列。

• 照片：存储员工照片。
• 生物数据文件：以文件格式存储员工详细信息。

带有 blob 列的 Python MySQL 表

截至目前，该python_employee表是空的。让我们在其中插入员工的照片和生物数据文件。在执行以下程序之前，请确保您拥有连接 MySQL 的用户名和密码。

什么是 BLOB

BLOB（大型二进制对象）是一种 MySQL 数据类型，用于存储二进制数据。我们可以在 Python 中将文件和图像转换为二进制数据，并使用 BLOB 将它们保存在 MySQL 表中。

注意：要将文件或图像插入到 MySQL 表中，我们需要创建一个 BLOB 列作为类型​​。MySQL 有以下四种 BLOB 类型。每个都包含可变数量的数据。

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

以上 BLOB 类型的区别仅在于它们可以保存的值的最大长度。要阅读有关 BLOB 的更多信息，您可以访问这个MySQL BLOB 文档。

将图像和文件作为 BLOB 数据插入 MySQL 表

让我们将员工照片和生物数据插入到 python_employee 表中。要从 Python 将 BLOB 数据插入 MySQL 表，您需要执行以下简单步骤：-

• 使用 Pip 安装 MySQL 连接器 Python。
• 二、用Python建立MySQL数据库连接。
• 创建一个可以将图像和文件转换为二进制数据的函数。
• 然后，定义插入查询以将二进制数据输入到数据库表中。您只需要知道表格的列详细信息。
• 使用cursor.execute(). 它返回受影响的行数。
• 成功执行查询后，将更改提交到数据库。
• 关闭 Cursor 和 MySQL 数据库连接。
• 最重要的是，捕获 SQL 异常（如果有）。
• 最后，通过从 MySQL 表中选择数据来验证结果。

现在让我们看看这个例子。

import mysql.connector

def convertToBinaryData(filename):
    # Convert digital data to binary format
    with open(filename, 'rb') as file:
        binaryData = file.read()
    return binaryData


def insertBLOB(emp_id, name, photo, biodataFile):
    print("Inserting BLOB into python_employee table")
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='python_db',
                                             user='pynative',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_insert_blob_query = """ INSERT INTO python_employee
                          (id, name, photo, biodata) VALUES (%s,%s,%s,%s)"""

        empPicture = convertToBinaryData(photo)
        file = convertToBinaryData(biodataFile)

        # Convert data into tuple format
        insert_blob_tuple = (emp_id, name, empPicture, file)
        result = cursor.execute(sql_insert_blob_query, insert_blob_tuple)
        connection.commit()
        print("Image and file inserted successfully as a BLOB into python_employee table", result)

    except mysql.connector.Error as error:
        print("Failed inserting BLOB data into MySQL table {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

insertBLOB(1, "Eric", "D:\Python\Articles\my_SQL\images\eric_photo.png",
           "D:\Python\Articles\my_SQL\images\eric_bioData.txt")
insertBLOB(2, "Scott", "D:\Python\Articles\my_SQL\images\scott_photo.png",
           "D:\Python\Articles\my_SQL\images\scott_bioData.txt")

输出：

Inserting BLOB into python_employee table
Image and file inserted successfully as a BLOB into python_employee table None
MySQL connection is closed
Inserting BLOB into python_employee table

让我们看一下python_employee插入图像和文件后的表格。

从 Python 插入 BLOB 数据后的 MySQL 表

注意：我们插入了员工 ID、姓名、照片和生物数据文件。对于图像和生物数据，我们传递了它存在的位置。

如您所见，我们通过在将图像和文件rb插入到 BLOB 列之前以模式读取图像和文件，将图像和文件转换为二进制格式。

此外，我们使用参数化查询将动态数据插入 MySQL 表。

使用 Python 从 MySQL 表中检索存储为 BLOB 的图像和文件

假设我们想以二进制格式读取存储在 MySQL 表中的文件或图像，并将该文件写回硬盘上的任意位置。让我们看看如何做到这一点。

• 从存储为 BLOB 的 MySQL 表中读取员工图像和文件。
• 将此 BLOB 二进制数据写入磁盘。我们可以传递我们希望它显示的文件格式来将这个二进制数据写入硬盘。

要使用 Python 从 MySQL 表中读取 BLOB 数据，您需要执行以下简单步骤：-

• 使用 pip 安装 MySQL 连接器 Python。
• 二、用Python建立MySQL数据库连接。
• 然后，定义 SELECT 查询以从数据库表中获取 BLOB 列值。
• 使用执行 SELECT 查询 cursor.execute()
• 用于cursor.fetchall() 从结果集中检索所有行并对其进行迭代。
• 创建一个函数，以正确格式写入我们从磁盘上的每一行检索到的 BLOB 或二进制数据。
• 关闭 Cursor 和 MySQL 数据库连接。

import mysql.connector


def write_file(data, filename):
    # Convert binary data to proper format and write it on Hard Disk
    with open(filename, 'wb') as file:
        file.write(data)


def readBLOB(emp_id, photo, bioData):
    print("Reading BLOB data from python_employee table")

    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='python_db',
                                             user='pynative',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_fetch_blob_query = """SELECT * from python_employee where id = %s"""

        cursor.execute(sql_fetch_blob_query, (emp_id,))
        record = cursor.fetchall()
        for row in record:
            print("Id = ", row[0], )
            print("Name = ", row[1])
            image = row[2]
            file = row[3]
            print("Storing employee image and bio-data on disk \n")
            write_file(image, photo)
            write_file(file, bioData)

    except mysql.connector.Error as error:
        print("Failed to read BLOB data from MySQL table {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")


readBLOB(1, "D:\Python\Articles\my_SQL\query_output\eric_photo.png",
         "D:\Python\Articles\my_SQL\query_output\eric_bioData.txt")
readBLOB(2, "D:\Python\Articles\my_SQL\query_output\scott_photo.png",
         "D:\Python\Articles\my_SQL\query_output\scott_bioData.txt")

输出：

Reading BLOB data from python_employee table
Id = 1
Name = Eric
Storing employee image and bio-data on disk
MySQL connection is closed


Reading BLOB data from python_employee table
Id = 2
Name = Scott
Storing employee image and bio-data on disk
MySQL connection is closed

从 MySQL 表中检索图像和文件并存储在磁盘上。

从 mysql 读取 BLOB 数据后存储在磁盘上的图像和文件