Re: MySQL from Python Error

Sun, 27 Jun 2004 09:37:10 -0700

I've not used Python, but it looks like the query has invalid syntax once it gets to mysql. The last error line is the key:

1064, "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
'139.80'' WHERE competitorID=1 AND sku = ''50300288''' at line 1"

Look at all the quotes. It appears to me that you and Python are both adding quotes to your values. I think your actual query ends up as

  UPDATE product  SET price = ''139.80''
  WHERE competitorID=1 AND sku = ''50300288'';

Let me change the spacing for emphasis:

  UPDATE product  SET price = ''
  139.80''
  WHERE competitorID=1 AND sku = ''
  50300288'';

See?

I think cursor.execute is quoting your values as it inserts them. Try it without quotes and see what happens. You don't need quotes around the price, anyway.

As an alternative, I'd suggest loading the complete query into a single string first, then passing that complete query string to your function. In this case, I expect cursor.execute would have nothing to quote, so you would need to explicitly quote strings in your query. To aid debugging, you should add a line to print the query string before sending it, or perhaps only in the event of an error, so you can compare what's actually sent to what is supposed to be sent.

Michael

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.

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()




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to