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

[Tobiah]

I wanted to do:

query = "query text" % tuple(rec[1:-1].append(extra))

but the append() method returns none, so I did this:

fields = rec[1:-1]
fields.append(extra)
query = "query text" % tuple(fields)

 

[Rob Wolfe]

I wanted to do:

query = "query text" % tuple(rec[1:-1].append(extra))

but the append() method returns none, so I did this:

fields = rec[1:-1]
fields.append(extra)
query = "query text" % tuple(fields)

What about this?

query = "query text" % tuple(rec[1:-1] + [extra])

 

[Paul McGuire]

I wanted to do:

query = "query text" % tuple(rec[1:-1].append(extra))

but the append() method returns none, so I did this:

fields = rec[1:-1]
fields.append(extra)
query = "query text" % tuple(fields)

query = "query text" % tuple(rec[1:-1] + [extra])

should work.

-- Paul

 

[Carsten Haese]

I wanted to do:

query = "query text" % tuple(rec[1:-1].append(extra))

but the append() method returns none, so I did this:

fields = rec[1:-1]
fields.append(extra)
query = "query text" % tuple(fields)

query = "query text" % tuple(rec[1:-1] + [extra])

should work.

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.

If you aren't querying an SQL database, never mind.

-Carsten

 

[Michael Bentley]

I wanted to do:

query = "query text" % tuple()

but the append() method returns none, so I did this:

fields = rec[1:-1]
fields.append(extra)
query = "query text" % tuple(fields)

As you learned. .append() adds to an existing list rather than
returning a new list. You might be happier with:

query = "query text" % tuple(rec[1:-1] + [extra])

 

[Tobiah]

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.

 

[Joshua J. Kugler]

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.

j

 

[Carsten Haese]

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@localhost python]$ python -mtimeit -s "from querytest import
Tester; t=Tester()" 't.with_params()'
10000 loops, best of 3: 146 usec per loop
[carsten@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