don't use bind variables (was Re: Using strings with ' in them in SQL-queries)
Fredrik Lundh
effbot at telia.com
Wed May 31 17:24:49 EDT 2000
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>
More information about the Python-list
mailing list