Python MySQL update statement with variables-CodePudding

I'm unable to get MySQL update statement working using variable. In my case I'm testing with just a simple list that contains couple of strings and I'm trying to execute following:

did_test = ['123456789', '123456788', '123456787']

sql_insert = """UPDATE JAX2 
            SET LOCATION = 'NY', ASSIGNEE = 'John Smith', IN_USE = '1' 
            WHERE DID = %s"""
mycursor.execute(sql_insert, did_test[1])
mydb.commit()

If I run WHERE condition as such:

WHERE DID = '123456788'

That works fine with no issue. I'm just trying to run this with variables because eventually I will be reading in this value from JSON... I keep getting this error:

mysql.connector.errors.ProgrammingError: Could not process parameters: str(123456788), it must be of type list, tuple or dict

CodePudding user response：

The second parameter to cursor.execute() needs to be a tuple, so use this version:

mycursor.execute(sql_insert, (did_test[1],))

Full script:

did_test = ['123456789', '123456788', '123456787']

sql_insert = """UPDATE JAX2 
            SET LOCATION = 'NY', ASSIGNEE = 'John Smith', IN_USE = '1' 
            WHERE DID = %s"""
mycursor.execute(sql_insert, (did_test[1],))
mydb.commit()

CodePudding user response：

Even if you have one single param, it has to be passed as a list or a tuple.

did_test = ['123456789', '123456788', '123456787']

sql_insert = """UPDATE JAX2 
            SET LOCATION = 'NY', ASSIGNEE = 'John Smith', IN_USE = '1' 
            WHERE DID = %s"""
mycursor.execute(sql_insert, [did_test[1]])
mydb.commit()