Python cx_Oracle 绑定变量

Python cx_Oracle bind variables

提问by Giovanni De Ciantis

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

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

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.

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

采纳答案by ffarquet

You are misusing the binding.

您正在滥用绑定。

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

使用 cx_Oracle 绑定变量有三种不同的方式，可以在此处看到：

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

1) 通过将元组传递给带有编号变量的 SQL 语句：

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:

2) 通过将关键字参数传递给带有命名变量的 SQL 语句：

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:

3) 通过将字典传递给具有命名变量的 SQL 语句：

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 :

Oracle 会理解它需要一个变量。这是一个命名变量，由 name 链接bind。然后，您应该将参数作为命名参数提供，如下所示：

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 :

但是，由于 cx_Oracle 接收的是一个元组，它会在按数字绑定时回退，就好像您的 SQL 语句是：

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 :

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

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.

[编辑]：感谢@tyler-christian 提到 cx_Oracle 支持传递字典。

回答by Tyler Christian

@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.

@ffarquest 说 cx_Oracle 不支持使用字典，但事实上，@giovanni-de-ciantis 只是错误地使用了它。

---

---

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 :bindwould need to be replaced with something different because it isn't being done in sequential order. Also, renamed the variable bindto get rid of confusion.

在给定的例子中，我相信第二个引用:bind需要用不同的东西替换，因为它不是按顺序完成的。此外，重命名变量bind以摆脱混乱。

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

这篇文章来自 2007 年，表明您可以使用字典：http: //www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html