Python csv_writer: Change the output format of a oracle date column

Question

I have a table with a date column and want to format that column to DD.MM.YYYYY in a csv file but alter session does not effect the python csv_writer.

Is there a way to handle all date columns without using to_char in the sql code?

file_handle=open("test.csv","w")
csv_writer = csv.writer(file_handle,dialect="excel",lineterminator='\n',delimiter=';',quoting=csv.QUOTE_NONNUMERIC)
conn=cx_Oracle.connect(connectionstring)
cur = conn.cursor()

cur.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'")
cur.execute("select attr4,to_char(attr4,'DD.MM.YYYY') from aTable")
rows = cur.fetchmany(16000)

while len(rows) > 0:
    csv_writer.writerows(rows)
    rows = cur.fetchmany(16000)
cur.close()

result:

"1943-04-21 00:00:00";"21.04.1943"
"1955-12-22 00:00:00";"22.12.1955"
"1947-11-01 00:00:00";"01.11.1947"
"1960-01-07 00:00:00";"07.01.1960"
"1979-12-01 00:00:00";"01.12.1979"

Todor Minakov · Accepted Answer · 2018-05-16 05:31:40Z

1

The output you see comes from the fact the result of a query is converted to the corresponding python datatypes - thus the values of the first column are datetime objects, and the second - strings (due to the to_char() cast you do in the query). The NLS_DATE_FORMAT controls the output for just regular (user) clients.
Thus the output in the csv is just the default representation of the python's datetime; if you want to output in a different form, you just need to change it.

As the query response is a list of tuples, you can't just change it in-place - it has to be copied and modified; alternatively, you could write it row by row, modified.

Here's just the write part with the 2nd approach:

import datetime

# the rest of your code

while len(rows) > 0:
    for row in rows:
        value = (row[0].strftime('%d.%m.%Y'), row[1])
        csv_writer.writerow(value)
    rows = cur.fetchmany(16000)

For reference, here's a short list with the python's strftime directives.

answered May 16, 2018 at 5:31

Todor Minakov

20.2k3 gold badges61 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anthony Tuininga Over a year ago

You should just iterate over the cursor instead of using cur.fetchmany(). That avoids the need to build up a list of 16,000 entries!

Todor Minakov Over a year ago

There could be different optimizations done, yet that's not the point of the OP's question - it's for a specific topic.

Anthony Tuininga Over a year ago

True enough. :-)

Collectives™ on Stack Overflow

Python csv_writer: Change the output format of a oracle date column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related