cx_Oracle clause IN using a variable

[Beppe]

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':'(e-mail address removed)',
'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?
suggestions?

regards
beppe

 

[Ian Kelly]

Hi all,
I don't know if it is the correct place to set this question, however,

The best place to ask questions about cx_Oracle would be the
cx-oracle-users mailing list.

what is wrong?
suggestions?

With the bind parameter you're only passing in a single string, so
your query is effectively equivalent to:

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

You can't pass an actual list into a bind parameter the way that you
would like. You need to use a separate parameter for each item in the
list. This may mean constructing the query dynamically:

in_vars = ','.join(':%d' % i for i in xrange(len(sequence_of_args)))

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

cursor.execute(sql, sequence_of_args)

 

[Hans Mulder]

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':'(e-mail address removed)',
'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':'(e-mail address removed)',
'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':'(e-mail address removed)',
'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':'(e-mail address removed)',
'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

 

[Beppe]

Il giorno martedì 16 ottobre 2012 19:23:22 UTC+2, Hans Mulder ha scritto:

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':'(e-mail address removed)',

'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':'(e-mail address removed)',

'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':'(e-mail address removed)',

'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':'(e-mail address removed)',

'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