470,849 Members | 859 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

MySQL error from Python

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()
Jul 18 '05 #1
3 2262
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)

iD8DBQFA3hDOJd01MZaTXX0RAgIqAJ4toIJNhq3Mc2UOSYvBg6 hNxXyO5gCeMdiA
ntIqmZLA3Ik5YWYqM3nL8TA=
=PZl8
-----END PGP SIGNATURE-----

Jul 18 '05 #2
On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <no***@nohow.com> declaimed
the following in comp.lang.python:

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.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #3
That fixed it.

Thank You!
"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote in message
news:4r********************************@4ax.com...
On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <no***@nohow.com> declaimed
the following in comp.lang.python:

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.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Cathy Hui | last post: by
3 posts views Thread by Mark Adams | last post: by
reply views Thread by google account | last post: by
2 posts views Thread by simen.haugen | last post: by
1 post views Thread by Steve Ametjan | last post: by
reply views Thread by Edwin.Madari | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.