Prev: Instance method decorator garbage collection problem
Next: Parsing email
From: Tim Chase on 23 Jun 2010 12:22
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


From: John Nagle on 24 Jun 2010 02:26
On 6/23/2010 10:59 PM, Dennis Lee Bieber wrote:
> On Wed, 23 Jun 2010 11:58:24 -0430, Victor Subervi
> <victorsubervi(a)gmail.com> declaimed the following in
> gmane.comp.python.general:
>
>
>> When I have this code:
.....
>
> And yes -- it IS an error... You are AGAIN trying to use parameters
> for SCHEMA entities, not data.

Yes. Please post your CREATE statements, so we can see your
database schema. If you really have one table per client, you're
doing it wrong.

John Nagle

First  |  Prev  | 
Pages: 1 2
Prev: Instance method decorator garbage collection problem
Next: Parsing email