Python bind variables cx_Oracle

Question

I'm a Python newbie, I'm having problems using binding 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 get 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 solved it with

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

but I can’t understand why the previous command was out of order.

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

Answer 1

You are using bindings incorrectly.

There are three different ways to bind variables with cx_Oracle: see here :

1), passing the tuple to the 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 SQL statements with named variables :

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

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

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

---

Notes

Why does your code work?

Let's try to understand what is happening 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 a single variable. This is a named variable associated with the name bind . Then you must specify the parameter as a named parameter as follows:

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

Or using a dictionary, for example:

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

However, since cx_Oracle receives the tuple instead, it disappears in the number binding, as if your SQL statement were:

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

And as you pass bind['var'] twice, this is just the string "ciao" . This is a mapping of two tuple elements to numbered variables:

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

This is random, but the code is very misleading.

Tuple with a single value for binding

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

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

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

Answer 2

@ffarquest says that using the dictionary is not supported by cx_Oracle, but actually @ giovanni-de-ciantis just used 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 this example, I believe that the second link to :bind should be replaced with something else, because it is not executed in sequential order. They also renamed the bind variable to get rid of the 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 ) 

---

In this article we will talk about the fact that you can use the dictator: http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html

Answer 3

Hey, when I try to run your code, I got the following error cx_Oracle.DatabaseError: ORA-03001: unrealized function How to fix it? I do not understand