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.
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.)
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.
_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).
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.
sqlalchemy.extract('year', model.MyRecord.dfield) == selected_year
For referene: https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.extract
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With