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) ) [EMAIL PROTECTED] python]$ python -mtimeit -s "from querytest import Tester; t=Tester()" 't.with_params()' 10000 loops, best of 3: 146 usec per loop [EMAIL PROTECTED] 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 -- http://mail.python.org/mailman/listinfo/python-list