Flask通过路由参数查询MySQL时出现SQL语法错误求助

Hey there! Let's sort out that frustrating MySQL syntax error you're hitting when trying to fetch data by ID from your Flask route. That 1064 (42000) error pointing to %(site_id)s tells us exactly what's wrong—you're using the wrong parameter placeholder format for mysql.connector.

The Root Cause #

mysql.connector doesn't use named placeholders like %(site_id)s by default for parameterized queries. When you pass that into your SQL statement, MySQL interprets it as invalid syntax instead of a placeholder for your value. Instead, it expects positional placeholders using %s.

Wrong vs. Right Query Examples #

Let's start with what you might be doing wrong, then fix it:

❌ Incorrect (Causes Syntax Error)

# This will throw that 1064 error
query = "SELECT * FROM sites WHERE id = %(site_id)s"
cursor.execute(query, {"site_id": site_id})

✅ Correct (Uses Positional Placeholders)

# Proper parameterized query for mysql.connector
query = "SELECT * FROM sites WHERE id = %s"
cursor.execute(query, (site_id,))  # Note the comma—this makes it a tuple!

If you really prefer named placeholders, you can enable prepared statements in your connection, but positional placeholders are simpler and more commonly used with mysql.connector.

Full Working Flask Route Example #

Here's a complete, safe route that fetches a site by ID without errors, plus best practices for database connections:

from flask import Flask, abort
import mysql.connector

app = Flask(__name__)

# Store DB config securely (use environment variables in production!)
DB_CONFIG = {
    "user": "your_db_user",
    "password": "your_db_password",
    "host": "localhost",
    "database": "your_database_name"
}

@app.route("/sites/<int:site_id>")
def get_single_site(site_id):
    try:
        # Use context managers to auto-close connections/cursors
        with mysql.connector.connect(**DB_CONFIG) as conn:
            with conn.cursor(dictionary=True) as cursor:
                # Parameterized query to avoid SQL injection
                query = "SELECT * FROM sites WHERE id = %s"
                cursor.execute(query, (site_id,))
                site_data = cursor.fetchone()

                if not site_data:
                    abort(404, description="Site not found")
                
                return f"Site Found: {site_data}"

    except mysql.connector.Error as db_err:
        return f"Database Error: {str(db_err)}", 500

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

Key Tips to Avoid Future Issues #

• Never use string concatenation for SQL queries (e.g., f"SELECT * FROM sites WHERE id = {site_id}"). This opens your app up to SQL injection attacks—always use parameterized queries.
• Validate route parameters: Using <int:site_id> ensures only integers are passed to your route, so you don't have to handle non-numeric ID values in your query.
• Use context managers (with statements): They automatically close database connections and cursors, preventing resource leaks.

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