CODEWITHSUNDEEP
pythonmysqlmysql-python
Matt Stokes
Matt Stokes

Reputation: 4968

MySQLdb Python insert %d and %s

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)

Upvotes: 31

Views: 100723

Answers (4)

wyz
wyz

Reputation: 31

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)

Upvotes: 3

lwzhuo
lwzhuo

Reputation: 324

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

Upvotes: 10

Arovit
Arovit

Reputation: 3719

You missed a '%' in string formatting

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

Upvotes: -4

xor
xor

Reputation: 2698

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

Upvotes: 57

Related Questions