Python MySQL带变量的UPDATE语句语法问题咨询

Let's break down the issues in your code and fix them step by step. First, I notice you're using SQLite (since you're calling lite.connect()—I assume that's sqlite3.connect()), so let's focus on the key problems with your UPDATE logic:

• Missing transaction commit: SQLite requires you to commit the transaction after executing an UPDATE (or any write operation), otherwise the changes won't be saved to the database.
• Unnecessary fetchall() call: The UPDATE statement doesn't return a result set, so cur.fetchall() will just return an empty list. If you want to check how many rows were updated, use cur.rowcount instead.
• Unclosed database connections/cursors: It's good practice to close your cursor and connection after use, or better yet, use a with statement to handle this automatically.
• Potential confusion with global userName: You mentioned userName is a global variable, but you're also passing it as a function parameter. This could lead to unexpected behavior—stick to one approach (either use the global or pass it as an argument, not both).

Fixed Code Example #

import sqlite3  # Assuming you meant sqlite3 here

def editInfo(start, userName):
    newFavGenre = input("Enter your favourite genre: ")
    newFavArtist = input("Enter your favourite artist: ")
    
    # Use with statement to auto-manage connection and cursor
    with sqlite3.connect(db) as con:
        cur = con.cursor()
        # Your original parameterized query syntax was correct!
        cur.execute("""
            UPDATE users 
            SET favGenre = %s, favArtist = %s 
            WHERE username = %s 
        """, (newFavGenre, newFavArtist, userName))
        
        # Get the number of rows affected by the update
        updated_rows = cur.rowcount
        print(f"Updated {updated_rows} row(s)")
        
        # The with statement automatically commits if no exceptions occur

    return updated_rows  # Return meaningful feedback instead of incomplete return

Key Notes on the Fixes #

• Parameterized query: Your original UPDATE syntax was actually correct! Using %s as placeholders with a tuple of values is the safe, recommended way to prevent SQL injection in SQLite—nice work on that part.
• Transaction handling: The with statement for the connection will automatically commit the transaction when the block exits (unless an exception is raised, in which case it rolls back). If you don't use with, you need to add con.commit() right after cur.execute().
• Resource management: The with statement ensures the connection and cursor are closed properly, even if an error occurs during execution.
• Useful feedback: Replacing fetchall() with cur.rowcount gives you clear visibility into how many rows your update actually modified.

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