Operation code for storing and reading pictures in MySQL database

Method 1: Store the image in the database as BLOB type

MySQL statement implementation

Create a table：
First, you need to create a table in MySQL that contains BLOB fields to store the image.

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

Insert picture：
Assuming you already have image data (probably binary data read from a file), you can useINSERT INTO ... VALUESSentence to insert pictures.

INSERT INTO images (name, image) VALUES ('', LOAD_FILE('/path/to/'));

Note: The LOAD_FILE() function requires the MySQL server to have read permissions to the specified path, and the path must be in the directory specified by the secure_file_priv variable of the MySQL server (if the variable is enabled).

Read the picture:
To read images from the database, you can use the SELECT statement and process the returned binary data in the application.

SELECT image FROM images WHERE id = 1;

To retrieve image data and save it as a file, you can use the SELECT statement and INTO OUTFILE:

SELECT image FROM images WHERE id = 1 INTO OUTFILE '/path/to/your/';

Notice: Use LOAD_FILE() and INTO OUTFILE require corresponding file system permissions. Additionally, for large binary data, consider performance impact, as BLOB data types may affect database performance.

Python implementation

Insert picture：

import 

# Connect to the databaseconn = (
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)
cursor = ()

# Read image fileswith open('/path/to/', 'rb') as file:
    binary_data = ()

# Insert the image into the databasesql = "INSERT INTO images (name, image) VALUES (%s, %s)"
val = ('', binary_data)
(sql, val)
()

# Close the connection()
()

Read pictures：

import 
from io import BytesIO
from PIL import Image

# Connect to the databaseconn = (
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)
cursor = ()

# Read pictures from the databasesql = "SELECT image FROM images WHERE id = 1"
(sql)
result = ()
image_data = result[0]

# Use the PIL library to display picturesimage = (BytesIO(image_data))
()

# Close the connection()
()

Method 2: Store the image in the file system and store the path in the database

MySQL statement implementation

Create a table：
Create a table that contains only the image path or URL.

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

Insert the image path：
When the image file is saved to the file system, insert its path into the database.

INSERT INTO images (name, path) VALUES ('', '/path/to/');

Read the image path：
Read the image path from the database and use that path to access the image in the file system.

SELECT path FROM images WHERE id = 1;

Notice: Use LOAD_FILE() and INTO OUTFILE require corresponding file system permissions. Additionally, for large binary data, consider performance impact, as BLOB data types may affect database performance.

Python implementation

Save the picture to the file system and insert the path：

import os
import 

# Connect to the databaseconn = (
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)
cursor = ()

# Save the picture to the file systemfile_path = '/path/to/save/'
with open('/path/to/', 'rb') as source_file:
    with open(file_path, 'wb') as dest_file:
        dest_file.write(source_file.read())

# Insert the image path to the databasesql = "INSERT INTO images (name, path) VALUES (%s, %s)"
val = ('', file_path)
(sql, val)
()

# Close the connection()
()

Read the image path and display the image：

import 
from PIL import Image

# Connect to the databaseconn = (
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)
cursor = ()

# Read the image path from the databasesql = "SELECT path FROM images WHERE id = 1"
(sql)
result = ()
image_path = result[0]

# Use the PIL library to display picturesimage = (image_path)
()

# Close the connection()
()

Summarize

BLOB storage: Suitable for scenarios where small images are stored or data integrity is required, but may increase the size of the database and backup complexity.
File system storage: Suitable for storing large images or scenarios that require frequent access, it can reduce database load, but it needs to ensure the availability and security of the file system.

Which method to choose depends on the specific application scenario and requirements.

The above is the detailed content of the operation code for storing pictures and reading pictures in MySQL database. For more information about storing and reading pictures in MySQL, please pay attention to my other related articles!