MySQLdb select

[Raaijmakers, Vincent (GE Infrastructure)]

I'm so glad that this issue is raised here.
Can someone help me in this thread explaining me how to use multiple parameters.
Jaco helped me already a lot and perhaps someone in this thread can give me the final answer:

My table contains a blob and a bigint for storing images. The table name is also a variable, so the query looks like:
SQL = "INSERT INTO %s (number, image) VALUES (%s, %s)"

Well, I get a mysql exception if the execution looks like:
cursor.execute(SQL, (tableName, aValue, jpgImage))

The execution only works when I fill out the data until the blob value is a 'left over' as a %s:
Yes, this looks ugly.. but it works...

SQL = "INSERT INTO %s (number, image) VALUES (%s," % (tableName, aValue) ## table name and number
SQL += "%s)" ## left over... only the image
cursor.execute(SQL, jpgImage)

Can someone explain this to me?

Vincent



-----Original Message-----
From: python-list-bounces+vincent.raaijmakers=ge.com at python.org
[mailto:python-list-bounces+vincent.raaijmakers=ge.com at python.org]On
Behalf Of Harald Massa
Sent: Sunday, August 01, 2004 5:41 PM
To: python-list at python.org
Subject: Re: MySQLdb select


Ruben,

it is considered bad style wihtin c.l.p. to have aggressive emotions 
apart from mails about a "ternary operator"

The correct way would have been:

> > cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
>>
>> That's particularly BAD STYLE. It's best to keep to letting the
>> DB-API do the proper quoting for all parameters.

"Gerhard, why is this BAD STYLE? And how can I make the DB-API do the 
proper quoting for all parameters?"


And probably the answer would have been:

with doing 

"SELECT * FROM mytest where clientID = %d" % numb 

you are using the standard Python String-Formatting Functions. That may 
or may not lead to success.

Especially when using a mixture of strings and numbers, they have to be 
quoted differently.

Also special characters within strings have to be quoted - for example, 
the single ' has to be quoted within SQL because it is usually used as 
string delimiter

cursor.execute("SELECT * FROM mytest where clientID = %d" , numb)

The "," is the magic -

with "Bla " % sth you are passing ONE argument --- the %s gets 
substituted, and one argument gets passed to the function.

With "," two arguments get passed to the execute-"function" so that the 
DB-API has the challenge to integrate the parameters into the SQL-String.

And it is more bad style to write

"SELECT * FROM mytest where clientID = %d" % numb 

because % is defined with using a Tuple as second parameter, that would 
be
"clientID = %d" % (numb,)

Using % with sth. else than a tuple may succeed or give you very funny 
errors if numb happens to be a List in a later run.

Best wishes,

Harald
-- 
http://mail.python.org/mailman/listinfo/python-list