Working with dates and timestamps using python and cx_oracle. Prefer to_date and to_timestamp with format strings. Don't like the alter session option. Some folks described problems with unicode so I added the unicode strings.

py_cx_ora_dates

#! /bin/python
import cx_Oracle
import platform

print ("Python version: " + platform.python_version())
print ("cx_Oracle version: " + cx_Oracle.version)
print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.'))

connection = cx_Oracle.connect('user/pass09@tns')
cursor = connection.cursor()
#
# Option with format strings
#
#cursor.execute("""select to_date(:arg1,'yyyy-mm-dd'), to_timestamp(:arg2,'yyyy-mm-dd hh24.mi.ss.ff') from dual""", arg1=u'2013-03-12', arg2=u'2013-03-12 08:22:31.332144')
#
# Option without format strings
#
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
cursor.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', u'2013-03-12 08:22:31.332144'])

mydate = cursor.fetchall()
print mydate
cursor.close()
connection.close()