I am trying to input the dates from outside text file into sql query using python Below is my code:
import cx_Oracle
import pandas as pd
import numpy as np
dsn_tns = cx_Oracle.makedsn(ip, port,service_name = SERVICE_NAME)
db = cx_Oracle.connect('username', 'password', dsn_tns)
curs = db.cursor()
with open("Date.txt") as file:
log=file.read().splitlines()
import datetime
var1=datetime.datetime.strptime(log[0], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
var2=datetime.datetime.strptime(log[1], "%d-%b-%Y %H:%M:%S").strftime("%d-%b-%Y %H:%M:%S")
query = curs.execute("""SELECT * from table_name where cr_date >= TO_DATE(%s,'DD-MON-YYYY HH24:MI:SS') AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))
from pandas import DataFrame
df = DataFrame(query.fetchall())
Here i connect my query with Oracle and then trying to insert the date from Date.txt into SQL query which includes two dates like below:
27-DEC-2018 00:00:00
26-JAN-2019 00:00:00
Then i want to save my result into dataframe df but getting below error while inserting dates from text file.
Traceback (most recent call last):
File "C:\Users\ab\Desktop\oracle_connect.py", line 24, in <module>
AND cr_date < to_date(%s,'DD-MON-YYYY HH24:MI:SS')""", (var1, var2))
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
Please suggest me what changes i need to do for running this code. Also let me know if my dataframe df pulls the column header too as currently i am inserting column header separately with other code. Thanks in advance
cx_oracle takes bind variable sin the form of ":1 , :2, :3" or dictionaries.
try changing the place where you have "%s" for the bind variables to be ":1" and ":2". That is how i have always done binds in oracle. not sure it that will fix any problems though. take out the "s of course.
you could also do it with a dictionary and have it be ":val1" & ":val2" and then pass in a dictionary instead of a list like {"val1":"something", "val2":"something2"}