在cx_Oracle中搜索名称 [英] Search for name in cx_Oracle

问题描述

我有var='smth'，我需要检查表'smtb'中是否存在var

I have var='smth' and I need to check if var exists in the table 'smtb'

我尝试了一些方法，但是它不起作用:

I tried something this, but it does not work:

rows=curs.fetchall()
for row in rows:
            if var==row:
                print("Yes")

当我打印行元素时，会得到一些类似的图案:

When I print elements of rows, I get some pattern like this:

('element',)

如何检查表中是否存在var?

How can I check whether var exists in the table?

推荐答案

不要在Python中执行此操作.如果要在数据库表中搜索某些内容，最快，最有效的方法是在SQL中进行搜索.不要忘记使用绑定变量.

Don't do this in Python. If you want to search for something in a database table by far the quickest and most efficient way is to do it in SQL. Don't forget to use bind variables.

假设您有一个游标对象curs，它可能看起来像这样(假设您的表在var上是唯一的).

Assuming you have a cursor object curs it might look something like this (assuming your table is unique on var).

>>> sql = "select * from my_table where var = :var"
>>> bind_variables = {'var' : 'smth'}
>>>
>>> curs.execute(sql, bind_variables)
[<cx_Oracle.STRING with value None>]
>>> results = curs.fetchall()
>>> results
[('smth',)]
>>> try:
...     var = results[0][0]
...     print ('Yes')
... except IndexError:
...     print ('No')
...
Yes

然后，如果您正在寻找不存在的东西，则会得到以下信息.

Then if you're looking for something that doesn't exist you get the following.

>>> bind_variables = {'var' : 'other'}
>>> results = curs.fetchall()
>>> results
[]
>>> try:
...     var = results[0][0]
...     print ('Yes')
... except IndexError:
...     print ('No')
...
No

您未得到期望的实际原因是cx_Oracle根据 PEP 249 .您期望只返回一列，因此，如果要按自己的方式进行操作，则需要访问元组的第0 ^th 索引.

The actual reason why you don't get what you expect is that cx_Oracle returns a list of tuples as per PEP 249. You're expecting only one column to be returned so you need an access the 0^th index of the tuple, if you want to do it in the manner you are.

>>> rows = [('other',),('smth',)]
>>> var = 'smth'
>>> for row in rows:
...     if var == row[0]:
...         print ('Yes')
...
Yes

这篇关于在cx_Oracle中搜索名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！