[DB-SIG] paramstyles, again

[Carsten Haese]

On Tue, 2007-05-22 at 11:24 -0400, Art Protin wrote:
> This seems like the lead in to suggesting that the API be extended to
> separate
> query specification from query execution.  I currently have query
> objects
> being created and handled transparently by the cursor objects.  I
> suspect
> that a better interface would allow the query object be explicitly
> created
> and then paramstyle would be an attribute of the query that could be
> controlled both explicitly and thread safely.  (I use the query string
> as
> key for looking up the query object so as be able to reuse the
> preparation
> work required by each query.)

It doesn't seem to make sense to allow the user to control the parameter
style after your internal query object is created, since the query
represents the statement that it executes, and a different parameter
style would require a different statement.

The most straightforward solution is to add an optional keyword
parameter to execute(). You can store that on your query object if you'd
like, but if the user wants to execute the query with a different
parameter style, they're going to have to call execute() again anyway.

> What additional benefits would a separate query class provide?

None that I can see.

> What liabilities would it create?

Clutter.

> > * Deprecate/disallow pyformat/format paramstyles.
> > 
> >   
> I can not get my boss to adequately describe why he dislikes these
> parameter styles.
> Can you offer up a rationale to help me see a reason to eschew
> pyformat and format?

1) They require literal percent signs to be escaped as %%.

2) They imply that parameter passing is a string formatting exercise,
which is only true in the dumbest of database implementations. Also, the
subtle difference between

curs.execute("insert into tab values(%s,%s)" % (1,2) )  #WRONG!

and

curs.execute("insert into tab values(%s,%s)", (1,2) )  #CORRECT!

makes it hard for newbies and pros alike to recognize the difference
between string formatting and parameter passing, and a lot of bad code
has been written as a result. Using question marks makes it immediately
obvious that something special is going on.

> Make qmark, numeric, and named all required. It does not take much
> Python
> code to adjust between them (to be able to implement any one in terms
> of any
> other) .  Then maybe SQL will be modivated to get to numeric.  Why let
> them
> bring us down to the least common denominator?

-1. It may not have taken much to implement on your backend, but that
may not be universally true. Even if "not much" code is required, the
amount is greater than zero, for no obvious benefit. Even requiring
qmark may require non-trivial code additions to some existing API
modules, but I think the effort would be justified. Requiring numeric
and named as well just adds a gratuitous implementation hurdle, and it
would seriously hurt the acceptability of this API change.

Best regards,

-- 
Carsten Haese
http://informixdb.sourceforge.net