[DB-SIG] pyformat Parameter Style

[Marcos Sánchez Provencio]

El mar, 13 de 05 de 2003 a las 11:31, M.-A. Lemburg escribió:
> Magnus Lyckå wrote:
> > At Sat, 10 May 2003 19:54:30 +0200, M.-A. Lemburg wrote:
> > 
> >>> On Saturday, May 10, 2003, at 10:23 US/Pacific, Andy Todd wrote:
> >>>
> >>>> Is there a definitive specification of the pyformat parameter style 
> >>>> anywhere?
> >>>>
> >>>> The usual sources come up dry. It is mentioned in the DB-API 
> >>>> specification (http://www.python.org/peps/pep-0249.html) but not 
> >>>> defined.
> >>>>
> >>>> It seems from observation that one should simply put '%(<parameter 
> >>>> name>)s' in the query string and then pass a mapping with a 
> >>>> corresponding key when calling the execute method. But I'd just like 
> >>>> to make absolutely sure.
> >>>>
> >>>> In particular, I'm curious if the 's' actually means anything (like 
> >>>> string?). Should I be using different characters for different data 
> >>>> types, and if so, which ones?
> >>
> >>
> >> The DB API says "Python extended format codes" meaning that all
> >> valid Python formatting codes (including their parameters) may
> >> be used.
> > 
> > I've heard repeatedly that only %s should be used. This is the
> > first time I've heard someone contradict this (however reasonable
> > it seems). Maybe this should be clarified in the spec? (No, it's
> > not clear if people misunderstand it reagrdless of what words in
> > the text you can point to. ;)
> 
> Looks like I ought to update the PEP then. Can you suggest
> a wording ?
> 
> > Is it just because of a misunderstanding that a number of drivers
> > get this wrong, or is there some reason to turn integers and strings
> > into floats.
> 
> You probably mean: floats into strings.
> 
> I think the reason is that those drivers don't support binding
> in the sense that the SQL statement and the data are sent to
> the database as two separate entities.
> 
> However, I don't understand why the current implementations
> don't use Python's wealth of format characters (and it's formatting
> implementation).
> 
> That said, I still think that positional binding is by far
> the easiest to use and understand way of binding variables.

I see your point, but if you have to use your parameters several times
in your sql statement it is a little awkward. For example, an optional
filter parameter (imagine that with a combination of four or five
parameters...):

select * from xx 
where (yy=[param] or [param] is null) 
and zz=[param2]

I always thought this should be encapsulated in a stored procedure until
I had to use Oracle. Oracle needs writing more than a dozen lines (not
the intuitive kind, if I may say) to cram that into a stored proc.