Another MySQL Images Question

Steve Holden steve at holdenweb.com
Sun Apr 20 16:06:41 CEST 2008


Dennis Lee Bieber wrote:
> On Sat, 19 Apr 2008 03:46:54 +0200, Karl-Heinz Ruskowski
> <kayvoo at googlemail.com> declaimed the following in comp.lang.python:
> 
>> Hi, 
>>
>>>       cursor.execute('update products set pic1="%s" where id="%s", ;',
>>> (pic1, id))
>> Shouldn't it be something like 
>> cursor.execute('update products set pic1="%s" where id="%s", ;' % (pic1, id))
> 
> 	It should be NEITHER...
> 
> 	The latter is relying on Python to fill in the parameters. The
> former is relying (preferred) for the DB-API adapter to correctly format
> the parameters (It is a coincidence that MySQLdb internally uses Python
> % formatting, so the place holders are %s, where others use ?)
> 
> 	To be fully compliant it should be
> 
> 	cursor.execute("update products set pic1=%s where id=%s",
> 					(pic1, id))
> 
> NOTE: no trailing ,
> 		no trailing ;
> 		no embedded "		-- the DB-API spec is that IT will properly
> supply whatever quotes or escapes are needed to ensure the data
> fits the syntax. This is also why most will not function if one tries to
> make the table or field names parameters -- the DB-API will quote them
> too, and
> 
> 	update "products" set "pic1" = "something"
> 
> is much different from
> 
> 	update products set pic1="something"
> 

Many versions of SQL do actually allow you to quote table names, 
allowing names with otherwise illegal characters like spaces in them. 
Double-quotes are typically used for such quoting (though Microsoft, to 
be different, tends to use brackets).

Single-quotes should be used for string literals, although certain 
sloppy implementations of SQL do also allow double-quotes.

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/




More information about the Python-list mailing list