mysql建立图片数据库

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

import mysql.connector

def store_image(image_path):
    conn = mysql.connector.connect(user='your_user', password='your_password',
                                   host='your_host', database='your_database')
    cursor = conn.cursor()

    with open(image_path, 'rb') as file:
        binary_data = file.read()

    query = "INSERT INTO images (name, type, data) VALUES (%s, %s, %s)"
    values = (image_path.split('/')[-1], 'image/jpeg', binary_data)
    cursor.execute(query, values)
    conn.commit()
    cursor.close()
    conn.close()

store_image('/path/to/your/image.jpg')

def retrieve_image(image_id):
    conn = mysql.connector.connect(user='your_user', password='your_password',
                                   host='your_host', database='your_database')
    cursor = conn.cursor(buffered=True)

    query = "SELECT data FROM images WHERE id = %s"
    cursor.execute(query, (image_id,))
    result = cursor.fetchone()
    if result:
        image_data = result[0]
        with open(f'retrieved_{image_id}.jpg', 'wb') as file:
            file.write(image_data)
    cursor.close()
    conn.close()

retrieve_image(1)