Re: [cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Himanshu <him...@gm...>]

On 1 March 2011 15:29, Amaury Forgeot d'Arc <ama...@gm...> wrote:

> Hi,
>
> 2011/3/1 Himanshu <him...@gm...>:
> >       WHERE log_date >= '10-Feb-11'
> >         AND log_date <= '10-Feb-11'
>
> You should avoid date literals in SQL statements. The exact format may
> depend on environment
> variables (language...) and other Oracle client settings.
> Use something like to_date('10-02-2011', 'DD-MM-YYYY')
> Also, this WHERE clause only selects lines where "log_date" has no
> time (or midnight).
> Is it really the case in your data?
>
Thanks for the reply. The actual code has a variable that populates with the
date time value oracle needs. This was the smallest test case I could create
for my post. Sorry for the confusion though.

What puzzles me is that the following code returns rows from python  :-

-- begin working code with one join less --

import cx_Oracle
connection_string = {snipped}
connection        = cx_Oracle.connect(**connection_string)
cursor            = connection.cursor()
cursor.execute('''
    SELECT g.city, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
      WHERE log_date >= to_date('10-02-2011', 'DD-MM-YYYY')
        AND log_date <= to_date('10-02-2011', 'DD-MM-YYYY')
        AND g.country  =  'IND'
   GROUP BY g.city, status
''')
print cursor.fetchall()

 -- end working code with one join less --

and the following works from sql developer

-- begin identical working query --

SELECT s.hasc, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
       JOIN statoids s
         ON s.capital = g.city
      WHERE log_date >= '10-Feb-11'
        AND log_date <= '10-Feb-11'
        AND g.country  =  'IND'
   GROUP BY s.hasc, status;

-- end identical working query --

but the following doesn't :-

-- begin not working code --

import cx_Oracle
connection_string = {snipped}
connection        = cx_Oracle.connect(**connection_string)
cursor            = connection.cursor()
cursor.execute('''
    SELECT s.hasc, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
       JOIN statoids s
         ON s.capital = g.city
      WHERE log_date >= to_date('10-02-2011', 'DD-MM-YYYY')
        AND log_date <= to_date('10-02-2011', 'DD-MM-YYYY')
        AND g.country  =  'IND'
   GROUP BY s.hasc, status
''')
print cursor.fetchall()

-- end not working code --