[Tutor] mysql formatting

[Marilyn Davis]

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





--