使用ON DUPLICATE KEY UPDATE批量更新MySQL玩家分数的参数问题求助

Your issue boils down to two key problems: a mismatch between your query's placeholders and the data you're passing, plus incorrect syntax for the score accumulation logic. Let's break this down and fix it step by step.

1. Correct the Score Accumulation Syntax #

When using ON DUPLICATE KEY UPDATE, you don't need an extra placeholder for the score. Instead, you can reference the value you tried to insert using VALUES(Score), then add it to the existing Score column in the database. The correct update clause should be:

Score = Score + VALUES(Score)

Here, Score refers to the existing value stored in the row, and VALUES(Score) is the new score you're trying to insert for that player. Adding them together gives you the accumulated total score.

2. Fix the Placeholder Mismatch #

Your original query had three %s placeholders, but each tuple in records_to_insert only contains two values (player name and score). This would cause a parameter count error. The corrected query only needs two placeholders for the INSERT portion.

3. Ensure a Unique Constraint on the Player Column #

For ON DUPLICATE KEY UPDATE to trigger, the Player column must have a unique constraint (either as a PRIMARY KEY or a UNIQUE index). If you haven't set this up yet, run this SQL command to add it:

ALTER TABLE results ADD UNIQUE KEY idx_player (Player);

Corrected Code #

Here's your updated code with all fixes applied, plus some improvements for robustness:

from mysql.connector.cursor import MySQLCursorPrepared
import mysql.connector

print(playerNames, playerScore)
try:
    # MariaDB Connection
    con = mysql.connector.connect(port=5004, user='root', password='password', host='localhost', database='scoreboard')
    # Cleanly create records list (works for any equal-length lists)
    records_to_insert = list(zip(playerNames, playerScore))
    # Corrected SQL query
    sql_insert_query = "INSERT INTO results (Player, Score) VALUES (%s, %s) ON DUPLICATE KEY UPDATE Score = Score + VALUES(Score);"
    
    # Use prepared cursor for safer, repeated queries
    myCursor = con.cursor(cursor_class=MySQLCursorPrepared)
    myCursor.executemany(sql_insert_query, records_to_insert)
    con.commit()
    print(f"Successfully inserted/updated {myCursor.rowcount} records")
except mysql.connector.Error as err:
    print(f"Error occurred: {err}")
    con.rollback()
finally:
    # Clean up resources to prevent leaks
    if myCursor:
        myCursor.close()
    if con:
        con.close()

Key Notes #

• Using list(zip(playerNames, playerScore)) is a cleaner, scalable way to create your records list instead of manual indexing.
• Adding error handling with try/except and rollback ensures your database stays consistent if something goes wrong.
• Closing the cursor and connection in a finally block prevents resource leaks.

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