String substitution VS proper mysql escaping

MRAB python at mrabarnett.plus.com
Mon Aug 30 04:48:34 CEST 2010


On 30/08/2010 03:33, Nik the Greek wrote:
> On 30 Αύγ, 05:04, MRAB<pyt... at mrabarnett.plus.com>  wrote:
>
> when iam trying to pass a tuple to the execute methos should i pass it
> like this?
>
> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
> date = %s and host = %s ''' % (page, date, host) )
>
>
> or like
>
> tuple = (page, host, date)
>
> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
> date = %s and host = %s ''' % (tuple) )
>
>
> Or is it the same thing?
>
'tuple' is the name of a built-in. Don't use it.

The first example is clearer.

>>> =========================
>>> I'm asking this to see why
>>
>>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
>>> date = '%s' and host = '%s' ''' % (page, date, host) )
>>
>>> does work, while same thign qithout the quotes
>>
>>> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
>>> = %s and host = %s ''' % (page, date, host) )
>>
>>> doesn't. Dont know why but quotes somehopw confuse me both in strings
>>> and sql_queries as well when it comes to substitutions.
>>
>> Don't quote the placeholders yourself. Let the method do it.
>
> No, iam taking substitution here not mysql escaping.
>
> Cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
> date = '%s' and host = '%s' ''' % (page, date, host) )
>
> As it is above it works , with double quotes still works but if i
> leave it unquoted it doesn't.
>
> This is because without sigle or double quotes the the method doesn't
> know where a value begins and here it ends? That why it needs quoting?

Let the method do the substitution:

cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date = 
%s and host = %s ''', (page, date, host) )

This is the best way.



More information about the Python-list mailing list