Not all parameters were used in the SQL statement

Question

Thanks for reading my post.

I want to do a easy thing: take info from one database and inject into another.

PRAGMA_fl_product = ACCESS_cursor.execute('SELECT Prekes.PrekeID, Prekes.NomNr, AmburiaSandLik.Likutis, Prekes.PardKaina, AmburiaSandLik.Likutis, Prekes.PardKaina FROM Prekes INNER JOIN AmburiaSandLik ON Prekes.PrekeID=AmburiaSandLik.PrekeID;').fetchall()

flproduct="INSERT INTO fl_product (product_id, model, quantity, prices, status) VALUES ('%s', '%s', '%s', '%s', '1') ON DUPLICATE KEY UPDATE quantity='%s', price='%s'"
SQLcursor.executemany(flproduct, PRAGMA_fl_product)

And this throws an error:

SQLcursor.executemany(flproduct, PRAGMA_fl_product)
File "C:\Program Files (x86)\Python 3.5\lib\site-packages\mysql\connector\cursor.py", line 606, in executemany
stmt = self._batch_insert(operation, seq_params)
File "C:\Program Files (x86)\Python 3.5\lib\site-packages\mysql\connector\cursor.py", line 553, in _batch_insert
"Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Can someone help me understand the problem that I am facing?

I SELECT 6 columns and try to use 6 '%s' escape entries, but seem to miss some thing.

Answer 1

I found this question because I had a similar situation:

curs.execute('INSERT INTO tbl (a, b) VALUES (%s, %s) ON DUPLICATE KEY UPDATE b = %s', (1,2,2))

Which was giving the same error when called. When I changed the query to named parameters:

curs.execute('INSERT INTO tbl (a, b) VALUES (%(a)s, %(b)s) ON DUPLICATE KEY UPDATE b = %(b)s', {a: 1, b: 2})

This errored with:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%(b)s' at line 1

From this I concluded that the connector doesn't recognise parameters after UPDATE. So my workaround was:

curs.execute('INSERT INTO tbl (a, b) VALUES (%(a)s, %(b)s) ON DUPLICATE KEY UPDATE b = VALUES(b)', {a: 1, b: 2})

Which uses the VALUES function to get the new value that would have been written, if there wasn't a duplicate key error. See mysql docs.

In your case, this would be:

flproduct="INSERT INTO fl_product (product_id, model, quantity, prices, status) VALUES ('%s', '%s', '%s', '%s', '1') ON DUPLICATE KEY UPDATE quantity=VALUES(quantity), prices=VALUES(prices)"