I am writing code which uses a Python script to execute a SQL (Oracle) statement, as shown below:
PYTHON:
with open(os.path.join(WORKING_PATH, "sql", "fetchCalendar.sql"), 'r') as
fetch_date = fetch.read()
#(Some other code)
CD = timestamp_tostring(ACTUALDATE)
print("CD:", CD)
cnnSB.cursor.execute(fetch_date, CD)
fetchCalendar.sql
SELECT YEARNBR as yearnbr, QUARTERNBR as quarternbr, MONTHNBR as monthnbr, WEEKNBR as weeknbr
FROM MFS_MFSCALENDAR
WHERE ACTUALDATE = TO_DATE(:CD, 'DD-MON-YY')
When I try running it with the following values, this is outputted:
CD: 30-AUG-18
Traceback (most recent call last): File "AHT_Init.py", line 919, in init_load(data, end_of_week, cur, 3) File "AHT_Init.py", line 644, in init_load populate_rollup(modelid, test_data, train_data, rollup_cmd) File "AHT_Init.py", line 684, in populate_rollup cnnSB.cursor.execute(fetch_date, CD) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
(Emphasis on 'ORA-01036: illegal variable name/number')
Naturally, I tried changing the variable names to no avail. I tried running the SQL code using Oracle SQL Developer (passing in '01-JUN-18', including the single quotes, when prompted), and got this error:
ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.
Though, when I swapped out the problematic line with the following:
WHERE ACTUALDATE = TO_DATE('01-JUN-18', 'DD-MON-YY');
the code worked as desired. Can anybody walk me through what the issue might be, and how to solve it? Thank you so much!
EDIT: I am aware of the missing semicolon in the fetchCalendar.sql code; this is necessary for how Python interacts with Oracle in this context. I added a semicolon when trying it on Oracle SQL Developer for testing purposes, but it should not cause an issue when removed for the .sql file.
Found the answer: In short, wrongtype error.
My code was passing in a datetime.date to a SQL to_date cast which was expecting a string.