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':'some...@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':'some...@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':'some...@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':'some...@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
--
http://mail.python.org/mailman/listinfo/python-list
