使用 Flask 从 MySQL 数据库读取图片并返回给前端

要从 MySQL 数据库读取图片并通过 Flask 返回给前端，你需要完成以下几个步骤：

1. 数据库准备

首先确保你的 MySQL 数据库中有一个表存储了图片数据。通常图片可以以 BLOB 类型存储：

CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    data LONGBLOB,
    mime_type VARCHAR(50)
);

2. Flask 应用代码

基本设置

from flask import Flask, Response, request
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database'
}

从数据库获取图片的路由

@app.route('/image/<int:image_id>')
def get_image(image_id):
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        # 查询图片数据
        query = "SELECT data, mime_type FROM images WHERE id = %s"
        cursor.execute(query, (image_id,))
        result = cursor.fetchone()

        if result is None:
            return "Image not found", 404

        image_data, mime_type = result

        # 返回图片响应
        return Response(image_data, mimetype=mime_type)

    except Error as e:
        print(f"Database error: {e}")
        return "Database error", 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

上传图片的路由（可选）

@app.route('/upload', methods=['POST'])
def upload_image():
    if 'file' not in request.files:
        return "No file part", 400

    file = request.files['file']
    if file.filename == '':
        return "No selected file", 400

    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        # 读取文件数据
        image_data = file.read()
        mime_type = file.mimetype

        # 插入数据库
        query = "INSERT INTO images (name, data, mime_type) VALUES (%s, %s, %s)"
        cursor.execute(query, (file.filename, image_data, mime_type))
        connection.commit()

        return "Image uploaded successfully", 200

    except Error as e:
        print(f"Database error: {e}")
        return "Database error", 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

3. 前端使用

在前端 HTML 中，你可以这样引用图片：

<img src="/image/1" alt="Database Image">

或者上传图片：

<form action="/upload" method="post" enctype="multipart/form-data">
    <input type="file" name="file">
    <button type="submit">Upload</button>
</form>

4. 性能优化建议

1. 考虑缓存：对于频繁访问的图片，可以使用 Flask-Caching 进行缓存
2. 使用 CDN：对于生产环境，考虑将图片存储在 CDN 或对象存储中
3. 数据库优化：对于大量图片，考虑只存储图片路径在数据库，实际文件存储在文件系统中
4. 连接池：使用数据库连接池提高性能

5. 完整示例

from flask import Flask, Response, request, render_template
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database'
}

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/image/<int:image_id>')
def get_image(image_id):
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        query = "SELECT data, mime_type FROM images WHERE id = %s"
        cursor.execute(query, (image_id,))
        result = cursor.fetchone()

        if result is None:
            return "Image not found", 404

        image_data, mime_type = result

        return Response(image_data, mimetype=mime_type)

    except Error as e:
        print(f"Database error: {e}")
        return "Database error", 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

@app.route('/upload', methods=['POST'])
def upload_image():
    if 'file' not in request.files:
        return "No file part", 400

    file = request.files['file']
    if file.filename == '':
        return "No selected file", 400

    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        image_data = file.read()
        mime_type = file.mimetype

        query = "INSERT INTO images (name, data, mime_type) VALUES (%s, %s, %s)"
        cursor.execute(query, (file.filename, image_data, mime_type))
        connection.commit()

        return "Image uploaded successfully", 200

    except Error as e:
        print(f"Database error: {e}")
        return "Database error", 500
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

if __name__ == '__main__':
    app.run(debug=True)

这个实现提供了从 MySQL 数据库读取图片并返回给前端的基本功能，你可以根据实际需求进行扩展和优化。