Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to filter query in sqlalchemy by year (datetime column)

I have table in sqlalchemy 0.4 that with types.DateTime column:

Column("dfield", types.DateTime, index=True)

I want to select records, that has specific year in this column, using model. How to do this? I though it should be done like this:

selected_year = 2009
my_session = model.Session()
my_query = my_session.query(model.MyRecord).filter(model.dfield.??? == selected_year)

# process data in my_query

Part with ??? is for me unclear.

like image 315
Jiri Avatar asked Sep 21 '09 09:09

Jiri


People also ask

What is difference between filter and filter by in SQLAlchemy?

The second one, filter_by(), may be used only for filtering by something specifically stated - a string or some number value. So it's usable only for category filtering, not for expression filtering. On the other hand filter() allows using comparison expressions (==, <, >, etc.)

How does the querying work with SQLAlchemy?

All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.

What is _sa_instance_state in SQLAlchemy?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance. While not directly relevant to this section, if we want to get at it, we should use the inspect() function to access it).

What is all () in SQLAlchemy?

method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement. The Query object, when asked to return either a sequence or iterator that consists of full ORM-mapped entities, will deduplicate entries based on primary key.


1 Answers

sqlalchemy.extract('year', model.MyRecord.dfield) == selected_year

For referene: https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.extract

like image 166
Ants Aasma Avatar answered Sep 17 '22 15:09

Ants Aasma