Flask上传CSV至MySQL仅首行成功问题求助

Hey there, let's fix that CSV upload issue where only the first line makes it to your MySQL database! I've run into this exact problem before with Python 2.7 and Flask, so let's break down what's going wrong and how to fix it.

The Root Cause #

9 times out of 10, this happens because your code is only reading a single line from the CSV (usually the header) and not looping through all the actual data rows. Let's build a corrected script that handles full CSV uploads, with best practices for Flask and MySQL.

Full Working Solution #

Here's a complete, tested Flask route that reads every row from your CSV and bulk-inserts them into MySQL:

from flask import Flask, request, redirect, url_for
import csv
import MySQLdb
import StringIO

app = Flask(__name__)

# Replace these with your actual database credentials
DB_CREDS = {
    'host': 'localhost',
    'user': 'your_db_user',
    'passwd': 'your_db_password',
    'db': 'your_target_db'
}

@app.route('/upload-csv', methods=['GET', 'POST'])
def upload_csv():
    if request.method == 'POST':
        # Validate the uploaded file
        if 'csv_file' not in request.files:
            return "No file was uploaded", 400
        file = request.files['csv_file']
        if file.filename == '' or not file.filename.endswith('.csv'):
            return "Please upload a valid CSV file", 400

        # Process the CSV content
        csv_content = file.read()
        # Use StringIO to treat the file content as a readable stream
        csv_stream = StringIO.StringIO(csv_content)
        csv_reader = csv.reader(csv_stream)

        # Skip the header row (remove this line if your CSV has no header)
        next(csv_reader)

        # Collect all valid data rows
        data_rows = []
        for row in csv_reader:
            # Make sure each row has the correct number of fields (5 in your example)
            if len(row) == 5:
                data_rows.append((row[0], row[1], row[2], row[3], row[4]))

        if not data_rows:
            return "No valid data found in the CSV", 400

        # Bulk insert into MySQL
        try:
            db_conn = MySQLdb.connect(**DB_CREDS)
            cursor = db_conn.cursor()

            # Replace `your_table_name` with your actual table name
            insert_query = """
                INSERT INTO your_table_name (date_time, first_name, surname, address, email)
                VALUES (%s, %s, %s, %s, %s)
            """
            # Use executemany for bulk inserts (way faster than individual executes)
            cursor.executemany(insert_query, data_rows)
            db_conn.commit()

            return f"Success! Inserted {cursor.rowcount} rows into the database."
        except MySQLdb.Error as e:
            db_conn.rollback()
            return f"Database error: {str(e)}", 500
        finally:
            if db_conn:
                db_conn.close()

    # Simple upload form for GET requests
    return '''
    <!doctype html>
    <title>Upload CSV to MySQL</title>
    <h1>Upload Your CSV File</h1>
    <form method=post enctype=multipart/form-data>
      <input type=file name=csv_file accept=".csv">
      <button type=submit>Upload & Import</button>
    </form>
    '''

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

Key Fixes & Best Practices #

• Loop through all CSV rows: The for row in csv_reader loop ensures we process every data line, not just the first one.
• Bulk inserts with executemany: This is way more efficient than running INSERT for each row individually, and reduces database overhead.
• Header handling: The next(csv_reader) line skips your CSV's header row (remove this if your CSV doesn't have a header).
• Error handling: We include database rollbacks on failure to avoid partial data inserts, plus clear error messages for debugging.
• Input validation: We check for valid CSV files and correct row length to avoid bad data entering your database.

Extra Tips for Your Setup #

1. Date format conversion: If your MySQL date_time field is a DATETIME type, update the insert query to convert the CSV string to a valid datetime:

   INSERT INTO your_table_name (date_time, first_name, surname, address, email)
   VALUES (STR_TO_DATE(%s, '%Y/%m/%d %H:%i:%s'), %s, %s, %s, %s)
   

2. Encoding handling: If your CSV has non-ASCII characters, add encoding handling when reading the file:

   csv_stream = StringIO.StringIO(csv_content.decode('utf-8'))
   

3. Upload size limits: Add this to your Flask config to allow larger CSV files:

   app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB limit
   

内容的提问来源于stack exchange，提问作者J_Kirkman