Python数据库表行更新求助：用户未输入时保留原字段值

Hey there! Let's get your Python database update code working properly. You've got the right core idea—updating only the fields the user provides while leaving others untouched—but there are a few key issues with how you're building the query, handling parameters, and structuring your logic. Let's break this down step by step.

First, let's go over the main problems in your original code: #

• You're trying to use a list (dummy) like a string, which will throw errors when you do dummy += "Firstname="+First_name
• Your SQL execute calls don't pass parameters correctly (you define values after calling execute, and the order is wrong)
• conn.commit is missing parentheses—it should be conn.commit() to actually run the commit
• You're repeating update logic for every field combination, which gets messy fast
• Hardcoding the table name as customer4 when you mentioned your table is called customers
• Directly concatenating user input into SQL strings (this is a huge SQL injection risk!)

Here's the cleaned-up, working version of your code: #

import sqlite3  # Swap this for your database driver (e.g., psycopg2 for PostgreSQL, mysql-connector for MySQL)

# Establish database connection (adjust this to match your database setup)
conn = sqlite3.connect('your_database_file.db')
cursor = conn.cursor()

try:
    # Get the customer ID to update (with input validation)
    while True:
        try:
            user_id = int(input("Enter the identification number you want to change: ").strip())
            break
        except ValueError:
            print("Oops, please enter a valid whole number for the ID.")

    # Get user inputs for each field (strip removes accidental leading/trailing spaces)
    first_name = input("Enter the First name: ").strip()
    last_name = input("Enter the Last name: ").strip()

    # Prepare lists to build our dynamic update query
    update_clauses = []
    update_values = []

    # Add fields to update only if the user provided input
    if first_name:
        update_clauses.append("Firstname = ?")
        update_values.append(first_name)
    if last_name:
        update_clauses.append("Lastname = ?")
        update_values.append(last_name)

    # Only run the update if there are fields to change
    if update_clauses:
        # Build the full SQL query
        sql = f"UPDATE customers SET {', '.join(update_clauses)} WHERE identification = ?"
        # Add the customer ID to our values list (matches the final ? in the query)
        update_values.append(user_id)

        # Execute the query and commit changes
        cursor.execute(sql, update_values)
        conn.commit()
        print(f"{cursor.rowcount} record(s) successfully updated!")
    else:
        print("No fields were entered—no changes made to the database.")

except Exception as e:
    print(f"An error occurred: {str(e)}")
    conn.rollback()  # Undo any partial changes if something goes wrong
finally:
    # Always clean up database connections
    cursor.close()
    conn.close()

Key improvements explained: #

• Dynamic Query Building: We only add fields to the UPDATE statement if the user provided input for them—no unnecessary changes to fields the user wants to keep as-is.
• Parameterized Queries: Using ? as placeholders and passing values separately eliminates SQL injection risks (never concatenate user input directly into SQL!).
• Input Validation: We added a loop to make sure the user enters a valid integer for the customer ID, so your code won't crash with bad input.
• Error Handling: The try-except-finally block catches errors, rolls back changes if something breaks, and ensures database connections are always closed properly.
• Cleaner Logic: No repeated code for every possible combination of fields—all fields are handled the same way.

Quick notes for your setup: #

• If you're using a database other than SQLite (like MySQL or PostgreSQL), swap sqlite3 for the appropriate driver (e.g., mysql-connector-python or psycopg2-binary). The placeholder syntax might change (MySQL uses %s instead of ?), but the core logic stays the same.
• Replace your_database_file.db with your actual database path or connection string.

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