0

I am fetching data from SQL (Oracle and MS SQL both) databases from a python code using pyodbc and cxOracle packages. Python automatically converts all date time fields in SQL to datetime.datetime. Is there any way I can capture data as is from SQL into a file. Same happens to Null and integer columns as well. 1) Date: Value in DB and expected-- 12-AUG-19 12.00.01.000 -- Python Output: 2019-08-12 00:00:01 2) Null becomes a NaN 3) Integer value 1s and 0s becomes True and False. I tried to google the issue, and seems like a common issue amongst all packages like pyodbc, cx_oracle, pandas.read_sql as well. I would like the data appearing exactly the same as in the database.

We are calling a Oracle/SQL Server Stored proc and NOT a SQL query to get this result and we can't change the stored proc. We cannot use CAST in sql query.

Pyodbc fetchall() output is the table in list format. We lose the formatting of the data as soon as it is captured in python.

Could someone help with this issue?

learner
  • 1
  • I understand not being able to change the stored prod, but would you be able to use `CAST()` in a SQL command that your python code sends? – digital.aaron Feb 12 '20 at 17:16
  • The tables passed as parameter to the Stored proc is different each time hence the the columns would be different each time. – learner Feb 12 '20 at 17:44
  • Take a look at the answers here, in addition to the answer I've posted: https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table – digital.aaron Feb 12 '20 at 17:46
  • You could pull the data into a pandas DataFrame and then use the formatting options of [to_csv](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html) to format the values as desired. – Gord Thompson Feb 12 '20 at 19:31

1 Answers1

0

I'm not sure about Oracle, but on the SQL Server side, you could change the command you use so that you capture the results of the stored proc in a temp table, and then you can CAST() the columns of the temp table.

So if you currently call a stored proc on SQL Server like this: EXEC {YourProcName}

Then you could change your command to something like this: 

CREATE TABLE #temp 
(
    col1 INT
    ,col2 DATETIME
    ,col3 VARCHAR(20)
);

INSERT INTO #temp
EXEC [sproc];

SELECT 
    col1 = CAST(col1 AS VARCHAR(20))
    ,col2 = CAST(FORMAT(col2,'dd-MMM-yy ') AS VARCHAR) + REPLACE(CAST(CAST(col2 AS TIME(3)) AS VARCHAR),':','.')
    ,col3 
FROM #temp;

DROP TABLE #temp

You'll want to create your temp table using the same column names and datatypes that get output from the proc. Then you can CAST() numeric values to VARCHAR, and with dates/datetimes, you can use FORMAT() to define your date string format. The example I have here should result in format you want of 12-AUG-19 12.00.01.000. I couldn't find a single format string that gave me the correct output, so I broke the date and time elements apart, format them in the expected way, and then concatenate the casted values.

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • The tables passed as parameter to the Stored proc is different each time hence the the columns would be different each time. – learner Feb 12 '20 at 18:57