[DB-SIG] Towards a single parameter style

[Matthew T. Kromer]

M.-A. Lemburg wrote:

>
> Now this would be hard to agree on and it will also break existing
> code, so I think we should just make the preferred paramstyle
> a suggestion in the DB-API. I'd vote for the simplest of them
> all: the 'qmark' style.


Qmark is the  style that's probably the hardest to cope with for 
binding, as well.  To do it fast you'd need a C extension just to rip 
the SQL apart to find non-escaped or quoted question marks.

The dictionary modes or string substitution modes would be easier to 
deal with, but still pose problems for purely positional parameter binds.

Consider the following:

   sql, args = transform( 'SELECT FROM FOO WHERE BLA=%(bla)s AND 
OOG=%(oog)s', {'bla': bla, 'oog': oog})

A simple translator might try:

    args = []
    c = dict.copy()
    for key, value in c.items():
        args.append(value)
        c[key] = '?'

    sql = statment % c
    return sql, args

Yet this would be dependant on the order of keys()!  It would 
conceivably alter the oder of BLA and OOG (in fact,  in my test case, I 
think it does).

Here's another example:  Oracle's positional parameter binds use a 
notation like :1, :2, :3 etc.  But as near as I can see, it doesn't 
actually CARE what the number is -- the first one to appear in the 
expression is 1, the second is 2, etc -- so an expression that is 
'SELECT FROM FOO WHERE BLA=:2 and OOG=:1' is identical to 'SELECT FROM 
FOO WHERE BLA=:1 and OOG=:2' -- the number in the format string is 
meaningless, only its position matters.  Fortunately, Oracle has named 
binds as well as positional binds -- but not all databases do.

As awful as it is, the style that's probably MOST univeral is "SELECT  * 
FROM FOO WHERE BLA=%s AND OOG=%s", (bla, oog) since this preserves the 
order of the parameters at the sake of losing their names (but synthetic 
names can be conjured for databases that need named binds).  A qmark or 
positional binder can easily substitute in '?' or ':1', ':2' etc and 
return the argument list as the bind parameters, and a named binder can 
substitute in ':p1', ':p2' etc and return a dictionary of {'p1': param1, 
...} as the argument vector.  The only case that isn't handled properly 
that I can see is those drivers that support NO binding at all -- but 
even those can guess at their format based on the type of the argument 
(ie quote strings but don't quote numbers).