[DB-SIG] pyformat Parameter Style

[Magnus Lyckå]

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! :)

----
         paramstyle

             String constant stating the type of parameter marker
             formatting expected by the interface. Possible values are
             [2]:
                 '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.

----
         .execute(operation[,parameters])

             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:

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

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

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

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

             'pyformat':
              sql = 'SELECT * FROM table WHERE age>%(min_age)d AND 
name=%(name)'
              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
spec.

>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
>implementation).

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