使用pymysql按ID查询MySQL数据时遇SQL语法错误1064求助

Hey there, let's tackle that frustrating 1064 syntax error you're seeing. The message pointing to %s tells me the root issue is how you're passing the UUID parameter to your pymysql query—you're either not passing the parameter at all, or doing it in a way that leaves the raw %s placeholder in your SQL statement (which MySQL doesn't understand).

Common Mistakes That Cause This Error #

First, let's look at the wrong ways people often write this query (these are probably what you're doing right now):

# Mistake 1: Forgetting to pass the parameter to execute()
uuid = request.form['uuid']
sql = "SELECT id, source_server, source_path, destination_path FROM your_table WHERE id = %s"
cursor.execute(sql)  # No arguments passed—MySQL sees the raw %s and throws an error

# Mistake 2: Dangerous string concatenation (also risks SQL injection)
sql = f"SELECT ... WHERE id = {uuid}"  # If uuid has special chars, this breaks syntax AND is unsafe

The Correct Parameterized Query Approach #

Pymysql is designed to handle parameter substitution safely—you just need to pass your parameters as a second argument to execute(). Here's a complete, fixed example (adjust for your web framework/database config):

import pymysql
# If you're using Flask/Django/etc., import your request handler here
from flask import request

# Update these with your actual database credentials
DB_CONFIG = {
    'host': 'your_db_host',
    'user': 'your_db_user',
    'password': 'your_db_password',
    'database': 'your_db_name'
}

def fetch_data_by_uuid():
    # Get the UUID from the frontend form
    user_uuid = request.form.get('uuid')
    if not user_uuid:
        return "Please provide a valid UUID"
    
    try:
        # Connect to the database
        conn = pymysql.connect(**DB_CONFIG)
        # Use DictCursor to get results as dictionaries (easier to work with)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # 1. Write the query with %s as a placeholder (NO quotes needed!)
        query = """
            SELECT id, source_server, source_path, destination_path 
            FROM your_table_name 
            WHERE id = %s
        """
        # 2. Pass the parameter as a tuple to execute() (even single params need the comma!)
        cursor.execute(query, (user_uuid,))
        
        # Fetch the matching row (since id should be unique, fetchone() is perfect)
        result = cursor.fetchone()
        
        if result:
            # Do something with the result (return to frontend, print, etc.)
            return f"Found data: {result}"
        else:
            return "No matching record found for that UUID"
    
    except pymysql.MySQLError as e:
        return f"Database error occurred: {str(e)}"
    
    finally:
        # Always clean up connections/cursors to avoid leaks
        if cursor:
            cursor.close()
        if conn:
            conn.close()

Key Things to Remember #

• No quotes around %s: Pymysql automatically handles quoting based on the parameter type (string, int, etc.)—adding quotes manually will break the syntax.
• Use a tuple for parameters: Even if you're passing just one value, you need to use (user_uuid,) (the comma makes it a tuple instead of a single value).
• Avoid string concatenation: This isn't just about syntax errors—it protects you from SQL injection attacks, which are a critical security risk.
• Match data types: If your id column is an integer, convert the incoming UUID string to an int first with int(user_uuid).

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