Problems with execute parameters type in MySQLdb

Question

``` c.execute("UPDATE Players SET %s = %d WHERE nick=%s", (parts[1], int(parts[2]), parts[0])) ``` is giving me the error ``` TypeError: %d format: a number is required, not str ``` I know `execute` should take only `%s`, but `parts[2]` should be cast to int, becacuse it will be an int (inputted as a string). So, if I put only `%s`'s, of course it comes up with this error: ``` mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; " "check the manual that corresponds to your MySQL server version " "for the right syntax to use near " "''wins' = '450' WHERE nick='xan'' at line 1") ``` Because `wins` is an integer. What is the solution to this?

Accepted Answer

I see a couple of problems. There may be more.

You cannot parametrize table nor column names. You can, however, do the string substitution and then call `.execute()`. If your program takes user input, you'll want to be wary of the potential for SQL-injection attack.

Always use `%s` no matter the type of the parameter. MySQLdb handles any quoting that needs to be done.

Example:

```
sql_stmt = "UPDATE Players SET {} = %s WHERE nick=%s;".format(parts[1])
c.execute(sql_stmt, (int(parts[2]), parts[0]))
```

Problems with execute parameters type in MySQLdb

Solution

Problem