Skip to content
Advertisement

Python: cx_Oracle does not like how I am entering date

I am trying to do a simple select all query in python using the Cx_oracle module. When I do a select all for the first ten rows in a table I am able to print our the output. However when I do a select all for the first ten rows for a specific date in the table all that gets printed out is a blank list like this: [].

Here is the query select all query that prints out all the results:

The above query works and is able to print out the results.

Here is the query that I am having trouble with and this query below works in sql developer:

I also tried this way where I define the date outside of the query.

Advertisement

Answer

Oracle dates have a time portion. The query

Will only give you the rows for which the value for the column requested_time is 01-jul-2021 00:00. Chances are that you have other rows for which there is a time portion as well. To cut off the time portion there are several options. Note that I explicitly added the a TO_DATE function to the date – you’re assuming that the database is expecting a dd-mon-yyyy format and successfully will do the implicit conversion but it’s safer to let the database know.

  1. TRUNC truncate the column – this will remove the time portion
  1. Format the column date to the same format as the date you supplied and compare the resulting string:

Example:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement