Python Flask下Pandas DataFrame清洗特殊字符写入MySQL报错求助

Hey there, let's figure out why you're hitting that ProgrammingError and get your data into MySQL smoothly!

First, let's break down the problem: your WERT column has values like 'ZAST_DIR','6.3' with single/double quotes, and even after trying to replace them, you're still getting SQL syntax issues. The root cause here is likely either incomplete data cleaning or (more commonly) manually constructing SQL queries without using parameterization, which lets leftover special characters break your syntax.

Step 1: Ensure Thorough Data Cleaning #

Your existing replace methods might not be catching all instances of quotes, especially if some values aren't string types. Let's adjust the cleaning to be more robust:

# First, convert the entire column to string to avoid skipping non-string values
df['WERT'] = df['WERT'].astype(str)

# Use regex to remove ALL single and double quotes in one go
df['WERT'] = df['WERT'].str.replace(r'["\']', '', regex=True)

This ensures every value in the column is treated as a string, and we strip both ' and " regardless of their position in the text.

Step 2: Avoid Manual SQL Queries – Use Parameterized Inserts #

The biggest mistake that leads to syntax errors here is writing raw INSERT statements with your data directly embedded. Even with clean data, this is risky (hello, SQL injection!) and prone to syntax breaks. Instead, use Pandas' built-in to_sql method with Flask's SQLAlchemy connection, which handles parameterization automatically.

Here's how to implement this in your Flask app:

1. Set up your SQLAlchemy database connection (if you haven't already):

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pandas as pd

app = Flask(__name__)
# Replace with your MySQL credentials
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://your_username:your_password@localhost/your_db_name'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

2. After cleaning your DataFrame, write it to MySQL:

# Write the DataFrame to your MySQL table
# Use if_exists='append' to add rows, or 'replace' to overwrite the table
df.to_sql(
    name='your_target_table_name',
    con=db.engine,
    if_exists='append',
    index=False  # Don't write the Pandas index as a column
)

If You Need to Write Custom Queries #

If you have to use custom INSERT logic (instead of to_sql), always use parameterized queries with SQLAlchemy's text function to avoid syntax errors:

from sqlalchemy import text

with db.engine.connect() as conn:
    # Iterate over cleaned rows
    for _, row in df.iterrows():
        # Define parameterized query
        insert_stmt = text("""
            INSERT INTO your_target_table_name (WERT, other_column)
            VALUES (:wert_value, :other_value)
        """)
        # Execute with parameter values
        conn.execute(insert_stmt, {
            "wert_value": row['WERT'],
            "other_value": row['other_column']  # Replace with your actual columns
        })
    # Commit the transaction
    conn.commit()

Why This Works #

Parameterized queries separate your data from the SQL syntax, so even if there were leftover special characters (though our cleaning step should handle that), MySQL won't interpret them as part of the query structure. This eliminates the syntax error entirely and keeps your database safe from injection attacks.

Give these steps a try – your data should write to MySQL without any syntax hiccups!

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