don't use bind variables (was Re: Using strings with ' in them in SQL-queries)

[Fredrik Lundh]

Aahz Maruch <aahz at netcom.com> wrote:
> I've been keeping silent for a while, but I'm now going to start
> speaking forcefully against this style.  It's a real PITA to maintain
> when you have complex queries, because you can't use dicts and named
> parameters.  I find that it works much better to do something like this:
> 
> dict = {....}
> q = """
>     SELECT %(field)s FROM %(table)s
>     WHERE %(key)s IN (SELECT %(key)s FROM %(jointable)s)
>     """ % dict

maybe the really right thing here would be to implement a small
helper function which implements "pyformat" parameter handling,
no matter what parameter format your database happens to use.

how about (almost completely untested):

import re

PARAMTYPE = "qmark"

def fixup(query, params, pattern=re.compile("%\((\w+)\)s")):
    if PARAMTYPE == "pyformat":
        return query, params
    paramlist = []
    def map_parameter(match, params=params, paramlist=paramlist):
        paramlist.append(params[match.group(1)])
        if PARAMTYPE == "qmark":
            return "?"
        elif PARAMTYPE == "numeric":
            return ":%d" % len(paramlist)
        elif PARAMTYPE == "format":
            return "%s"
    return pattern.sub(map_parameter, query), paramlist

dict = { "field": "myfield", "table": "mytable", "key": "mykey" }
query = """SELECT %(field)s FROM %(table)s WHERE %(key)s = 10"""

apply(db.query, fixup(query, dict))

(or something)

> (This is a stupid example, but it demonstrates the problem that
> frequently occurs when you have to use the same variable more than
> once.)  Remember that the Python Way is to write code as clearly as
> possible until such time as speed is proven to be an issue.

sure, but designing your way around FAQ's is also a good idea.

(but I'm just a code monkey, so what do I know ;-)

</F>