Another MySQL Problem

[Tim Chase]

On 06/23/2010 08:45 AM, Victor Subervi wrote:
> Hi;
> I have this line:
>
>    cursor.execute('select clientEmail from clients where client=%s',
> (string.replace(client, '_', ' ')))
>    clientEmail = cursor.fetchone()[0]
>    cursor.execute('select * from %s' % (client))
>
> client = "Lincoln_Properties"
> With the replacement, the interpreter complains that mydatabase.Lincoln
> doesn't exist. Therefore, the first line of code isn't putting the %s
> replacement in quotes, as I was told by you all it would. So I add quotes to
> it and the interpreter complains on the second line of code that it's
> unsubscriptable (because it's None). What gives?

First, you don't specify which .execute() is throwing the 
exception.  My guess is the 2nd.  However, a little side-tour:

---[side tour]------------------

Well, while I'm not sure whether it's the root of your problem, 
notice that the parameters should be

   cursor.execute(sql, tuple_of_parameters)

Notice the difference between

   print type((string.replace(client, '_', ' '))) # string
   print type((string.replace(client, '_', ' '),)) # tuple

returns...hint, what you have is not a single-element tuple as 
execute() expects.  Once you're passing an expected tuple, it 
should properly escape it.  You might be getting a peculiar 
side-effect of MySQL's DB-escaping (that string-formatting with a 
non-tuple parameter acts like a one-element-tuple).  However, 
internally, coercion-to-tuple may be happening (as it may well on 
a different DB backend), so you might be getting something like

   tuple(replaced_string)

which gives you something weird like

   ('L', 'i', 'n', ...)

but if that was the case, I'd expect it would complain with a 
"TypeError: not all arguments converted during string formatting" 
unless the DB backend does smarter parsing of the parameters.

Additionally, using module version of string.replace() has been 
deprecated in favor of just using the method on available on all 
strings:

   client.replace("_", " ")

---[end side tour]--------------

Verify that the value for "client" has the underscore in it 
before you do your 2nd execute -- if the exception is what I 
think it is, it sounds like you're creating the SQL with the 
wrong client string.  Verify it by pulling it out as a parameter:

sql = 'select * from %s' % (client)
print repr(sql)
cursor.execute(sql)

and verify that your SQL isn't

   'select * from Lincoln Properties'

If your tablename does violate best practices by having a space 
in it, then you'd need to hand-escape with whatever means MySQL 
offers for escaping table/column/view names.  In SQL Server, it's

   [Lincoln Properties]

and in some others, I think it would be

   `Lincoln Properties`

However, it's generally considered bad-practice to have 
object-names (tables, columns, views, indexes, triggers, 
stored-procedures, etc) that have spaces in their names.

-tkc