SQL update

Posted on 2005-03-18
Medium Priority
Last Modified: 2012-05-05
I have python 2.2 and MySQLdb.   A sql update command generates errors of sql syntax.  Please advise.

Here is a fragment of python code:

cursor.execute("""UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s""",(zip,title,text,itemid,))

Here are the error messages:

 --> -->
ProgrammingError      Python 2.2.3: /usr/bin/python
Fri Mar 18 18:42:24 2005

A problem occurred in a Python script. Here is the sequence of function calls leading up to the error, in the order they occurred.

  694                 values["main"]=editingitem(cursor,type,itemid)
  695         elif action =="update":
  696                 values["main"]=updateitem(cursor,form)
  697         else:
  698                 values["main"]=badpass(cursor)
values = {'debug': '', 'head': '', 'menu': ' <table cellspacing="0" cellpadding="5" width="... </td>\r\n </tr>\r\n </tbody>\r\n </table>\r\n', 'subhead': ''}, updateitem = <function updateitem>, cursor = <MySQLdb.cursors.Cursor instance>, form = FieldStorage(None, None, [MiniFieldStorage('zip'...', 'update'), MiniFieldStorage('state', 'save')])

 /var/www/cgi-bin/donotneed.py in updateitem(cursor=<MySQLdb.cursors.Cursor instance>, form=FieldStorage(None, None, [MiniFieldStorage('zip'...', 'update'), MiniFieldStorage('state', 'save')]))
  398         if result is not None:
  399                 return "Only digits in zip"
  400         cursor.execute("""UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s""",(zip,title,text,itemid,))
  401         timestamp=DateTime.now()
  402         cursor.execute("""INSERT timestamp(source, timestamp) VALUES(%s,%s)""",(ip,timestamp))
cursor = <MySQLdb.cursors.Cursor instance>, cursor.execute = <bound method Cursor.execute of <MySQLdb.cursors.Cursor instance>>, zip = '98466', title = 'power brick for concord modem', text = '.8 amp, screw on power connector\r\n\r\nastar@spamcop.net', itemid = '0'

 /usr/lib/python2.2/site-packages/MySQLdb/cursors.py in execute(self=<MySQLdb.cursors.Cursor instance>, query="UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s", args=('98466', 'power brick for concord modem', '.8 amp, screw on power connector\r\n\r\nastar@spamcop.net', '0'))
   64         else:
   65             try:
   66                 r = self._query(query % self.__conn.literal(args))
   67             except TypeError, m:
   68                 if m.args[0] in ("not enough arguments for format string",
r undefined, self = <MySQLdb.cursors.Cursor instance>, self._query = <bound method Cursor._query of <MySQLdb.cursors.Cursor instance>>, query = "UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s", self.__conn undefined, args = ('98466', 'power brick for concord modem', '.8 amp, screw on power connector\r\n\r\nastar@spamcop.net', '0')

 /usr/lib/python2.2/site-packages/MySQLdb/cursors.py in _query(self=<MySQLdb.cursors.Cursor instance>, q=r"UPDATE items SET zip = ''98466'',title = ''power...tor\r\n\r\nastar@spamcop.net'' WHERE itemid = '0'")
  167     def _query(self, q):
  168         rowcount = self._BaseCursor__do_query(q)
  169         self._rows = self._result and self._fetch_row(0) or ()
  170         self._pos = 0
rowcount undefined, self = <MySQLdb.cursors.Cursor instance>, self._BaseCursor__do_query = <bound method Cursor.__do_query of <MySQLdb.cursors.Cursor instance>>, q = r"UPDATE items SET zip = ''98466'',title = ''power...tor\r\n\r\nastar@spamcop.net'' WHERE itemid = '0'"

 /usr/lib/python2.2/site-packages/MySQLdb/cursors.py in __do_query(self=<MySQLdb.cursors.Cursor instance>, q=r"UPDATE items SET zip = ''98466'',title = ''power...tor\r\n\r\nastar@spamcop.net'' WHERE itemid = '0'")
  110         from string import split, atoi
  111         db = self._get_db()
  112         db.query(q)
  113         self._result = self._get_result()
  114         self.rowcount = db.affected_rows()
db = <open connection to 'localhost' at 81b2ad0>, db.query = <built-in method query of connection object>, q = r"UPDATE items SET zip = ''98466'',title = ''power...tor\r\n\r\nastar@spamcop.net'' WHERE itemid = '0'"

ProgrammingError: (1064, "You have an error in your SQL syntax near '98466'',title = ''power brick for concord modem'',item = ''.8 amp, screw on powe' at line 1")
      __doc__ = 'Exception raised for programming errors, e.g. ta...t, wrong number\n of parameters specified, etc.'
      __getitem__ = <bound method ProgrammingError.__getitem__ of <_mysql_exceptions.ProgrammingError instance>>
      __init__ = <bound method ProgrammingError.__init__ of <_mysql_exceptions.ProgrammingError instance>>
      __module__ = '_mysql_exceptions'
      __str__ = <bound method ProgrammingError.__str__ of <_mysql_exceptions.ProgrammingError instance>>
      args = (1064, "You have an error in your SQL syntax near '98466...modem'',item = ''.8 amp, screw on powe' at line 1")
Question by:astar666
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 13582649
i dont know much about python [just started yesterday].
try this
cursor.execute("UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s" % (zip,title,text,itemid))

or just store this sql statement into a temporary string and check that string is proper.

sql1="UPDATE items SET zip = '%s',title = '%s',item = '%s' WHERE itemid = %s" % (zip,title,text,itemid)
print sql1


Author Comment

ID: 13583103
In mysqldb, cursor.execute takes two parameters.  The second parameter is a tuple that is substituted internal to cursor.execute.  In my project, there are alot of invocation of cursor.execute with two parameters, so at the end of the day, I do not think a gross change of syntax is warranted.  But this questions regards the first UPDATE instance, and so it is likely it is a sql syntax error.  Just as the error message said.  But what I wrote is pretty much what the reference I used for the other SQL syntaxes tells me to use.

Author Comment

ID: 13583951
The problem  solution is as follows:

consider item='%s'

it must be item="%s"
LVL 15

Accepted Solution

mish33 earned 1000 total points
ID: 13649923
Change you code to:

cursor.execute("UPDATE items SET zip=%s, title=%s, item=%s WHERE itemid = %s", (zip,title,text,itemid,))

because .execute() put %s arguments already quoted, so you end up with double quoted values which is the error like  zip = ' ' 9 8 4 6 6 ' '

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question