update query python mysql.connector-CodePudding

I'm doing a web scraping project and now I'm trying to store my data into a MySQL database (remote server). I'm sure the DB config is done write, but I keep getting this error:

Traceback (most recent call last):
  File "C:**\main.py", line 14, in <module>
    cnx.commit()
File "C:**\mysql\connector\connection_cext.py", line 406, in commit
    self._cmysql.commit()
_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now

Here is my code (main.py):

import mysql.connector

cnx = mysql.connector.connect(user='XXXX', password='XXXXX',
                              host='XXXXXXXX',
                              database='sql4456946')
cursor = cnx.cursor()

maxID = ("SET @lastid = (SELECT MAX(`id`) FROM `stand`); "
         "UPDATE `stand` SET `price` = 9999 WHERE `id` = @lastid")
cursor.execute(maxID)
cnx.commit()

CodePudding user response：

You have a multiquery so you must use the parameter in the connection string

But you could make it in one query only

CREATE TABLE stand(id int ,price DECIMAL(19,2) )

INSERT INTO stand VALUES (1,100)

    UPDATE `stand` SET `price` = 9999 WHERE `id` = (SELECT MAX(`id`) FROM (SELECT `id` FROM `stand`)  st)

SELECT * FROM stand

id |   price
-: | ------:
 1 | 9999.00

db<>fiddle here