The query object is an object that exists on a given model that we have defined in a SQLAlchemy application. This query object is essentially the source of all select statements that would be generated by the ORM, offering you the ability to return filtered slices of the data that comes from our database tables.
ClassName.query.filter_by(name='example')
ClassName.query.all()
ClassName.query.count()
ClassName.query.filter(ClassName.name == 'example')
ClassName.query.filter(ClassName.name == 'example', AnotherClassName.name == 'new_example')
ClassName.query.get(1) # gets by primary key
# Delete
ClassName.query.filter_by(category = 'Misc').delete()
Model.query method chaining
ClassName.query
.filter(ClassName.name == 'example')
.filter(Team.name == 'example')
.first()
ClassName.query
.join('another_className')
.filter_by(example_id=3)
.all()
Method chaining is essential for when we need to do things like joints and joined filtering.
There are two ways by which you can access the query object on a given model.
ClassName.query
#Same as
db.session.query(ClassName)
db.session.query(ClassName)
session.query(ClassName).join(AnotherClassName)
ClassName.query
.filter(ClassName.name == 'example')
.filter(AnotherClassName.name == 'another_example')
.first()
# Same as
db.session.query(ClassName)
.filter(ClassName.name == 'example')
.filter(AnotherClassName.name == 'another_example')
.first()
Query Methods
MyModel.query.all()
same as doing a ’ SELECT * ', fetching all records from the model’s table. Returns a list of objects.
first()
MyModel.query.first()
Fetches just the first result. Return either None or an object if found
MyModel.query.filter_by(my_table_attribute='some value')
Similar to doing a SELECT * from … WHERE SQL statement for filtering data by named attributes.
filter
#Example
MyModel.query.filter(MyOtherModel.some_attr='some value')
OrderItem.query.filter(Product.id=3)
Similar to filter_by, but instead, you specify attributes on a given Model. It is more flexible than using filter_by itself, and is especially useful when querying from a joined table where you want to filter by attributes that span across multiple models.
equals: query.filter(User.name == 'ed')
not equals: query.filter(User.name != 'ed')
LIKE: query.filter(User.name.like('%ed%'))
ILIKE (case-insensitive LIKE): query.filter(User.name.ilike('%ed%'))
IN: query.filter(User.name.in_(['ed', 'wendy', 'jack']))
NOT IN: query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
IS NULL: query.filter(User.name == None)
IS NOT NULL: query.filter(User.name != None)
AND: query.filter(User.name == 'ed', User.fullname == 'Ed Jones') or chain filter methods together
OR: query.filter(User.name == 'a' | User.name == 'b')
MATCH: query.filter(User.name.match('wendy'))
MyModel.order_by(MyModel.created_at)
MyModel.order_by(db.desc(MyModel.created_at))
To order the results by a given attribute. Use db.desc to order in descending order.
limit
Order.query.limit(100).all()
limit(max_num_rows) limits the number of returned records from the query. ala LIMIT in SQL.
query = Task.query.filter(completed=True)
query.count()
Returns an integer set to the number of records that would have been returned by running the query.
get()
Get object by ID
model_id = 3
MyModel.query.get(model_id)
Returns the object as a result of querying the model by its primary key.
query = Task.query.filter_by(category='Archived')
query.delete()
delete() does a bulk delete operation that deletes every record matching the given query.
Driver.query.join('vehicles')
Query has a method join(<table_name>) for joining one model to another table.
In Summary