Search code examples
pythoncx-oraclebind-variables

Python cx_Oracle bind variables

I am a Python newbie, I am having troubles in the use of bind variables. If I execute the code below everything works fine.

bind= {"var" : "ciao"}
sql = "select * from sometable where somefield = :bind"
cur.prepare(sql)
cur.execute(sql,bind)

Instead if I add another bind variable I obtain an error.

bind= {"var" : "ciao"}
sql = "select * from sometable where somefield = :bind and otherfield = :bind"
cur.prepare(sql)
cur.execute(sql,(bind,bind))

cur.execute(sql,(bind,bind))
Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data

I have solved it with

cur.execute(sql,(bind["var"],bind["var"]))

but I can't understand why the previous command was not ok.

Which is the correct way to use bind variables? I am using cx_Oracle.

Solution

  • You are misusing the binding.

    There are three different ways of binding variables with cx_Oracle as one can see here :

    1) by passing a tuple to a SQL statement with numbered variables :

    sql = "select * from sometable where somefield = :1 and otherfield = :2"
cur.execute(sql, (aValue, anotherValue))

    2) By passing keyword arguments to a SQL statement with named variables :

    sql = "select * from sometable where somefield = :myField and otherfield = :anotherOne"
cur.execute(sql, myField=aValue, anotherOne=anotherValue)

    3) By passing a dictionary to a SQL statement with named variables :

    sql = "select * from sometable where somefield = :myField and otherfield = :anotherOne"
cur.execute(sql, {"myField":aValue, "anotherOne":anotherValue})

    Remarks

    Why does your code work then ?

    Let's try to understand what happens here :

    bind= {"var" : "ciao"}
sql = "select * from sometable where somefield = :bind and otherfield = :bind"
cur.execute(sql,(bind["var"], bind["var"]))

    Oracle will understand that it expects one variable. This is a named variable, linked by name bind. You should then give a parameter as a named parameter like this :

    cur.execute(sql, bind="ciao")

    Or using a dictionary, like that :

    cur.execute(sql, {bind:"ciao"})

    However, as cx_Oracle receives a tuple instead, it fallbacks in a binding by number, as if your SQL statement was :

    sql = "select * from sometable where somefield = :1 and otherfield = :2"

    And as you are passing bind['var'] twice, which is just the string "ciao". It is mapping the two tuple items to the numbered variables :

    cur.execute(sql, ("ciao", "ciao"))

    That runs by chance but the code is very misleading.

    Tuple with a single value to bind

    Also note that the first option requires a tuple. But if you have a single value to bind, you can use this notation to create a tuple of a single value :

    sql = "select * from sometable where somefield = :1"
cur.execute(sql, (aValue,))

    [EDIT] : Thanks to @tyler-christian for mentioning that passing a dict was supported by cx_Oracle.