如何去除Python生成SQL INSERT语句中VALUES后的首个多余逗号？

Your script is producing SQL INSERT statements with an extra leading comma immediately after the VALUES ( clause, e.g.:

INSERT INTO your_table VALUES (,'X01LA0295.JPG', 'other_value', ...)

This happens because you're prepending a comma to every value, including the first one. Here's how to fix it properly:

Cleanest Approach: Use List Joining #

Instead of building the value string by appending , 'value' each time, collect your values in a list and join them with commas. This avoids creating the invalid leading comma entirely.

Corrected Code Example

def build_insert_query(table_name, data_points):
    # Sanitize values to avoid SQL injection (basic example)
    sanitized_values = []
    for val in data_points:
        if isinstance(val, str):
            # Escape single quotes
            sanitized_val = val.replace("'", "''")
            sanitized_values.append(f"'{sanitized_val}'")
        else:
            sanitized_values.append(str(val))
    
    # Join values with commas for valid SQL syntax
    values_clause = ', '.join(sanitized_values)
    return f"INSERT INTO {table_name} VALUES ({values_clause})"

# Test the function
sample_data = ["X01LA0295.JPG", "Product Image", 45.99, False]
query = build_insert_query("product_media", sample_data)
print(query)

Output

INSERT INTO product_media VALUES ('X01LA0295.JPG', 'Product Image', 45.99, False)

Quick Fix for Existing Strings (If Needed) #

If you need to repair a pre-generated invalid query string (not recommended for new code), you can target and replace the leading comma in the VALUES clause:

# Fix an already built problematic query
invalid_query = "INSERT INTO product_media VALUES (,'X01LA0295.JPG', 'Product Image')"
valid_query = invalid_query.replace("VALUES (,", "VALUES (", 1)

Note: For production code, always use parameterized queries instead of string formatting to prevent SQL injection risks. The example above uses string formatting for demonstration only.

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