cx_Oracle clause IN using a variable

[Hans Mulder]

On 16/10/12 15:41:58, Beppe wrote:
> Hi all,
> I don't know if it is the correct place to set this question, however,
> I'm using cx_Oracle to query an Oracle database.
> I've a problem to use the IN clause with a variable.
> My statement is 
> 
> sql = "SELECT field1,field2,field3
>         FROM my_table
>         WHERE field_3 IN (:arg_1)"
> 
> where arg_1 is retrive by a dictionary
> that is build so
> 
>  my_dict = {'location':"X",
>             'oracle_user':'user',
>             'oracle_password':'pass',
>             'dsn':'dsn',
>             'mailto':'someone at somewhere.org',
>             'codes':"CNI,CNP"}
> 
> args = (dict['codes'],)
>            
>  
> con = cx_Oracle.connect(my_dict["oracle_user"],
>                          my_dict["oracle_password"],
>                          my_dict["dsn"])
>                          
> cur = con.cursor()
> cur.execute(sql,args)
> rs =  cur.fetchall()           
> 
> but it doesn't work in the sense that doesn't return anything
> 
> If i use the statment without variable 
> 
> SELECT field1,field2,field3
> FROM my_table
> WHERE field_3 IN ('CNI','CNP')
> 
> the query works
> 
> what is wrong?

You only have a single placeholder variable,
so your statement is equivalent to

SELECT field1,field2,field3
FROM my_table
WHERE field_3 IN ('CNI,CNP')

Presumably 'CNI,CNP' is not a valid value for field_3,
thus your query finds no records.

> suggestions?

To verify that you have the correct syntax, try it
with a single value first:

my_dict = {'location':"X",
            'oracle_user':'user',
            'oracle_password':'pass',
            'dsn':'dsn',
            'mailto':'someone at somewhere.org',
            'codes':"CNI"}

It that produces some of the records you want, then the
question is really: can you somehow pass a list of values
via a single placeholder variable?

I'm, not a cx_Oracle expert, but I think the answer is "no".


If you want to pass exactly two values, then the work-around
would be to pass them in separate variables:

my_dict = {'location':"X",
            'oracle_user':'user',
            'oracle_password':'pass',
            'dsn':'dsn',
            'mailto':'someone at somewhere.org',
            'code1':"CNI",
            'code2':"CNP"}

sql = """SELECT field1,field2,field3
         FROM my_table
         WHERE field_3 IN (:arg_1, :arg_2)"""
args = (my_dict['code1'],my_dict['code2'])


If the number of codes can vary, you'll have to generate a
query with the correct number of placholders in it.  Mabye
something like this (untested):

my_dict = {'location':"X",
            'oracle_user':'user',
            'oracle_password':'pass',
            'dsn':'dsn',
            'mailto':'someone at somewhere.org',
            'codes':"Ornhgvshy,vf,orggre,guna,htyl"}


args = my_dict['codes'].split(",")
placeholders = ','.join(":x%d" % i for i,_ in enumerate(args))

sql = """SELECT field1,field2,field3
         FROM my_table
         WHERE field_3 IN (%s)""" % placeholders

con = cx_Oracle.connect(my_dict["oracle_user"],
                         my_dict["oracle_password"],
                         my_dict["dsn"])

cur = con.cursor()
cur.execute(sql,args)
rs =  cur.fetchall()


Hope this helps,

-- HansM