MySQLdb Python insert %d and %s

Question

Precursor:

MySQL Table created via:
CREATE TABLE table(Id INT PRIMARY KEY NOT NULL, Param1 VARCHAR(50))

Function:

.execute("INSERT INTO table VALUES(%d,%s)", (int(id), string)

Output:

TypeError: %d format: a number is required, not a str

I'm not sure what's going on here or why I am not able to execute the command. This is using MySQLdb in Python. .execute is performed on a cursor object.

EDIT:

The question: Python MySQLdb issues (TypeError: %d format: a number is required, not str) says that you must use %s for all fields. Why might this be? Why does this command work?

.execute("INSERT INTO table VALUES(%s,%s)", (int(id), string)

You are missing a `%` and `)` - `.execute("INSERT INTO table VALUES(%d,%s)"% (int(id), string))` — Hussain, Dec 09 '13 at 05:08

score 57 · Accepted Answer · edited Feb 01 '17 at 08:05

57

As the whole query needs to be in a string format while execution of query so %s should be used...

After query is executed integer value is retained.

So your line should be.

.execute("INSERT INTO table VALUES(%s,%s)", (int(id), string))

Explanation is here

edited Feb 01 '17 at 08:05

ΦXocę 웃 Пepeúpa ツ

47,427
17
69
97

answered Dec 09 '13 at 05:13

xor

2,668
2
28
42

I have received errors on python 2.7 with syntax like that. Atleast on my side if a parametized %s is expected my python expects a string value there, and I've had to convert it to string like str(someVal). – Kickass Mar 01 '18 at 22:33
@Kickass - You might want to check to see if you have a string interpolation operator (%) separating your SQL INSERT string and your values tuple instead of a comma. – Bob Kline Dec 28 '19 at 15:23
@xor How do I do this with just a single column? I have my ID as Autoincrement. so I need not add it. Only the field "`Param1`" (as in example here) needs to be added. `mycursor.execute("INSERT INTO mytable Param1 VALUES %s", (name))`. But it keeps saying Syntax error at `%s`. I also tried as `(%s)`. No use – Priya Jun 25 '21 at 10:23
@Priya I found the solution here: https://stackoverflow.com/questions/41309096/cant-insert-single-column-value-in-python-using-mysql – less Oct 15 '21 at 08:09

score 10 · Answer 2 · answered Aug 17 '17 at 15:50

10

The format string is not really a normal Python format string. You must always use %s for all fields

answered Aug 17 '17 at 15:50

lwzhuo

324
2
6

score 3 · Answer 3 · edited Apr 05 '20 at 06:40

I found a solution on dev.mysql.com. In short, use a dict, not a tuple in the second parameter of .execute():

add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}

cursor.execute(add_salary, data_salary)

Arovit · Answer 4 · 2017-04-22T00:16:03.160

-4

You missed a '%' in string formatting

"(INSERT INTO table VALUES(%d,%s)"%(int(id), string))

edited Apr 22 '17 at 00:16

answered Dec 09 '13 at 05:15

Arovit

3,579
5
20
24

1

i am sorry this s downvoted, but it is a way forward the answer `("INSERT INTO table VALUES(%d,'%s')"%(int(id), string))` you should edit it – Abr001am Apr 21 '17 at 14:57
1

It's much safer to get in the habit of using the parameters to the `cursor.execute` function to eliminate risk of injection: https://stackoverflow.com/a/775399/6284025 – TallChuck Aug 11 '17 at 20:58

MySQLdb Python insert %d and %s

4 Answers4

Linked