Python MySQL update statement with variables

Question

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

Tim Biegeleisen · Accepted Answer · 2022-04-12 16:02:11Z

1

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()

answered Apr 12, 2022 at 16:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Floh · Accepted Answer · 2022-04-12 16:01:26Z

0

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()

answered Apr 12, 2022 at 16:01

Floh

9195 silver badges17 bronze badges

Collectives™ on Stack Overflow

Python MySQL update statement with variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related