Search For Name In Cx_oracle

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:

Solution 1:

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.

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

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