Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #31400 > unrolled thread

cx_Oracle clause IN using a variable

Back to article view | Back to comp.lang.python

Contents

  cx_Oracle clause IN using a variable Beppe <giuseppecostanzi@gmail.com> - 2012-10-16 06:41 -0700
    Re: cx_Oracle clause IN using a variable Ian Kelly <ian.g.kelly@gmail.com> - 2012-10-16 11:04 -0600
    Re: cx_Oracle clause IN using a variable Hans Mulder <hansmu@xs4all.nl> - 2012-10-16 19:22 +0200
      Re: cx_Oracle clause IN using a variable Beppe <giuseppecostanzi@gmail.com> - 2012-10-17 06:49 -0700

#31400 — cx_Oracle clause IN using a variable

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@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?
suggestions?

regards
beppe

[toc] | [next] | [standalone]

#31420

On Tue, Oct 16, 2012 at 7:41 AM, Beppe <giuseppecostanzi@gmail.com> wrote:
> 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)

[toc] | [prev] | [next] | [standalone]

#31421

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@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@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@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@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

[toc] | [prev] | [next] | [standalone]

#31502

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@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@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@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@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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web