I wish that [].append(x) returned [x]

[Carsten Haese]

On Wed, 2007-05-02 at 13:45 -0800, Joshua J. Kugler wrote:
> On Wednesday 02 May 2007 12:05, Tobiah wrote:
> 
> > 
> >> In addition to the above good advice, in case you are submitting a query
> >> to a DB-API compliant SQL database, you should use query parameters
> >> instead of building the query with string substitution.
> > 
> > I tried that a long time ago, but I guess I found it to be
> > more awkward.  I imagine that it is quite a bit faster that way?
> > I'm using MySQLdb.
> 
> The issue is not speed, it's security.  Query parameters are automatically
> escaped to prevent SQL injection attacks.

In addition to the important security factor, on many databases, using
query parameters will also result in a speed increase. It just so
happens that MySQLdb is not one of them.

The wording that query parameters are "escaped" implies that handling
query parameters is a string formatting exercise and that query
parameters are stuffed into the query string as properly escaped
literals. That is not always the case.

In many databases, the lowlevel database API provides a particular
mechanism for binding parameter values to placeholders without
"injecting" them into the query string. This saves the client from
constructing literals and it saves the server from parsing those
literals. It also allows the server to reuse the query string without
re-parsing it, because the query string doesn't change if the parameters
are transmitted separately.

The resulting speedup can be quite significant, as demonstrated for
example with an IBM Informix database:

# querytest.py
class Tester(object):
   def __init__(self):
      import informixdb
      conn = informixdb.connect("ifxtest")
      self.cur = conn.cursor()
      self.cur.execute("create temp table t1(a int, b int)")
      self.counter = 0
   def with_params(self):
      self.counter += 1
      self.cur.execute("insert into t1 values(?,?)",
                       (self.counter,self.counter*2) )
   def without_params(self):
      self.counter += 1
      self.cur.execute("insert into t1 values(%s,%s)" %
                       (self.counter,self.counter*2) )

[carsten at localhost python]$ python -mtimeit -s "from querytest import
Tester; t=Tester()" 't.with_params()'
10000 loops, best of 3: 146 usec per loop
[carsten at localhost python]$ python -mtimeit -s "from querytest import
Tester; t=Tester()" 't.without_params()'
1000 loops, best of 3: 410 usec per loop

I think those numbers speak for themselves.

-Carsten