[DB-SIG] cx_Oracle - passing unnecessary bind variables

[Paul Moore]

I'm trying to write a function, using cx_Oracle, which allows the user
to specify a SQL statement, to be executed with some bind variables
out of a common set.

Example:

    class Collector:
        def __init__(self, cn, **kws):
            self.vars = kws
        def execute(sql):
            c = cn.cursor()
            c.execute(sql, self.vars)
            c.close()

This is a simplified example, but the basic usage is something like
this:

    cn = cx_Oracle.connect(...)
    coll = Collector(cn, a=12, b=24, c=11, d=13)
    coll.execute("insert into t1 (a,b) values(:a, :b)")
    coll.execute("insert into t2 (a,c,d) values(:a, :c, :d)")

The problem is that this doesn't work - it returns an Oracle error,
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number.

OK, this is to some extent a limitation of Oracle (or maybe of
cx_Oracle, I don't know how Oracle OCI works at this low a level) -
I'm passing bind variables which aren't used by the SQL statement. But
is there any way of doing something like this? One option is to scan
the SQL manually - something like

    rx = re.compile(":\w+")
    vars = [m.group()[1:] for m in rx.finditer(sql)]
    bindvars = {}
    for var in vars:
        try:
            bindvars[var] = self.vars[var]
        except KeyError:
            pass

but (a) that's very messy, and (b) it's fragile - consider
sql = "select a || ':b' from t where a = :a"...

Can anyone suggest anything better?

Thanks,
Paul.
-- 
This signature intentionally left blank