2

I am trying to use cx_Oracle to query a table in oracle DB (version 11.2) and get rows with values in a column between a datetime range.

I have tried the following approaches:

  1. Tried between clause as described here, but cursor gets 0 rows 

    parameters = (startDateTime, endDateTime)
query = "select * from employee where joining_date between :1 and :2"
cur = con.cursor()
cur.execute(query, parameters)

  2. Tried the TO_DATE() function and Date'' qualifiers. Still no result for Between or >= operator. Noteworthy is that < operator works. I also got the same query and tried in a sql client, and the query returns results. Code:

    #returns no rows:
query = "select * from employee where joining_date >= TO_DATE('" + startDateTime.strftime("%Y-%m-%d") + "','yyyy-mm-dd')"
cur = con.cursor()
cur.execute(query)
#tried following just to ensure that some query runs fine, it returns results:
query = query.replace(">=", "<")
cur.execute(query)

Any pointers about why the between and >= operators are failing for me? (my second approach was in line with the answer in Oracle date comparison in where clause but still doesn't work for me)

I am using python 3.4.3 and used cx_Oracle 5.3 and 5.2 with oracle client 11g on windows 7 machine

iehrlich
  • 3,572
  • 4
  • 34
  • 43
Shoonya
  • 118
  • 8
  • No clue, really, just a thought... I haven't seen `yyy` as a date format element, and I am not sure if it can mess things up. I assume you meant `yyyy` (four digit year) or perhaps just `yy`. Fix that and see if it helps. –  Jun 29 '17 at 00:23
  • Yes, yyy was a typo here, my code has yyyy corrected it – Shoonya Jun 29 '17 at 00:25

2 Answers2

2

Assume that your employee table contains the field emp_id and the row with emp_id=1234567 should be retrieved by your query.

Make two copies of your a program that execute the following queries

query = "select to_char(:1,'YYYY-MM-DD HH24:MI:SS')||' >= '||to_char(joining_date,'YYYY-MM-DD HH24:MI:SS')||' >= '||to_char(:2,'YYYY-MM-DD HH24:MI:SS') resultstring from employee where emp_id=1234567"

and

query="select to_char(joining_date,'YYYY-MM-DD HH24:MI:SS')||' >= '||to_char(TO_DATE('" + startDateTime.strftime("%Y-%m-%d") + "','yyyy-mm-dd'),'YYYY-MM-DD HH24:MI:SS') resultstring from employee where emp_id=1234567"

Show us the code and the value of the column resultstring

miracle173
  • 1,852
  • 16
  • 33
  • Actually I had figured out the problem, which was human error on my end. I had two databases with same schema. I was breaking the query and running the same in a sql client (I was running by mistake on other DB), and getting result. Finally spotted that and fixed it. I will mark your reply as answer as this and similar queries would have helped me figure this out much sooner. – Shoonya Jul 03 '17 at 17:48
0

You are constructing SQL queries as strings when you should be using parameterized queries. You can't use parameterization to substitute the comparison operators, but you should use it for the dates.

Also, note that the referenced answer uses the PostgreSQL parameterisation format, whereas Oracle requires you to use the ":name" format.

holdenweb
  • 33,305
  • 7
  • 57
  • 77
  • Thanks for the input, but my first attempt in the question does use parameterized query. It is almost same as what is on the link I provided, only one difference that they define the string literal using triple quotes. So it should have worked. Or am I missing something there? cx_Oracle should support even ":number" format as per that page – Shoonya Jun 29 '17 at 10:57
  • My bad - you don't appear to be missing anything and I would have expected your parameterised approach to work. As I remember my Oracle syntax (from a very long time ago) you can use literal strings like `'MM-DD-YYYY'` as date constants. Maybe that will help formulating an alternative? – holdenweb Jun 30 '17 at 13:51