On Nov 12, 2007, at 11:16 AM, Michael Schlenker wrote:
> > Hi all, > > I'm not sure if its a bug or an intended feature, but the default > behaviour > of sqlalchemy when reading Oracle DATE fields is annoying. cx_Oracle > rightfully returns datetime.datetime objects, but Sqlalchemy > truncates this > to datetime.date objects. > > Why is it done like this (in lib/sqlalchemy/databases/oracle.py: > 34-60)? > Wouldn't it be a better choice to default to OracleDateTime instead > of OracleDate > for queries without bound metadata? > > Its not a (major) problem when querying via a table object, where i > can override > the column type with a sane version (OracleDateTime), but for > queries directly > using conn.execute() its ugly. > > Basically this throws up: > > import sqlalchemy as sa > import datetime > engine = sa.create_engine('oracle://scott:[EMAIL PROTECTED]') > conn = engine.connect() > conn.execute('create table dtest (a DATE)') > # insert a row with date and time > now = datetime.datetime(2007,12,11,13,11,00) > conn.execute('insert into dtest values (:dt)', {'dt':now}) > # check its there > rows = conn.execute('select a from dtest where a=:dt',{'dt':now}) > for r in rows: > if rows[0]==now: > print "Found" > else: > print "Not Found" > > This prints 'Not Found' even though the row is there and is returned > correctly > by cx_Oracle. > > I would expect to get at least identity for this. > > So is this a bug and should i add a report or is it a 'feature' of > some kind and will > not change even if i report a bug? > your above test means to say "if r[0]==now:", else you get a runtime error. When I run it with that fix, the row matches and it prints "Found". its only when executing result-typed ClauseElement subclasses that any SQLAlchemy typing behavior, including the lines 34-60 of oracle.py, is applied, so your above test is not using any SA typing behavior at all, youre getting cx_oracle's results directly. In the case of Oracle, the Binary types break this rule right now but otherwise that's it. Also, OracleDate and OracleDateTime dont do anything at all to bind parameters; the only processing that occurs is OracleDate converts incoming result datetimes to dates. OracleDate and other types only get used for expressions that are typed ClauseElements (either table.select() or text() with a "typemap" parameter). The only way that an OracleDate is getting used in *those* cases is if you specified it explicitly or the table was reflected using autoload=True, which currently matches DATE to OracleDate, so im guessing that is the "default behavior" you are referring to. We might look into changing that to an OracleDateTime for autoload=True on the next release. In the meantime, override your reflected DATE column with an OracleDateTime type. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---