On Fri, 2007-08-10 at 16:11 -0400, Mike Meyer wrote: > Maybe this is late, and the issues are already settled, but it seemed > that nobody spoke up for them, so I figured I ought to. > > There are two downsides given for the format and pyformat > paramstyles. They are: > > 1) It confuses newbies, and they wind up building queries in Python > instead of using parameters. > > 2) You have to use '%%' to get a real '%' into the query. > > #1 is a bad thing. [...] > > As for #2, yes, you have to use '%%' to insert a single '%'. How do > the other paramstyles deal with wanting to get their significant > character into the query? From reading the last few months of > archives, it seems that they don't.
Actually, they do (or at least they should, and InformixDB certainly does). SQL already has a standard way of treating parameter markers as literals: Apostrophes. A question mark is a literal question mark if and only if it occurs inside apostrophes. Otherwise, it's a parameter marker. The same principle is true for colon-identifier tokens. The same principle would also apply to percent signs, but somehow it didn't occur to anybody to require (py)format modules authors to make accommodations for percent signs appearing inside literal strings, instead placing the burden on application developers. > The db module author either > expects that that won't happen, or is expected to recognize those > characters in some db-dependent way. It does happen. For example, Informix has a "matches" operator that uses literal question marks as wild cards for single characters, similar to glob matching. Hence, both select * from persons where name matches '?' and select * from persons where name matches ? are valid queries with very different meanings. In the former, any single-character name is matched, and the question mark is not a parameter marker. In the latter, the name is matched to whatever pattern is given as the parameter. > If the goal is to be able to > write code that will port between db modules - or even databases - > without modification, then having a defined way to deal with this > issue is clearly better than punting to the module authors. After all, > it's the latter that created the current situation. As I said, there is a defined way: Don't treat things that look like parameter markers as parameter markers if they appear inside apostrophes. This may require a simple parser in the API module, but I prefer placing a burden on a dozen API module authors over placing a burden on thousands of application developers. -- Carsten Haese http://informixdb.sourceforge.net _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
