CODEWITHSUNDEEP
pythonoracle-databasedatetimewhere-clausepyodbc
akleefel
akleefel

Reputation: 87

pyodbc: ORACLE SQL Statement with WHERE Clause that incorporates date range

I am using pyodbc to extract data from a database table. Now, I would like to limit the query to only contain the records that were added within the last 24 hrs. The information about the time a record was added is contained in the DATE_ADDED column .

This is what I am trying:

pyodbc.pooling = False
conn_str = (
    r'DRIVER={Oracle in OraClient12Home1};'
   .....
   .....
   .....)

conn = pyodbc.connect(conn_str)
curs = conn.cursor()


date_today = datetime.datetime.today()
date_yesterday = date_today - datetime.timedelta(days=1)

sql = f"SELECT PROD_ID, ID, COUNT, DATE_ADDED FROM LZE.APODAT WHERE DATE_ADDED < {date_today} AND DATE_ADDED  > {date_yesterday}"

my_df = pd.DataFrame(pd.read_sql(sql, conn))

The error message I get is:

UnicodeDecodeError: 'utf-16-le' codec can't decode bytes in position 218-219: illegal encoding

I'm sure it has to do with the different formatting/data types of the DATE_ADDED column but I am unsure how to approach this. In the database it looks as follows: 2019-04-24 10:21:05 AM

Any help would be greatly appreciated.

Upvotes: 2

Views: 1085

Answers (2)

Parfait
Parfait

Reputation: 107707

Consider parameterization (the industry standard when using SQL in application layer) as Python's datetime object may translate to Oracle's date type.

And yes, you can parameterize in Pandas with read_sql() using the params argument (which does not need a pd.DataFrame() wrapper). Plus, you do not need F-strings, so this version can work in any Python version.

sql = """SELECT PROD_ID, ID, COUNT, DATE_ADDED 
         FROM LZE.APODAT 
         WHERE DATE_ADDED < ? AND DATE_ADDED  > ?
      """

my_df = pd.read_sql(sql, conn, params=[date_today, date_yesterday])

Otherwise convert to string (removing decimal microseconds) and use Oracle's TO_DATE():

sql = """SELECT PROD_ID, ID, COUNT, DATE_ADDED 
         FROM LZE.APODAT 
         WHERE DATE_ADDED < TO_DATE(?, 'YYYY-MM-DD HH:MI:SS')
           AND DATE_ADDED > TO_DATE(?, 'YYYY-MM-DD HH:MI:SS')?
      """

my_df = pd.read_sql(sql, conn, params=[date_today.strftime("%Y-%m-%d %H:%M:%S"), 
                                       date_yesterday.strftime("%Y-%m-%d %H:%M:%S")]
                   )

Upvotes: 4

O. Jones
O. Jones

Reputation: 108806

You say you want the last 24 hours...

You can say DATE_ADDED >= SYSDATE() - 1.0 in a where clause to get that. SYSDATE() gets you the present moment. Dates in oracle behave like floating point numbers, where 1.0 is 24 hours. So subtracting 1.0 from the present moment gets you the same time yesterday.

If you want everything from midnight yesterday, you can truncate the date. Use 

DATE_ADDED >= TRUNC(SYSDATE() - 1.0)

Upvotes: 0

Related Questions