MySQLdb select

[Andy Todd]

Peter Abel wrote:
> John Fabiani <jfabiani at yolo.com> wrote in message news:<9RPOc.2792$LI7.1058 at newssvr27.news.prodigy.com>...
> 
>>thanks that worked but I don't understand why.
>>cursor.execute("SELECT * FROM mytest where address = %s",string1)
>>above works but  - following your suggestion:
>>cursor.execute("SELECT * FROM mytest where address = %s" % string1)
>>above does not work.  So why did 
>> cursor.execute("SELECT * FROM mytest where clientID = %d" % numb)
>>work???????????????
>>john
>>F. GEIGER wrote:
>>
> 
> 
>>>>numb=10
>>>>"SELECT * FROM mytest where clientID = %d",numb
> 
> ('SELECT * FROM mytest where clientID = %d', 10)
> 
> The result of the above is a tuple, where the first value is a string
> and the second one is an int.
> So you call cursor.execute (..) with a two values. The first value is
> 'SELECT * FROM mytest where clientID = %d' which raises the
> exception because %d is never an int.
> What you wanted to do is to call cursor.execute (..) with one parameter,
> which is a string. So as ohers pointed out you have to use Python's
> formatting features to get the desired string:
> 
> 
>>>>"SELECT * FROM mytest where clientID = %d"  % numb
> 
> 'SELECT * FROM mytest where clientID = 10'
> 
> Even if your first example works from the point of view of SQL
> it wouldn't give you the results, you want.
> 
> 
>>>>string1='18 Tadlock Place'
>>>>"SELECT * FROM mytest where address = %s",string1
> 
> ('SELECT * FROM mytest where address = %s', '18 Tadlock Place')
> 
> That means again, your'e calling cursor.execute (..) with two parameters
> where the first one - again - is 'SELECT * FROM mytest where address = %s'
> So youre SQL-result should be empty, cause you surely don't have an adress
> with the value %s.
> 
> So formatting it the right way and adding single quotmarks araound string1
> will give you the result you want:
> 
> 
>>>>"SELECT * FROM mytest where address = '%s'" % string1
> 
> "SELECT * FROM mytest where address = '18 Tadlock Place'"
> 
> 
> Regards Peter
>  
> 
>>>"John Fabiani" ... [snip]

Peter,

Your first statement, explaining why the exception is raised, is correct.

Your second statement is false. Worse, misleading and possibly 
dangerous. In the MySQLdb driver (as I stated in my response to the 
original poster) the %s simply acts as a marker in the SQL string to 
instruct the database module to place the next parameter in that part of 
the statement, thus

 >>> cursor.execute("SELECT * FROM mytest WHERE address=%s", string1)

is a different statement to

 >>> cursor.execute("SELECT * FROM mytest WHERE address='%s'" % string1)

The first one uses database parameter passing (and will cope with *any* 
type of object - although it will probably only work with strings, 
numbers and dates) whereas the second mucks about with the string before 
it is passed to the database module.

You should always do the first. This allows the database module to 
handle type conversion, character encoding and a plethora of other nasty 
issues which you as the application programmer really, *really* don't 
want to know about.

In databases where parameter binding is properly supported (for instance 
Oracle) using the first form will also make your code more efficient 
(removing the parsing overhead if you must know).

The point to note here is that the MySQLdb database module uses %s not 
for string interpolation but for parameter passing. Other database 
modules use ? (for instance mxODBC) and others use named or numbered 
parameters (e.g. :param1 or :1).

Not that it's vitally important, but the first statement I showed above 
is more correctly written as;

 >>> cursor.execute("SELECT * FROM mytest WHERE address=%s", (string1,))

Because the second argument to the 'execute' method is a tuple of 
parameter values to be substituted in the first argument - the SQL 
statement.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/