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_s cripts\commerce craft.py", line 36, in ?
cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice, mysku))
File "E:\Python22\Li b\site-packages\MySQLd b\cursors.py", line 95, in
execute
return self._execute(q uery, args)
File "E:\Python22\Li b\site-packages\MySQLd b\cursors.py", line 114, in
_execute
self.errorhandl er(self, exc, value)
File "E:\Python22\Li b\site-packages\MySQLd b\connections.p y", line 33, in
defaulterrorhan dler
raise errorclass, errorvalue
_mysql_exceptio ns.ProgrammingE rror: (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.Scra pe(mysku)
price = result.split(" ")
tmpprice = str(price[0])

conversion = string.maketran s("$"," ")
myprice = tmpprice.transl ate(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,my sku))
(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)

iD8DBQFA3hDOJd0 1MZaTXX0RAgIqAJ 4toIJNhq3Mc2UOS YvBg6hNxXyO5gCe MdiA
ntIqmZLA3Ik5YWY qM3nL8TA=
=PZl8
-----END PGP SIGNATURE-----

[Dennis Lee Bieber]

On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <no***@nohow.co m> declaimed
the following in comp.lang.pytho n:

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.

-- =============== =============== =============== =============== == <
wl*****@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
=============== =============== =============== =============== == <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.ne tcom.com/> <

[Paul M]

That fixed it.

Thank You!
"Dennis Lee Bieber" <wl*****@ix.net com.com> wrote in message
news:4r******** *************** *********@4ax.c om...

On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <no***@nohow.co m> declaimed
the following in comp.lang.pytho n:

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.

--

> =============== =============== =============== =============== == <
> wl*****@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <