[DB-SIG] format and pyformat redux

Chris Cogdon chris@cogdon.org
Wed, 21 Aug 2002 19:11:43 -0700

I thought I'd just recap the previous discussion on format vs pyformat, s=
I got a few responses privately. At the end, I'll also add a 'neat=20
suggestion' we can all champion or lampoon as appropriate.

I originally asked what where the advantages of pyformat over format. pyf=
seems to be the 'preferred' paramater passing mechanism, even though my o=
experiences with 'pyformat' was that it was more cumbersome than 'format'=

However, when the number of parameters increases, it becomes more and mor=
difficult to maintain sequencing with the 'format' method, given that a=20
change in parameters necessitates a change in the order the parameters ar=
passed to the 'execute' method. For example, with the following compariso=
it appears that 'format' is much simpler:

cur.execute ( "select * from people where name=3D%s and age>%s", name, ag=
e )
cur.execute ( "select * from people where name=3D%(name)s and age>%(age)s=

The shorter 'format' becomes more complex with, say, 10 or more parameter=
(example omitted, I'm sure it's obvious to you all).

Additionally, you can do some neat tricks with the 'pyformat' method. If =
your variables are in your local namespace already, you can pass the outp=
of 'vars'. Viz:

cur.execute ( "select * from people where name=3D%(name)s and age>%(age)s=
vars() )

In the cases where this is not the case, you can write a 'wrapper functio=
n' to=20
help you create the anonymous dictionary. Viz:

def execute2 ( cursor, format, **kwargs ):
    return cursor.execute ( format, kwargs )

execute2 ( cur, "select * from people where name=3D%(name)s and age>%(age=
name=3Dgetname(), age=3Dgetage() )

So, thanks to you all fine folks out there, I dont find 'pyformat' as=20
unattractive anymore.

I do have an interesting proposition, though. (Stop groaning, you ;)

Currenty given the case where the information to be passed to the query i=
s not=20
currently in any local variable. To get it in there, either a local varia=
needs to be created, and then the vars() function called to create the=20
dictionary, or a dictionary created to hold all the values. My suggestion=
another parameter passing format, and the opportunity to create a new gen=
function for Python, is as follows:

We create a function that treats the information inside the () of the %=20
operator (or the execute statement) as a full python expression, rather t=
just a key into a dictionary. Since the current % expansion operator uses=
parenthesis, it's perfectly reasonable to assume these are the surroundin=
parentesis of an expression, and evaluate the contents as a python=20
expression! Here's an example, assuming we have a new parameter passing=20
method called, say, pyexprformat (python expression format)

cur.execute ( "select * from people where name=3D%(name)s and age>%(age)s=
locals(), globals() )

Okay, so that's nothing new, but in this case, the stuff inside the=20
parenthesis is a python expression, and it's been told to evaluage in the=
specified namespaces, just like 'eval'. Now, assume that the two paramete=
need to be calculated, we could have:

cur.execute ( "select * from people where name=3D%(getname())s and=20
age>%(age())s", locals(), globals() )

I've shown an example of using a simple function call, but this could hav=
been any python expression.

To me, this seems to be very easy to set up. The syntax is clear, and I'm=
there's a hook into the interpretor that would stop parsing tokens when t=
valid end of expression is reached (same syntax as function calls), we ge=
the value of the expression, do type checking to find out what kind of=20
quoting is necessary for SQL, and then we can continue parsing the string=
SQL :)

Apart from needing to specify the namespace, (and I'm sure we can get=20
reasonable defaults by playing with the python call stack) one does ot ne=
to assign 'temporary' dictionary keys to get the information into the que=
at all.

And finally, this can be expanded into a general python function for doin=
'expression-based' formatting strings, like the following:

formatexpr ( "Hello %(form['name'])s, if you are %(form['age'])s this yea=
you'll be %(form['age']+1)s next year." )

Is this not nifty?

   ("`-/")_.-'"``-._        Chris Cogdon <chris@cogdon.org>
    . . `; -._    )-;-,_`)
   (v_,)'  _  )`-.\  ``-'
  _.- _..-_/ / ((.'
((,.-'   ((,/   fL