where¶
You can use where
clauses with the following queries:
It allows powerful filtering of your data.
Equal / Not Equal¶
await Band.select().where(
Band.name == 'Pythonistas'
)
await Band.select().where(
Band.name != 'Rustaceans'
)
Hint
With Boolean
columns,
some linters will complain if you write
SomeTable.some_column == True
(because it’s more Pythonic to do
is True
). To work around this, you can do
SomeTable.some_column.eq(True)
. Likewise, with !=
you can use
SomeTable.some_column.ne(True)
Greater than / less than¶
You can use the <, >, <=, >=
operators, which work as you expect.
await Band.select().where(
Band.popularity >= 100
)
like
/ ilike
¶
The percentage operator is required to designate where the match should occur.
await Band.select().where(
Band.name.like('Py%') # Matches the start of the string
)
await Band.select().where(
Band.name.like('%istas') # Matches the end of the string
)
await Band.select().where(
Band.name.like('%is%') # Matches anywhere in the string
)
await Band.select().where(
Band.name.like('Pythonistas') # Matches the entire string
)
ilike
is identical, except it’s Postgres specific and case insensitive.
not_like
¶
Usage is the same as like
excepts it excludes matching rows.
await Band.select().where(
Band.name.not_like('Py%')
)
is_in
/ not_in
¶
You can get all rows with a value contained in the list:
await Band.select().where(
Band.name.is_in(['Pythonistas', 'Rustaceans'])
)
And all rows with a value not contained in the list:
await Band.select().where(
Band.name.not_in(['Terrible Band', 'Awful Band'])
)
is_null
/ is_not_null
¶
These queries work, but some linters will complain about doing a comparison
with None
:
# Fetch all bands with a manager
await Band.select().where(
Band.manager != None
)
# Fetch all bands without a manager
await Band.select().where(
Band.manager == None
)
To avoid the linter errors, you can use is_null
and is_not_null
instead.
# Fetch all bands with a manager
await Band.select().where(
Band.manager.is_not_null()
)
# Fetch all bands without a manager
await Band.select().where(
Band.manager.is_null()
)
Complex queries - and / or¶
You can make complex where
queries using &
for AND, and |
for OR.
await Band.select().where(
(Band.popularity >= 100) & (Band.popularity < 1000)
)
await Band.select().where(
(Band.popularity >= 100) | (Band.name == 'Pythonistas')
)
You can make really complex where
clauses if you so choose - just be
careful to include brackets in the correct place.
((b.popularity >= 100) & (b.manager.name == 'Guido')) | (b.popularity > 1000)
Multiple where
clauses¶
Using multiple where
clauses is equivalent to an AND.
# These are equivalent:
await Band.select().where(
(Band.popularity >= 100) & (Band.popularity < 1000)
)
await Band.select().where(
Band.popularity >= 100
).where(
Band.popularity < 1000
)
Also, multiple arguments inside where
clause is equivalent to an AND.
# These are equivalent:
await Band.select().where(
(Band.popularity >= 100) & (Band.popularity < 1000)
)
await Band.select().where(
Band.popularity >= 100, Band.popularity < 1000
)
Using And
/ Or
directly¶
Rather than using the |
and &
characters, you can use the And
and
Or
classes, which are what’s used under the hood.
from piccolo.columns.combination import And, Or
await Band.select().where(
Or(
And(Band.popularity >= 100, Band.popularity < 1000),
Band.name == 'Pythonistas'
)
)
WhereRaw
¶
In certain situations you may want to have raw SQL in your where clause.
from piccolo.columns.combination import WhereRaw
await Band.select().where(
WhereRaw("name = 'Pythonistas'")
)
It’s important to parameterise your SQL statements if the values come from an untrusted source, otherwise it could lead to a SQL injection attack.
from piccolo.columns.combination import WhereRaw
value = "Could be dangerous"
await Band.select().where(
WhereRaw("name = {}", value)
)
WhereRaw
can be combined into complex queries, just as you’d expect:
from piccolo.columns.combination import WhereRaw
await Band.select().where(
WhereRaw("name = 'Pythonistas'") | (Band.popularity > 1000)
)
Joins¶
The where
clause has full support for joins. For example:
>>> await Band.select(Band.name).where(Band.manager.name == 'Guido')
[{'name': 'Pythonistas'}]
Conditional where
clauses¶
You can add where
clauses conditionally (e.g. based on user input):
async def get_band_names(only_popular_bands: bool) -> list[str]:
query = Band.select(Band.name).output(as_list=True)
if only_popular_bands:
query = query.where(Band.popularity >= 1000)
return await query
Hint
This works with all clauses, not just where
clauses.