Updating a Mysql database using an external variable

Question

I am trying to update (not insert) a python variable into a Mysql database. Searches show me many examples of the UPDATE being set to a constant,and this works for me: fragment

mycursor.execute("UPDATE enviromental SET temp = 10  WHERE room = 'Hot Water Tank A'")

mydb.commit()

works well,

but,

print (hot_water_temp)

mycursor.execute("UPDATE enviromental SET temp = hot_water_temp  WHERE room = 'Hot Water Tank A'")

mydb.commit()

gives me:

Traceback (most recent call last): File "/srv/nfs/mysqlconnector.py", line 20, in mycursor.execute("UPDATE enviromental SET temp = hot_water_temp WHERE room = 'Hot Water Tank A'") File "/home/paul/.local/lib/python3.11/site-packages/mysql/connector/cursor.py", line 551, in execute self._handle_result(self._connection.cmd_query(stmt)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/paul/.local/lib/python3.11/site-packages/mysql/connector/connection.py", line 490, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/paul/.local/lib/python3.11/site-packages/mysql/connector/connection.py", line 395, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'hot_water_temp' in 'field list'


Is my python variable just not seen inside the SQL statement? I not, how can I get it into the statement?

This must be a pretty common thing to want to do, but none of my searches gave me even a hint of how this should be done

Regards

Read the docs, especially about the params parameter: dev.mysql.com/doc/connector-python/en/… — Michael Butscher
– Michael Butscher, Commented Feb 18, 2024 at 18:21
I used the link to modify to: updatedata = ( "UPDATE enviromental SET temp = %s WHERE room = %s" "VALUES (%s, %s)" ) data = (hot_water_temp, 'Hot Water Tank A') mycursor.execute(updatedata, data) But the error now is now that there are not enough parameters for the SQL statement — Paul Lavender
– Paul Lavender, Commented Feb 18, 2024 at 19:09
UPDATE does not take a VALUES clause. The number of parameters in your data list must be the same as the number of %s placeholders in your query. — Bill Karwin
– Bill Karwin, Commented Feb 18, 2024 at 20:57

Paul Lavender · Accepted Answer · 2024-02-19 14:11:36Z

0

Finally puzzled this out, but the linked documentation was of little use, as like most other posted information, it references the INSERT, rather than UPDATE. It was essential to use two variables, looking like this:

sql_update_query = """UPDATE enviromental SET temp = %s WHERE room = %s"""

input_data = (hot_water_temp, 'Hot Water Tank A')

mycursor.execute(sql_update_query, input_data)

I sill don't quite understand what is going on, but this fragment works.

edited Feb 19, 2024 at 14:11

answered Feb 19, 2024 at 9:35

Paul Lavender

13 bronze badges

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

Collectives™ on Stack Overflow

Updating a Mysql database using an external variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related