Oracle to_date within SQLAlchemy query

Question

Using plain Oracle SQL, I would use:

select *
from mytable
where 
to_date(date_d, 'dd-mon-yyyy') == to_date('01-Jan-2013', 'dd-mon-yyyy')

Now using SQLAlchemy, I'm at a loss.

from datetime import datetime

dt = myTable.c.date_d == datetime(2013, 1, 1)
q = session.query(myTable).filter(dt)
q.first()

Will give me incorrect results, because date_d is varchar, so it won't translate to date unless I run an Oracle function to convert the datatype within the query. How can I do that?

Step 1 - stop storing dates as text.

Dan Bracuk
– Dan Bracuk

2014-04-29 00:19:52 +00:00
Commented Apr 29, 2014 at 0:19 — Dan Bracuk
– Dan Bracuk, Commented Apr 29, 2014 at 0:19
@DanBracuk, consider this an intellectual exercise.

dmvianna
– dmvianna

2014-04-29 01:11:21 +00:00
Commented Apr 29, 2014 at 1:11 — dmvianna
– dmvianna, Commented Apr 29, 2014 at 1:11

dmvianna · Accepted Answer · 2014-04-29 01:13:08Z

2

Turns out I didn't need to convert the datatype. SQLAlchemy seems to do that transparently. However, If I feel like doing it explicitly:

from sqlalchemy import func

dt = func.to_date(myTable.c.date_d, 'dd-mon-yyyy') == datetime(2013, 1, 1)
q = session.query(dt)
q.first()

My incorrect results were due to an unrelated mistake.

edited Apr 29, 2014 at 1:13

answered Apr 29, 2014 at 0:31

dmvianna

15.8k18 gold badges81 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

Oracle to_date within SQLAlchemy query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related