mysql图片保存与显示

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255)
);

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

import mysql.connector
from werkzeug.utils import secure_filename
import os

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 保存图片到服务器
file = request.files['avatar']
filename = secure_filename(file.filename)
file_path = os.path.join('uploads', filename)
file.save(file_path)

# 将文件路径存储到数据库
sql = "INSERT INTO users (username, avatar_url) VALUES (%s, %s)"
val = ("username", file_path)
cursor.execute(sql, val)
db.commit()

import mysql.connector
from werkzeug.utils import secure_filename
import os

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 保存图片到服务器
file = request.files['avatar']
filename = secure_filename(file.filename)
file_path = os.path.join('uploads', filename)
file.save(file_path)

# 读取图片二进制数据
with open(file_path, 'rb') as f:
    binary_data = f.read()

# 将二进制数据存储到数据库
sql = "INSERT INTO images (name, data) VALUES (%s, %s)"
val = (filename, binary_data)
cursor.execute(sql, val)
db.commit()

<img src="{{ user.avatar_url }}" alt="User Avatar">

from flask import send_file
import mysql.connector

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 从数据库中读取图片二进制数据
sql = "SELECT data FROM images WHERE id = %s"
cursor.execute(sql, (image_id,))
result = cursor.fetchone()
binary_data = result[0]

# 返回图片数据
return send_file(
    io.BytesIO(binary_data),
    mimetype='image/jpeg',
    as_attachment=False,
    attachment_filename='image.jpg'
)