Python cx_Oracle bind variables -

i python newbie, having troubles in use of bind variables. if execute code below works fine.

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

instead if add bind variable obtain error.

bind= {"var" : "ciao"} sql = "select * sometable somefield = :bind , otherfield = :bind" cur.prepare(sql) cur.execute(sql,(bind,bind))  cur.execute(sql,(bind,bind)) oracle.notsupportederror: variable_typebyvalue(): unhandled data 

i have solved with

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

but can't understand why previous command not ok.

which correct way use bind variables? using cx_oracle.

you misusing binding. there 2 different ways of binding variables :

1) by giving tuple sql numbering variables :

sql = "select * sometable somefield = :1 , otherfield = :2" cur.execute(sql, (avalue, anothervalue)) 

2) or can reference variables name :

sql = "select * sometable somefield = :my_field , otherfield = :anotherone" cur.execute(sql, myfield=avalue, anotherone=anothervalue) 

---

remarks

using dictionary ?

in example, mixing both. using dictionary not supported cx_oracle. unpack dictionnary fallback in second example :

sql = "select * sometable somefield = :my_field , otherfield = :anotherone" my_dict = { "myfield":avalue, "anotherone":anothervalue } cur.execute(sql, **my_dict) 

tuple 1 value bind

also note first case need receive tuple. if have 1 value bind, have use weird-but-ok notation :

sql = "select * sometable somefield = :1" cur.execute(sql, (avalue,)) 

why code work ?

your solution working bad. let's try understand happens here :

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

oracle understand expects 1 variable. named variable, linked name 'bind'. should give parameter named parameter :

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

however, cx_oracle receives tuple instead of named parameter, tries use binding number. if sql :

sql = "select * sometable somefield = :1 , otherfield = :2" 

and giving bind['var'] twice, contains "ciao". doing , corresponds number of expected arguments:

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

that runs chance code misleading.