CODEWITHSUNDEEP
pythondatetimesqlalchemy
Sarah
Sarah

Reputation: 367

Way to query database with SQLAlchemy where a date is a particular day of the week?

I have a table (called 'entry') which has a datetime column (called 'access_date'), and I want to do an SQLAlchemy query that only produces results where entry.access_date is a Monday (or any other day of the week specified by a number [0..6]).

Is this possible? I am using sqlite & SQLalchemy 0.5.8 if that makes any difference.

Upvotes: 5

Views: 4302

Answers (3)

Sarah
Sarah

Reputation: 367

Further from Daniel Kluev's answer, I found another way of saying the same thing (possibly nicer looking?)

query.filter(func.strftime('%w', Entry.access_date) == str(weekday)).all()

Where weekday is a number [0..6]

Upvotes: 3

Daniel Kluev
Daniel Kluev

Reputation: 11315

There is no generic DAYOFWEEK() function supported by SQLAlchemy, so you will have to use dialect-specific sql in the where clause.

For MySQL, you would go with custom func 'weekday' or 'dayofweek', but since sqlite has neither datetime type nor weekday()/dayofweek(), you need some raw sql there.

http://www.mail-archive.com/[email protected]/msg51116.html here are examples for this query.

In SQLA, this will look like

query.filter("strftime('%w', access_date) = :dow").params(dow=0).all()

Upvotes: 2

Tim McNamara
Tim McNamara

Reputation: 18385

I'm not an expert on SQLAlchemy, so await others' views. I've adapted an example from http://www.sqlalchemy.org/docs/ormtutorial.html#common-filter-operators. This may be worth testing & experimenting with while you wait for others' answers.:

query.filter(entry.access_date.in_([0,1,2,3,4,5,6]))

Upvotes: 0

Related Questions