In message <[EMAIL PROTECTED]>, Sybren Stuvel
wrote:
> Tim Chase enlightened us with:
>> >>> cur.execute("select * from people where last_name in (%s)" %
>> ','.join('%s' for i in xrange(len(t))),
>> t)
>
> But since the value of 'i' isn't used at all, it can be abbreviated
> to:
>
>>>> cur.execute("select * from people where last_name in (%s)" %
> ','.join('%s' for i in t),
> t)
Stuff this. Here are some new functions, separating the escaping of specials
from that of wildcards:
def SQLString(Str) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good
enough."""
Result = []
for Ch in str(Str) :
if Ch == "\0" :
Ch = "\\0"
elif Ch == "\010" :
Ch = "\\b"
elif Ch == "\011" :
Ch = "\\t"
elif Ch == "\012" :
Ch = "\\n"
elif Ch == "\015" :
Ch = "\\r"
elif Ch == "\032" :
Ch = "\\z"
elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
#end if
Result.append(Ch)
#end for
return "\"" + "".join(Result) + "\""
#end SQLString
def EscapeSQLWild(Str) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append("\\")
#end if
Result.append(Ch)
#end for
return "".join(Result)
#end EscapeSQLWild
So to turn the result from EscapeSQLWild into a string, you still need to
wrap it in SQLString.
And here is the corresponding replacement for my previous QuoteSQLList
routine:
def SQLStringList(TheList) :
"""returns a MySQL list containing the items of TheList, suitable
for use in an "in" clause."""
return \
"(" + ", ".join([SQLString(Str) for Str in TheList]) + ")"
#end SQLStringList
So Tim Chase's example above now becomes:
cur.execute("select * from people where last_name in %s" %
SQLStringList(t))
Much simpler, isn't it?
And there are other, more dynamic cases where explicit quoting using
SQLString is easier than trying to make do with autoquoting.
--
http://mail.python.org/mailman/listinfo/python-list
