CODEWITHSUNDEEP
pythonpython-3.xcx-oracle
Dheepan Manoharan
Dheepan Manoharan

Reputation: 45

Python CX_Oracle : oracle query with conditions including a list of values(IN)

I would like get a list of values from a DB table in python using cx_oracle. I am unable to write a query with two where conditions one of single value and another of a list.

I am able to achieve it when I filter it two strings separately or only filter it by a list of string. But could not achieve it together!!

    output_list=[]
    catlist = ','.join(":x%d" % i for i, _ in enumerate(category_list))        
    db_cursor = connection.cursor()
    db_cursor.execute("""
    SELECT LWEX_WORD_EXCLUDE
    FROM WCG_SRC_WORD_EXCLUDE
    WHERE LWEX_CATEGORY IN (%s) and LWIN_USER_UPDATED = :did""" % catlist, category_list, did =argUser)
    for word in db_cursor :
        output_list.append(word[0])

The current code throws an error. But if I have either of the conditions separately then it works fine. The python version that I am using is 3.5

Upvotes: 0

Views: 2102

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

You cannot mix and match "bind by position" and "bind by name", which is what you are doing in the above code. My suggestion would be to do something like this instead:

output_list=[]
catlist = ','.join(":x%d" % i for i, _ in enumerate(category_list))
bindvals = category_list + [arguser]       
db_cursor = connection.cursor()
db_cursor.execute("""
SELECT LWEX_WORD_EXCLUDE
FROM WCG_SRC_WORD_EXCLUDE
WHERE LWEX_CATEGORY IN (%s) and LWIN_USER_UPDATED = :did""" % catlist, bindvals)
for word in db_cursor :
    output_list.append(word[0])

Upvotes: 1

Related Questions