At 19:08 -0700 6/26/04, Paul Maine wrote:
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.
If you are using the MySQLdb Python module for MySQL (I assume you are,
but you don't say), then you should use %s for placeholder markers.
That's the only supported format specifier for MySQLdb.
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))
In addition, this query is incorrect. Use just %s, not '%s'. The
quotes here are incorrect. When MySQLdb performs parameter substitution,
it'll add the quotes as necessary. (For example, if you want to bind a
NULL value, you definitely don't want quotes in the value.
Hmm, the code you show below looks oddly familiar, so I'll just conclude
with this: See page 105, last two paragraphs. :-)
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()
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]