Python cx_Oracle绑定变量 [英] Python cx_Oracle bind variables

问题描述

我是Python新手，使用绑定变量时遇到麻烦。如果我执行下面的代码，一切正常。

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

我已经解决了

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

但是我不明白为什么前面的命令不正确。

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

使用绑定变量的正确方法是什么？我正在使用cx_Oracle。

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

推荐答案

您正在滥用绑定。

使用cx_Oracle绑定变量有三种不同的方式，因为请参见此处：

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

1），将元组传递给带有编号变量：

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）通过SQL语句具有命名变量的关键字参数：

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）通过将字典传递给SQL带有命名变量的策略 ：

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})

---

备注

为什么您的代码为什么起作用？

让我们尝试了解这里发生的情况：

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会理解它期望一个变量。这是一个命名变量，通过名称 bind 链接。然后，您应该像这样给定一个参数作为命名参数：

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")

或者使用字典，例如：

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

但是，当cx_Oracle接收到一个元组时，它将回退到数字绑定中，就像您的SQL语句是：

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"

您将两次传递 bind ['var'] ，这只是字符串 ciao 。它将两个元组项映射到编号变量：

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.

具有一个要绑定的值的元组

还请注意，第一个选项需要一个元组。但是，如果要绑定一个值，则可以使用此符号创建一个具有单个值的元组：

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,))

：感谢@ tyler-christian提到cx_Oracle支持通过dict。

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

这篇关于Python cx_Oracle绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！