cx_Oracle clause IN using a variable

[Beppe]

Il giorno martedì 16 ottobre 2012 19:23:22 UTC+2, Hans Mulder ha scritto:
> 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

Thanks a lot of to ian and hans for your explanations that have allowed me to resolve my problem and above all to understand the why I was wrong.

regards
beppe