[DB-SIG] pyformat Parameter Style

Magnus Lyckå magnus at thinkware.se
Tue May 13 15:02:22 EDT 2003

At 11:31 2003-05-13 +0200, M.-A. Lemburg wrote:
>Looks like I ought to update the PEP then. Can you suggest
>a wording ?

I can try! :)


             String constant stating the type of parameter marker
             formatting expected by the interface. Possible values are
                 'qmark'         Question mark style
                 'numeric'       Numeric, positional style
                 'named'         Named style
                 'format'        All ANSI C printf format codes
                 'pyformat'      All Python extended format codes

              See .execute below for examples.

IIRC there has also been discussions on whether
execute("... x=%i AND y=%s", 1, 2)
is correct, or whether it should be
execute("... x=%i AND y=%s", (1, 2))
or perhaps either.

I think a clarification on execute is good as well, and honestly,
now that I look at it, I feel uncertain, but I assume it should
be just .execute(s, p), where p is a sequence or a mapping, i.e.
the second example above. From a BNF etc p.o.v. I guess "parameters"
is *one* argument, containing all the parameters for the SQL operation.


             Prepare and execute a database operation (query or
             command).  Parameters may be provided as sequence or
             mapping and will be bound to variables in the operation.
             Variables are specified in a database-specific notation
             (see the module's paramstyle attribute for details). [5]


             Return values are not defined.

             Code examples with different paramstyles:

              sql = 'SELECT * FROM table WHERE age>=? AND name=?'
              cur.execute(sql, (15, 'Guido'))

              sql = 'SELECT * FROM table WHERE age>=:1 AND name=:2'
              cur.execute(sql, (15, 'Guido'))

              sql = 'SELECT * FROM table WHERE age>=:min_age AND name=:name'
              cur.execute(sql, {'min_age': 15, 'name': 'Guido'})

              sql = 'SELECT * FROM table WHERE age>=%d AND name=%s'
              cur.execute(sql, (15, 'Guido'))

              sql = 'SELECT * FROM table WHERE age>%(min_age)d AND 
              cur.execute(sql, {'min_age': 15, 'name': 'Guido'})

As far as I understand,
              sql = 'SELECT * FROM table WHERE age>=:2 AND name=:1'
              cur.execute(sql, (15, 'Guido'))
will do exactly the same thing as the example above, and not the same
thing as
              sql = 'SELECT * FROM table WHERE age>=:2 AND name=:1'
              cur.execute(sql, ('Guido', 15))
which is what I had expected. I don't know if that should be mentioned in the

>You probably mean: floats into strings.

You are probably right. :)

>I think the reason is that those drivers don't support binding
>in the sense that the SQL statement and the data are sent to
>the database as two separate entities.

Right. They perform the "SQL % parameters" in Python after turning
all parameters into strings, and that's where the exceptions come
from. At least for pysqlite and the related PostgreSQL driver.

>However, I don't understand why the current implementations
>don't use Python's wealth of format characters (and it's formatting

Right. It's as simple as to not do anything with numeric types,
rather than to call repr() on them. And that will still work
if people write %s in their SQL even for numeric values.

Actually, using repr() on a float will probably lead to a
few surprises that %f or %s on the actual float prevents:
 >>> "%s %f %s" % (0.1, 0.1, repr(0.1))
'0.1 0.100000 0.10000000000000001'

The backend might well be able to store an exact decimal
representation, and then it's a bit silly to store the
Python float approximation.

>That said, I still think that positional binding is by far
>the easiest to use and understand way of binding variables.

A mapping is obviously useful if we have the same value
several times in the SQL statement.

Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program 

More information about the DB-SIG mailing list