Getting the "wrong" type generated using SQLAlchemy 0.7.8 and Oracle XE

Question

Can someone tell me why the DateTime type below creates a DATE object, not a DateTime (or a more suitable TIMESTAMP type), since I need to force the type to enter, like in the following line:

#!/bin/python import sqlalchemy from sqlalchemy import Column, Integer, String, DateTime, Index, MetaData from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.orm import sessionmaker from sqlalchemy.dialects import oracle Base = declarative_base() import logging logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) class TypeTest(Base): __tablename__ = "TYPETESTZ" thisisinteger = Column(Integer, primary_key = True) thisisnotadatetime = Column(DateTime) thisisdatetime = Column(oracle.TIMESTAMP) if __name__ == "__main__": engine = sqlalchemy.create_engine('oracle://richard: password@xe ') metadata = Base.metadata metadata.create_all(engine) 

Log output:

 INFO:sqlalchemy.engine.base.Engine:SELECT USER FROM DUAL INFO:sqlalchemy.engine.base.Engine:{} INFO:sqlalchemy.engine.base.Engine:SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name INFO:sqlalchemy.engine.base.Engine:{'name': u'TYPETESTZ', 'schema_name': u'RICHARD'} INFO:sqlalchemy.engine.base.Engine: CREATE TABLE "TYPETESTZ" ( thisisinteger INTEGER NOT NULL, thisisnotadatetime DATE, thisisdatetime TIMESTAMP, PRIMARY KEY (thisisinteger) ) INFO:sqlalchemy.engine.base.Engine:{} INFO:sqlalchemy.engine.base.Engine:COMMIT 

Answer 1

Oracle does not have a DateTime data type. In Oracle, a Date contains both a day (i.e., August 21, 2012) and a time (e.g., 1:30 PM) with a granularity of 1 second. A Timestamp , without additional qualifiers, simply allows you to increase the grain size to nanoseconds (10 ^ -9 seconds). It seems reasonable that SQLAlchemy translates DateTime to Oracle Date unless there is a requirement that DateTime supports fractional seconds (in which case you will need a Timestamp ) or time zones (in which case you will need a Timestamp with [local] time zone ).

Answer 2

To create a TIMESTAMP object, you can use Time as

 from sqlalchemy import Column, Integer, String, Time, Index, MetaData 

Although DateTime in sqlalchemy will get TIMESTAMP with TIMEZONE, only if the TIMEZONE parameter is set to TRUE . The default is FALSE .

class sqlalchemy.types.DateTime (timezone = False )

init (timezone = False) Create a new DateTime.

Parameters: timezone - boolean. If True and the backend is supported, it will produce "TIMESTAMP WITH TIMEZONE". For backends that do not support timestamps, it is not affected.

Setting timezone=True should work, since Oracle supports Timestamp with a time zone, so you may need to set this.