While Importing data to Python Environment from Oracle Database

Question

I am trying to import one of view table o python by executing the following code. But the problem is by running the same code on oracle SQl developer i am getting the data but while executing the same line of code i am getting zero rows of data (No Data). The code is successfully getting executed and the connection is also well established with Oracle DB. Kindly help me how to get the data loaded on python

I have tried to use To_Date function for getting the results

import cx_Oracle

cx_Oracle.clientversion()

conn = cx_Oracle.connect('##########')

c = conn.cursor()

querystring1 = "(select * from CP_FALLOUT_VIEW where SCOPE_DT = TO_DATE(sysdate-90))"

df1 = pd.read_sql_query(querystring1, con=conn)

Expected : Dataframe with all the data rows

Actual: Dataframe with all the headers but no rows of data

Bob Jarvis - Слава Україні · Accepted Answer · 2019-05-03 11:36:15Z

The SYSDATE function already returns a DATE value so there's no need for ...TO_DATE(sysdate-90). Nor can I see any reason to surround the entire query in parentheses. However, keep in mind that in Oracle a DATE is actually a timestamp, containing fields for year, month, day, hours, minutes, and seconds. When a comparison like SCOPE_DT = SYSDATE - 90 is made the SCOPE_DT value must match not only the year/month/day from SYSDATE, but also the hours:minutes:seconds. I suspect you really want something like

select *
  from CP_FALLOUT_VIEW
  where TRUNC(SCOPE_DT) = TRUNC(sysdate) - 90

Here the TRUNC function is used to truncate the dates - that is, to set the hours:minutes:seconds to 00:00:00 - so that we can effectively compare only the day/month/year portion of the DATE values.

But this is going to be slow because we're applying the TRUNC function to every row in the view. A better option would be

SELECT *
  FROM CP_FALLOUT_VIEW
  WHERE SCOPE_DT BETWEEN TRUNC(SYSDATE) - 90
                     AND TRUNC(SYSDATE) - 89

Here you're determining if SCOPE_DT falls between the start of SYSDATE-90 and the day following that, which is SYSDATE-89. However even this has one minor problem - it'll pick up a SCOPE_DT which exactly matches TRUNC(SYSDATE)-89, because a BETWEEN comparison includes its endpoints. Thus to only include dates which fall on the day which is exactly 90 days in the past and perform reasonably you'd want to use

SELECT *
  FROM CP_FALLOUT_VIEW
  WHERE SCOPE_DT BETWEEN TRUNC(SYSDATE) - INTERVAL '90' DAY
                     AND TRUNC(SYSDATE) - INTERVAL '89' DAY - INTERVAL '1' SECOND

Best of luck.

Collectives™ on Stack Overflow

While Importing data to Python Environment from Oracle Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related