How I Fix a Game Score Database Error in My Code with Python

I recently completed a small but meaningful feature for a game I’ve been developing saving a player’s score to a database when the game ends with python. While it started with a simple goal, it helped me navigate a real world database error, refine the design, and apply best practices in Python and SQL. Let me walk you through the process, what went wrong, and how I improved it.

Original Game Code

Here was my initial attempt to save the score once the game ended:

def gameover():
    message = Msg("Game Over")
    message.update()
    player.kill()
    shot.kill()
        SQL = 'INSERT INTO TblScore(Score) VALUES (' + str(score.value)
        Databaseconnector.INSERT(SQL)

And the basic database connection code:

def INSERT(SQL):
    print(SQL)
    cursor.execute(SQL)
    conn.commit()

The Error I Encounter

Upon running this code, I received the following error in python:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.')

Why This Error Show

After a quick debug, here’s what I found:

• Missing Parenthesis: The SQL command was missing a closing ).
• Unsafe SQL Formatting: Concatenating SQL strings directly with + is not only error-prone but also a SQL injection risk.
• Indentation Error: The SQL and insert call were indented improperly, leading to potential runtime issues.

Correct and Safe Version

I updated the code using parameterized queries, which are not only cleaner but also far more secure.

gameover(score_value):
    message = Msg("Game Over")
    message.update()
    player.kill()
    shot.kill()

    # Correct SQL using parameterized query
    SQL = 'INSERT INTO TblScore (Score) VALUES (?)'
    Databaseconnector.INSERT(SQL, (score_value,))

Updated Database Function

I also revised the INSERT function to include proper error handling with python:

def INSERT(SQL, params):
    try:
        cursor.execute(SQL, params)
        conn.commit()
        print("Score saved successfully.")
    except Exception as e:
        print("Database error:", e)

Practice Add ons and Extra Functionality

To make the feature more robust and future-ready, I added a few practice enhancements:

Log Player Name with Score

gameover(score_value, player_name):
    message = Msg("Game Over")
    message.update()
    player.kill()
    shot.kill()

    SQL = 'INSERT INTO TblScore (PlayerName, Score) VALUES (?, ?)'
    Databaseconnector.INSERT(SQL, (player_name, score_value))

Show Top 5 High Scores

get_top_scores():
    SQL = 'SELECT PlayerName, Score FROM TblScore ORDER BY Score DESC LIMIT 5'
    cursor.execute(SQL)
    results = cursor.fetchall()
    print("🏆 Top 5 Scores:")
    for row in results:
        print(f"{row[0]} - {row[1]}")

Validate Score Before Saving

 isinstance(score_value, int) and score_value >= 0:
    Databaseconnector.INSERT(SQL, (player_name, score_value))
else:
    print("Invalid score. Score not saved.")

Final Thoughts

This project reminded me how small mistakes like a missing parenthesis or poor indentation can cause frustrating errors. But with a bit of debugging and refactoring, I not only solved the problem but also improved the system’s security and functionality.

By using parameterized queries, input validation, and clear error handling, I made the code safer and more maintainable. I’m also excited to expand this with more features like recording the game level, time played, or adding a leaderboard UI.