SQL problem in python

[Carsten Haese]

On Sat, 2008-03-08 at 10:11 -0800, Dennis Lee Bieber wrote:
> On Sat, 8 Mar 2008 09:28:23 -0800 (PST), aiwarrior
> <zubeido at yahoo.com.br> declaimed the following in comp.lang.python:
> 
> > Thanks a lot.
> > In the Python documentation, the sqlite module documentation doesn't
> > mention that special rule. I really thought that every variable to be
> > included in a query had to use that special method.
> >
> 	It's not sqlite specific -- it is common to all of the db-api
> compatible adapters. 
> 
> 	The explanation for why you were getting 'filename' (or whatever the
> field was is: parameter substitution ensures that the parameter value is
> properly quoted and escaped to be safe for use as a value.

It's called "parameter binding", not "parameter substitution".
Parameter binding ensures that the value is passed to the database
safely. That need not necessarily be a quoting-and-escaping exercise.
Depending on the API module, it may use a mechanism that transmits the
query string and the parameters to the database engine separately.

Also note that SQL standard doesn't even allow parameters in the
projection clause of a select statement. SQLite is just more relaxed
about the standard than for example Informix:

>>> import informixdb
>>> conn = informixdb.connect("stores_demo")
>>> cur = conn.cursor()
>>> cur.execute("select ? from customer", ("customer_num",) )
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_informixdb.ProgrammingError: SQLCODE -201 in PREPARE: 
42000: Syntax error or access violation

-- 
Carsten Haese
http://informixdb.sourceforge.net