[Tutor] mysql formatting

[Lloyd Kvam]

I checked our programming.  We add the %  for like to the parameter!

s = 'update doorman set status = %s where in_id = %s and out_address like %s'
cursor.execute(s, (new_status, inside_id, '%'+out_address+'%'))

On Wed, 2004-11-03 at 14:30, Marilyn Davis wrote:
> Darn me.
> 
> I still don't have it right.
> 
> As a reminder, here's my method:
> 
>      def execute_mysql(self, this, *args):
>          if log.level & log.sql:
>              log.it('About to execute:' + this + '<-->' + repr(args))
>          try:
>              self.cursor.execute(this, args)
>          except _mysql_exceptions.Warning, msg:
>              log.it('Mysql Warning: ' + str(msg))
>          except _mysql_exceptions.OperationalError, msg:
>              print "Are you using the right data base?  Try changing 
> /b/local/doorman/configure.py TESTING = 0"
>              raise
> 
> 
> Yes, Danny is right that the database name must be hard-coded:
> 
> 
>      s = 'update doorman set status = "%s" where in_id = %s and out_address like 
> "%%%s%%"'
>      my_connection.execute_mysql(s, new_status, inside_id, out_address)
> 
> 
> Doing that eliminates the traceback.  But the update doesn't happen
> and in mysql's log I see:
> 
> update doorman set status = "'MOVED'" where in_id = '60' and
> out_address like "%'courier-imap-admin at lists.sourceforge.net'%";
> 
> So I still have too many 's.
> 
> Here I try Lloyd's idea and take all the quotes out of my format:
> 
>      s = 'update doorman set status = %s where in_id = %d and out_address like 
> %%%s%%'
>      my_connection.execute_mysql(s, new_status, int(inside_id), out_address)
> 
> 
> inside_id is "60", as a string.  I figured I have better luck passing
> int(inside_id). But:
> 
>    File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in 
> defaulterrorhandler
>      raise errorclass, errorvalue
> TypeError: int argument required
> 
> That sure stumps me.
> 
> So I try %s:
> 
>      s = 'update doorman set status = %s where in_id = %s and out_address like 
> %%%s%%'
>      my_connection.execute_mysql(s, new_status, inside_id, out_address)
> 
> Gets me:
> 
> _mysql_exceptions.ProgrammingError: (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
> '%'courier-imap-admin at lists.sourceforge.net'%' at line 1")
> 
> Maybe it has something to do with unpacking the tuple?  So I give a
> fancy version a try, back to fiddling with escapes:
> 
>      def execute_mysql(self, this, *args):
>          caller = 'self.cursor.execute(\"%s\"' % this
>          for each in args:
>              caller += ", " + each
>          caller += ')'
>          print caller
>          try:
>              exec(caller)
> #            self.cursor.execute(this, args)
>          except _mysql_exceptions.Warning, msg:
>              log.it('Mysql Warning: ' + str(msg))
>          except _mysql_exceptions.OperationalError, msg:
>              print "Are you using the right data base?  Try changing 
> /b/local/doorman/configure.py TESTING = 0"
>              raise
> 
> and I go back to this call, afterall, it has "%s", %s, and "%%%s%%",
> trying everything:
> 
>      s = 'update doorman set status = "%s" where in_id = %s and out_address like 
> "%%%s%%"'
>      my_connection.execute_mysql(s, new_status, inside_id, out_address)
> 
> 
>      self.cursor.execute("update doorman set status = "%s" where in_id = %s and 
> out_address like "%%%s%%"", MOVED, 60, courier-imap-admin at lists.sourceforge.net)
>                                                                                                  ^
> SyntaxError: invalid syntax
> 
> 
> I don't know.  This is very hard!
> 
> Maybe I should use MySQLdb.escape_string on my addresses and be happy
> with that?  Maybe this is why it's not documented, maybe it's not so
> robust.
> 
> Thank you for your help and thought.
> 
> Marilyn
> 
-- 
Lloyd Kvam
Venix Corp