update query python mysql.connector

Question

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

nbk · Accepted Answer · 2021-12-06 23:35:54Z

1

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

answered Dec 6, 2021 at 23:35

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

Miguel Rodrigues Over a year ago

I've checked the documentation and I still don't understand what is that "st" you added at the end of the query. Is it just to say it is a multiquery? But it works now with it, thank you!

nbk Over a year ago

mysql doesn't like when you update a table and use it in a subquery, that is why you need a temporarytable which is created with (SELECT id FROM stand) st and st is that temporary table. to understand it further, an update changes rows so that the result of the subselect can change, a´the created temporary table is fixed and will not be changed by the update and it is now deterministic

Collectives™ on Stack Overflow

update query python mysql.connector

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related