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.
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})
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.
@ffarquest says that using a dictionary isn't supported by cx_Oracle but it is in fact, @giovanni-de-ciantis was just using it incorrectly.
named_params = {'dept_id':50, 'sal':1000}
query1 = cursor.execute(
'SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal',
named_params
)
OR
query2 = cursor.execute(
'SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal',
dept_id=50,
sal=1000
)
In the given example, I believe the second reference to :bind
would need to be replaced with something different because it isn't being done in sequential order. Also, renamed the variable bind
to get rid of confusion.
bind_dict = {bind:"var" : diff:"ciao"}
sql = "select * from sometable where somefield=:bind and otherfield=:diff"
cur.prepare(sql)
cur.execute(sql, bind_dict )
This article is from 2007 showing that you can use a dictionary: http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With