:TypeError: %d format: a number is required, not str: even numbers were given in python pandas

Question

I am trying to update the values in the mysql database using parameterized queries. But after running the below code:

df = pd.read_sql('SELECT * FROM df LIMIT 3', con=db_connection)
print(df.shape)
labels = ('I1','I2','I3')
ids = (123456,123457,123458)
data = (labels,ids)
cursor = db_connection.cursor()
for i in df.id:
    cursor.execute("UPDATE df SET label = %s WHERE id= %d",data)
    db_connection.commit()
db_connection.close()

I am getting this error: TypeError: %d format: a number is required, not str

The data type of 'id' is int in database. And I am providing numbers only. don't know why it is throwing such error...

Answer 1

Thanks for the help AlEmerich, I was giving data in this format

labels = ('I1','I2','I3')
ids = (123456,123457,123458)
data = (labels,ids)
data
(('I1', 'I2', 'I3'), (123456, 123457, 123458))

But when I made it in this way:

data = (('I1',123456),('I2',123457),('I3',123458))

It works fine now.

Answer 2

cursor.execute("UPDATE df SET label = %s WHERE id= %d",data)

You have a tuple of tuple in data. It seems that the compiler tries to format the 'I2' variable in '%d'.

Answer 3

In my case I was using flask_sqlalchemy and I made a mistake creating the model

I wrote

class LocationModel(db.Model):
      address = db.String(db.String(50))

instead

class LocationModel(db.Model):
      address = db.Column(db.String(50))