Home Original page

String substitution VS proper mysql escaping

MRAB python at mrabarnett.plus.com
Sun Aug 29 22:04:32 EDT 2010 
On 30/08/2010 02:38, Νίκος wrote:
> On 29 Αύγ, 21:34, MRAB<pyt... at mrabarnett.plus.com>  wrote:
>
>> It likes the values to be in a tuple. If there's one value, that's a
>> 1-tuple: (page, ).
>
> I noticed that if we are dealing with just a single value 'page' will
> do, no need to tuple for 1-value.
> it handles fine as a string.
>
I tried it with sqlite3, which it didn't like it. For consistency, and
compatibility with other SQL engines, I recommend that you always
provide a tuple.

>>>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
>>>> date = %s and host = %s''', page, date, host)
>>
>>> or python will not allow it cause it might think there are 4 args
>>> isntead of two?
>>
>> Not Python (the language) as such, but the method. As I said, it
>> expects the value(s) to be in a tuple.
>
> If i dont parenthesize the execute method instead of getting 2
> args(sql_query and tuple value) as it expects by deficition, it gets 4
> args instead and thats why it fails? I need to know why ti fails. Is
> that it?
>
If the SQL query contains placeholder(s), the .execute method expects
the value(s) to be provided in a tuple. It's as simple as that.

> Also in here,
>
> page, date, host is 3 separate variable values here
>
> while
>
> (page, date, host) is 3 separate variables values also but withing a
> tuple. Is this correct?
>
It doesn't care about the variables as such, only their values. You're
putting the values into a tuple and then passing that tuple because
that's what the method wants.
>
>>>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
>>>> date = '%s' and host = '%s' ''', (page, date, host))
>>
>>> Whats happens if i attempt to also quote by single or double quoting
>>> the above although now i'm aware that .execute method does the quoting
>>> for me?
>>
>> The method will put in any quoting that's needed. If you also put in
>> quotes then that'll result in 2 sets of quoting, one inside the other
>> (or something like that).
>>
>> Why make more work for yourself? Let the method do it for you, safely
>> and correctly!
>
> I'am askign this because i'm tryong to see why
>
> On 29 Αύγ, 21:34, MRAB<pyt... at mrabarnett.plus.com>  wrote:
>
>> It likes the values to be in a tuple. If there's one value, that's a
>> 1-tuple: (page, ).
>
> I noticed that if we are dealing with just a single value 'page' will
> do, no need to tuple for 1-value.
> it handles fine as a string.
>
As I've said, for consistency I recommend that you always provide a
tuple because some SQL engines require it, and if you need to provide
multiple values then you'll need to anyway.

>>>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
>>>> date = %s and host = %s''', page, date, host)
>>
>>> or python will not allow it cause it might think there are 4 args
>>> isntead of two?
>>
>> Not Python (the language) as such, but the method. As I said, it
>> expects the value(s) to be in a tuple.
>
> If i dont parenthesize the execute method instead of getting 2
> args(sql_query and tuple value) as it expects by deficition, it gets 4
> args instead and thats why it fails? I need to know why ti fails. Is
> that it?
>
> ========================
> Also in here,
>
> page, date, host is 3 separate variable values here
>
> while
>
> (page, date, host) is 3 separate variables values also but withing a
> tuple. Is this correct?
>
>
> =========================
> 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.
More information about the Python-list mailing list