On Nov 12, 12:24 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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 readingOracleDATE 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 oforacle.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.
I think there's clearly a bug in sqlalchemy's oracle.py file. I'm
looking at 0.3.11.
An Oracle DATE column can hold both a date and a time, so to map it to
a datetime.date() loses the time.
For example:
Create a table and insert a value into it:
CREATE TABLE datetest (dt DATE)
insert into datetest values ('2007-1-1 3:15')
select * from datetest
DT
---------------------
1/1/2007 3:15:00 AM
1 row selected
Describe the table, to make sure of the datatype:
describe datetest
Name Null? Type
-------------------------------- ------ -------------------------
DT Yes DATE
Run this script to read the value (using autoload):
import sqlalchemy as sa
engine = sa.create_engine('oracle://[EMAIL PROTECTED]',
connect_args={'password': 'password'})
metadata = sa.BoundMetaData(engine, case_sensitive=False)
conn = engine.connect()
tbl = sa.Table('datetest', metadata, autoload=True)
selector = sa.select([tbl.c.dt])
i = iter(conn.execute(selector))
print i.next()
The output is:
$ python sa.py
(datetime.date(2007, 1, 1),)
sqlalchemy is reading the table information with this:
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name and OWNER = :owner
which returns:
COLUMN_NAME DATA_TYPE
DATA_LENGTH DATA_PRECISION
DATA_SCALE N DATA_DEFAULT
------------------------------
---------------------------------------------------------------------
------------------------------------- ----------- --------------
---------- - ----------------------
----------------------------------------------------------
DT DATE
7 Y
So, the datatype is DATE, but it contains a date with a time.
Replacing OracleDate with OracleDateTime in oracle.py, and the script
produces:
$ python sa.py
(datetime.datetime(2007, 1, 1, 3, 15),)
Which is what I expect (and what I used to get in 0.3.8, although it's
locally patched and I can't swear that oracle.py wasn't patched for
some other reason).
For now, I'm just going to apply the OracleDateTime patch locally, but
it would be nice if this were fixed in sqlalchemy itself.
Thanks.
Eric.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
