服务器端TXT文件上传后按首行内容重命名并存储至MySQL需求

Got it, let's walk through how to build this feature step by step. I'll use Python with Flask for the server-side logic (super common for file handling tasks) and pymysql for MySQL interactions—feel free to adapt this to your stack (like Node.js/PHP) if needed.

Step 1: Receive Uploaded File & Extract Target Filename #

First, we need to accept the uploaded .txt file, read its first line, and parse out the target name (like the username in your example).

For instance, if the first line is Username: DemoUser, we'll split the line to grab DemoUser and append .txt to get our new filename.

from flask import Flask, request
import os

app = Flask(__name__)
UPLOAD_FOLDER = './temp_uploads'
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

@app.route('/upload', methods=['POST'])
def upload_file():
    # Get the uploaded file
    uploaded_file = request.files.get('file')
    if not uploaded_file or uploaded_file.filename.split('.')[-1].lower() != 'txt':
        return "Please upload a valid .txt file", 400

    # Save temp file to read first line
    temp_path = os.path.join(UPLOAD_FOLDER, uploaded_file.filename)
    uploaded_file.save(temp_path)

    # Read first line and extract target name
    try:
        with open(temp_path, 'r', encoding='utf-8') as f:
            first_line = f.readline().strip()
            # Parse the username (adjust this logic to match your first line format)
            if not first_line.startswith('Username: '):
                os.remove(temp_path)
                return "First line must follow 'Username: [Name]' format", 400
            target_name = first_line.split(': ')[1].strip()
            new_filename = f"{target_name}.txt"
    except Exception as e:
        os.remove(temp_path)
        return f"Error reading file: {str(e)}", 500

Step 2: Rename the File & Handle Conflicts #

Next, rename the temp file to our new filename. We should handle cases where a file with the same name already exists (to avoid overwriting)—adding a timestamp or counter suffix is a simple fix.

# Check for duplicate filenames and adjust if needed
new_path = os.path.join(UPLOAD_FOLDER, new_filename)
counter = 1
while os.path.exists(new_path):
    new_filename = f"{target_name}_{counter}.txt"
    new_path = os.path.join(UPLOAD_FOLDER, new_filename)
    counter += 1

# Rename the temp file
os.rename(temp_path, new_path)

Step 3: Store the Renamed File in MySQL #

Now we'll read the renamed file's content and store it in a MySQL table. First, set up your table with columns for the filename and file content:

CREATE TABLE uploaded_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_content LONGBLOB NOT NULL,
    upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then add the code to insert the file into the database:

import pymysql

# Database connection (replace with your DB credentials)
db = pymysql.connect(
    host='your_host',
    user='your_user',
    password='your_password',
    database='your_db'
)
cursor = db.cursor()

# Read file content
with open(new_path, 'rb') as f:
    file_content = f.read()

# Insert into database
try:
    sql = "INSERT INTO uploaded_files (filename, file_content) VALUES (%s, %s)"
    cursor.execute(sql, (new_filename, file_content))
    db.commit()
    # Clean up the local file if you don't need it anymore
    os.remove(new_path)
    return f"File uploaded and saved as {new_filename}", 200
except Exception as e:
    db.rollback()
    os.remove(new_path)
    return f"Database error: {str(e)}", 500
finally:
    cursor.close()
    db.close()

Key Notes to Avoid Issues #

• Validation: Always validate the uploaded file is a .txt, and that the first line matches your expected format (to prevent invalid filenames like those with special characters).
• File Size: Add a max file size limit in Flask using app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024 (16MB example) to avoid oversized uploads crashing your server.
• Encoding: Use the correct encoding when reading the file (utf-8 works for most cases, but adjust if your files use other encodings like latin-1).
• Error Handling: Wrap file operations and database calls in try/except blocks to catch issues like permission errors, broken DB connections, or empty files.

内容的提问来源于stack exchange，提问作者Marko Stevanović