Menu
I basically just need to know how to query by excluding selected columns. Is this possible?
Example: I have table which has id, name, age, address, location, birth, age, sex... etc.
Instead of citing out the columns to retrieve, I'd like to just exclude some columns in the query(exclude age for example).
Sample code:
db.session.query(User.username).filter_by(username = request.form['username'], password = request.form['password']).first()
Last thing I wanna do is to list down all the attributes on the query() method, since this would be pretty long especially when you have lots of attributes, thus I just wanna exclude some columns.
806
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() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present.
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
Python Flask and SQLAlchemy ORM 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.
Not sure why you're not just fetching the model. When doing that, you can defer loading of certain columns so that they are only queried on access.
db.session.query(User).options(db.defer('location')).filter_by(...).first()
In this example, accessing User.location the first time on an instance will issue another query to get the data.
See the documentation on column deferral: http://sqlalchemy.readthedocs.org/en/rel_0_9/orm/mapper_config.html?highlight=defer#column-deferral-api
Note that unless you're loading huge amounts of data, you won't see any speedup with this. It might actually make things slower since another query will be issued later. I have queries that load thousands of rows with eager-loaded relationships in less than 200ms, so this might be a case of premature optimization.
120
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
