[DB-SIG] paramstyles, again

[Art Protin]

Dear folks,
    Carsten Haese wrote:

>On Wed, 2007-05-30 at 22:53 -0400, Michael Bayer wrote:
>  
>
>>On May 30, 2007, at 11:17 AM, Vern Cole wrote:
>>
>>    
>>
>>>c.execute("SELECT (CASE WHEN infos.pk <", p1,
>>>           "THEN", p2,
>>>           "WHEN (infos.pk >=", p3,
>>>           " AND infos.pk <", p4,
>>>           ")THEN", p5,
>>>           "END) AS x, infos.pk, infos.info FROM infos")
>>>###^^^
>>>
>>>I think that most people would find the function parameter notation  
>>>much easier to read, write, and maintain than the qmark version.  
>>>There is no question what parameter goes where, no counting, no  
>>>wondering how python or your reader will interpret it.  And it will  
>>>look like the built-in python 3 print() function. ;-)
>>>
>>>      
>>>
>>seriously ?  how would it differentiate a string value that is part  
>>of the generated SQL vs. a string value that is intended to be a bind  
>>parameter ?
>>    
>>
>
>Differentiating parameters from query bits is easy, they alternate, but
>I vote -1 on Vern's proposal for all the other reasons you mentioned.
>
>The same readability of injecting variables into an SQL query can
>already be achieved with named parameters and locals():
>
>c.execute("""SELECT (CASE
>             WHEN infos.pk < :p1 THEN :p2
>             WHEN (infos.pk >= :p3 AND infos.pk < :p4) THEN :p5
>             END) AS x, infos.pk, infos.info FROM infos""", locals() )
>
>  
>
>>my vote for paramstyles would be, everyone supports qmark and named,  
>>and we're done.  the rest of the styles are all redundant.
>>    
>>
>
>That makes +3 votes for making qmark mandatory (you, Marc-Andre, and
>myself). I'm not sure what your stance is on format, pyformat, and
>numeric. Are you allowing them optionally or are you proposing that they
>be deprecated/removed? I would vote -1 on completely removing numeric
>because I don't think it's redundant.
>
>  
>
I guess I am expected to weigh in and make perfectly clear where I stand.

I disapprove of (-1) making qmark mandatory and exclusive.

I would approve (+1) dropping the two formatted styles (format & pyformat).

I would favor (+1) adding a switching requirement.

I do strongly favor (+1) requiring support for all acceptable formats 
(any form
not required is forbidden).  Our users should not have to support all the
different forms just so our implementations don't have to!!!!

I do favor (+1) making named and/or numeric required.

Without a switching requirement, I favor dropping qmark as too 
"impoverished" a
form.  (The commonness of qmark in SQL only reflects badly on SQL.)


As for a switching requirement: how does this sound (I just though of it 
this morning)
making the parameterstyle depend on the first character of the SQL 
statement.
If it is a colon, remove it and the parameter style is either numeric or 
named; if it is
not a colon, the parameter style is qmark.  Numeric and named are only 
different
in that with numeric all the keys are numbers, and thus a list of 
parameters could be
provided instead of a dictionary of parameters.  It is very easy to 
programatically
distinguish between numeric and named, and both can be simultaneuosly 
supported.

I also think that the specification should include fragments of code to 
show how to
convert named to numeric and how to convert numeric to qmark (all in 
Python of
course).  Then no one should complain about having to support named and 
numeric.

>I personally like named style, but I'm +0 on making it required. If it
>were required, you'd have to specify how the API is expected to
>differentiate between qmark and named. Do you expect the API to
>auto-detect the parameter style from the query string, or do you expect
>some kind of switching mechanism?
>Best regards,
>  
>
    Thank you all,
    Art Protin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20070531/8d15f342/attachment.htm