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