MySQL error from Python

[Paul M]

I encountered the following error when trying to perform a SQL UPDATE to a
MySQL database table from Python.

I would apprciate any assistance. In the Python code I have tried integer
and decimal format specifiers in addition to the string specifier and
nothing worked.

Traceback (most recent call last):
File "e:\my_python_scripts\commercecraft.py", line 36, in ?
cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice,mysku))
File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
return self._execute(query, args)
File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
File "E:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in
defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax. Check the manual that cor
responds to your MySQL server version for the right syntax to use near
'139.80'' WHERE competitorID=1 AND sk
u = ''50300288''' at line 1")




Describe product;

Field Type Null Key Default Extra
------------ ------------ ------ ------ ------- --------------
productID int(11) PRI (NULL) auto_increment
sku varchar(50) YES (NULL)
description varchar(60) YES (NULL)
price decimal(7,2) YES (NULL)
scrape_date datetime YES (NULL)
competitorID int(11) YES (NULL)



**************************************************************
import test_iopus
import MySQLdb
import sys
import string


try:
conn = MySQLdb.connect (host = "localhost",
user = "root",
passwd = "xyz",
db = "commerce")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)

cursor = conn.cursor()
cursor.execute ("SELECT sku FROM product WHERE competitorID=1")
while (1):
row = cursor.fetchone ()
if row == None:
break
mysku = row[0]
print mysku
print "%d rows were returned" % cursor.rowcount

result = test_iopus.Scrape(mysku)
price = result.split(" ")
tmpprice = str(price[0])

conversion = string.maketrans("$"," ")
myprice = tmpprice.translate(conversion)


print myprice

cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1 AND
sku = '%s'",(myprice,mysku))
cursor.close()
conn.close()

 

[Jeff Epler]

You probably should write
cursor.execute ("UPDATE product SET price = %s WHERE competitorID=1"
" AND sku = %s",(myprice,mysku))
(remove the single-quotes -- the splitting of the string makes no
difference vs a single line) not

cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice,mysku))

Jeff

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFA3hDOJd01MZaTXX0RAgIqAJ4toIJNhq3Mc2UOSYvBg6hNxXyO5gCeMdiA
ntIqmZLA3Ik5YWYqM3nL8TA=
=PZl8
-----END PGP SIGNATURE-----

 

[Dennis Lee Bieber]

cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice,mysku))

Get rid of the single quotes around the %s fields... The
..execute method should, in theory, determine the data type and needed
quoting internally.

--

 

[Paul M]

That fixed it.

Thank You!

Get rid of the single quotes around the %s fields... The
.execute method should, in theory, determine the data type and needed
quoting internally.

--