Sqlite3. Substitution of names in query.

[Carsten Haese]

Lawrence D'Oliveiro wrote:
>> On what grounds are you asserting that it's not necessary to mix the
>> two? Please elaborate your point.
> 
> On the grounds that Python has more general and powerful string parameter-
> substitution mechanisms than anything built into any database API.

That statement is fundamentally flawed. You are assuming that the
preferred way of getting a value into a query is by substituting a
literal into the query string. That is, in general, not true, because
that would be horribly inefficient. This is also why I despise the term
"parameter substitution", since it implies incorrectly that passing
parameters to a query is merely a string formatting exercise. The
correct term is "parameter binding."

Most databases actually provide an API for supplying parameters
separately from the query string. This is more efficient, because it
eliminates the need to render the parameter value into a literal form on
the client side and to parse the literal form on the server side. Also,
it allows the engine to perform the same query multiple times with
different values without having to re-parse the query.

Finally, you're assuming that every value that can be supplied to a
query actually HAS a literal form. That is not true. For example, in
Informix databases, there are no literals for BYTE-type values. (You'd
probably call them blobs.) So, if vomiting literals into the query
string were your only way of conveying values to the database, you'd
never be able to populate a BYTE column on an Informix database. The
only way to pass a BYTE value to an Informix database is by parameter
binding.

Since parameter binding is in general much more than string
substitution, it is indeed necessary to mix the two.

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