[DB-SIG] In praise of pyformat

[Paul Boddie]

On Saturday 11 August 2007 10:25, Mike Meyer wrote:
>
> Well, InformixDB has a way that works. I can think of at least two
> other databases that it won't work for - or at least, will have
> surprising consequences for users. That's not the same thing as the
> paramstyle having a way.

Can you name those database systems? The DB-API should support parameters 
without additional quoting. If, say, PostgreSQL doesn't support parameters 
internally, the module should prevent the programmer from knowing about that.

[...]

> I do agree that the module authors ought to have to deal with this
> rather than the users. But letting each module author do it however
> they feel is best only helps users who are writing for a specific
> module; users trying to write code that's portable between modules
> and/or databases are better served by a specification that applies to
> all modules, even if it adds the burden of flagging significant
> characters as literal.

As you may have seen, I wrote some code which attempts to mark parts of 
queries as literal and non-literal text:

http://www.python.org/pypi/sqlliterals

This should help module developers know whether a parameter marker is genuine 
or not.

> If a new version of the PEP is going to require supporting a parameter
> style - which I believe is a good thing - it should be one that's at
> least as explicit as the most explicit of the current parameter
> styles. Which means the PEP needs to lay out the rules for when the
> parameters are recognized as such and when they aren't.

You previously asked the following question: "How do the other paramstyles 
deal with wanting to get their significant character into the query?" The 
answer is that at the application level you never have the problem of 
getting, for example, a question mark into the query: it's either inside a 
string literal, meaning that it's protected from any interpretation as a 
parameter marker (1), or it's supplied as part of a value which is passed to 
the database system in association with a parameter (2). Examples:

# (1)
cursor.execute("select name from addresses where status = '?' order by name")

# (2)
cursor.execute("select name from addresses where status = ? order by name", 
("?",))

And where a question mark (or other parameter marker) appears outside a string 
literal, it's unambiguously interpreted as a parameter marker since there 
should be no other interpretation of that character outside literals. Of 
course, choosing a character sequence which may plausibly be part of some 
other syntactic feature would defeat such simple measures for knowing whether 
that sequence was a parameter marker or not, but the question mark should be 
a satisfactory choice according to the standard.

If one considers parameters as locations for string substitution, then I can 
see how your question arises, but they are not mere substitutions, even if a 
database module or system might use that mechanism to achieve the effect (due 
to a lack of "proper" parameter support internally). The pyformat paramstyle 
is confusing because it's used to achieve something that resembles the effect 
of another mechanism - string substitution - but is actually somewhat 
different (as the above explanation attempts to make clear). Meanwhile, some 
of the other paramstyles are legitimate standards that can be seen in a 
number of widespread technologies.

Paul