• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2369
  • Last Modified:

SQL update

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.

 /var/www/cgi-bin/donotneed.py
  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'")
  166
  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")
0
astar666
Asked:
astar666
  • 2
1 Solution
 
durgaprasad_jCommented:
hi,
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

DP
0
 
astar666Author Commented:
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.
0
 
astar666Author Commented:
The problem  solution is as follows:

consider item='%s'

it must be item="%s"
0
 
mish33Commented:
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 ' '
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now