def test_where_ilike_postgres(self):
"""
Only Postgres has ILIKE - it's not in the SQL standard. It's for
case insensitive matching, i.e. 'Foo' == 'foo' -> True.
"""
self.insert_rows()
for ilike_query in (
"Python%",
"Pythonistas",
"pythonistas",
"PytHonIStas",
"%istas",
"%ist%",
"%IST%",
):
response = (Band.select(Band.name).where(
Band.name.ilike(ilike_query)).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
for ilike_query in ("Pythonistas1", "%123", "%xyz%"):
response = (Band.select(Band.name).where(
Band.name.ilike(ilike_query)).run_sync())
self.assertEqual(response, [])
def test_where_is_not_null(self):
self.insert_rows()
Band(name="Managerless", popularity=0, manager=None).save().run_sync()
queries = (
Band.select(Band.name).where(Band.manager != None), # noqa
Band.select(Band.name).where(Band.manager.is_not_null()),
)
for query in queries:
response = query.run_sync()
self.assertEqual(
response,
[
{
"name": "Pythonistas"
},
{
"name": "Rustaceans"
},
{
"name": "CSharps"
},
],
)
def test_where_like_sqlite(self):
"""
SQLite's LIKE is case insensitive for ASCII characters,
i.e. a == 'A' -> True.
"""
self.insert_rows()
for like_query in (
"Python%",
"Pythonistas",
"%istas",
"%ist%",
"PYTHONISTAS",
):
response = (Band.select(Band.name).where(
Band.name.like(like_query)).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
for like_query in (
"xyz",
"XYZ%",
"%xyz",
"%xyz%",
):
response = (Band.select(Band.name).where(
Band.name.like(like_query)).run_sync())
self.assertEqual(response, [])
def test_select_all_columns_exclude(self):
"""
Make sure we can get all columns, except the ones we specify.
"""
result = (
Band.select(
Band.all_columns(exclude=[Band.id]),
Band.manager.all_columns(exclude=[Band.manager.id]),
)
.output(nested=True)
.first()
.run_sync()
)
result_str_args = (
Band.select(
Band.all_columns(exclude=["id"]),
Band.manager.all_columns(exclude=["id"]),
)
.output(nested=True)
.first()
.run_sync()
)
for data in (result, result_str_args):
self.assertDictEqual(
data,
{
"name": "Pythonistas",
"manager": {"name": "Guido"},
"popularity": 1000,
},
)
def test_where_equals(self):
self.insert_row()
manager = Manager.objects().first().run_sync()
# This is the recommended way of running these types of queries:
response = (Band.select(Band.name).where(Band.manager.id == getattr(
manager, Band._meta.primary_key._meta.name)).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
# Other cases which should work:
response = (Band.select(Band.name).where(Band.manager == getattr(
manager, Manager._meta.primary_key._meta.name)).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
response = (Band.select(Band.name).where(Band.manager.id == getattr(
manager, Manager._meta.primary_key._meta.name)).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
# check multiple arguments inside WHERE clause
response = (Band.select(Band.name).where(
Band.manager.id == 1, Band.popularity == 500).run_sync())
self.assertEqual(response, [])
# check empty WHERE clause
response = Band.select(Band.name).where().run_sync()
self.assertEqual(response, [{"name": "Pythonistas"}])
def test_where_bool(self):
"""
If passing a boolean into a where clause, an exception should be
raised. It's possible for a user to do this by accident, for example
``where(Band.has_drummer is None)``, which evaluates to a boolean.
"""
with self.assertRaises(ValueError):
Band.select().where(False)
def test_output_as_list(self):
self.insert_row()
response = Band.select(Band.name).output(as_list=True).run_sync()
self.assertEqual(response, ["Pythonistas"])
# Make sure that if no rows are found, an empty list is returned.
empty_response = (Band.select(Band.name).where(
Band.name == "ABC123").output(as_list=True).run_sync())
self.assertEqual(empty_response, [])
def test_save_specific_columns(self):
"""
Make sure that we can save a subset of columns.
"""
manager = Manager(name="Guido")
manager.save().run_sync()
band = Band(name="Pythonistas", popularity=1000, manager=manager)
band.save().run_sync()
self.assertEqual(
Band.select().run_sync(),
[{
"id": 1,
"name": "Pythonistas",
"manager": 1,
"popularity": 1000,
}],
)
band.name = "Pythonistas 2"
band.popularity = 2000
band.save(columns=[Band.name]).run_sync()
# Only the name should update, and not the popularity:
self.assertEqual(
Band.select().run_sync(),
[{
"id": 1,
"name": "Pythonistas 2",
"manager": 1,
"popularity": 1000,
}],
)
#######################################################################
# Also test it using strings to identify columns
band.name = "Pythonistas 3"
band.popularity = 3000
band.save(columns=["popularity"]).run_sync()
# Only the popularity should update, and not the name:
self.assertEqual(
Band.select().run_sync(),
[{
"id": 1,
"name": "Pythonistas 2",
"manager": 1,
"popularity": 3000,
}],
)
def test_columns(self):
"""
Make sure the colums method can be used to specify which columns to
query.
"""
self.insert_rows()
response = (Band.select().columns(
Band.name).where(Band.name == "Pythonistas").first().run_sync())
self.assertEqual(response, {"name": "Pythonistas"})
# Multiple calls to 'columns' should be additive.
response = (Band.select().columns(Band._meta.primary_key).columns(
Band.name).where(Band.name == "Pythonistas").first().run_sync())
self.assertEqual(response, {"id": 1, "name": "Pythonistas"})
def test_sum_alias(self):
self.insert_rows()
response = (Band.select(Sum(
Band.popularity, alias="popularity_sum")).first().run_sync())
self.assertEqual(response["popularity_sum"], 3010)
def test_min_alias(self):
self.insert_rows()
response = (Band.select(Min(
Band.popularity, alias="popularity_min")).first().run_sync())
self.assertEqual(response["popularity_min"], 10)
def test_max_alias(self):
self.insert_rows()
response = (Band.select(Max(
Band.popularity, alias="popularity_max")).first().run_sync())
self.assertEqual(response["popularity_max"], 2000)
def test_avg_with_where_clause(self):
self.insert_rows()
response = (Band.select(Avg(
Band.popularity)).where(Band.popularity > 500).first().run_sync())
self.assertEqual(response["avg"], 1500)
def test_where_greater_than(self):
self.insert_rows()
response = (Band.select(
Band.name).where(Band.popularity > 1000).run_sync())
self.assertEqual(response, [{"name": "Rustaceans"}])
def test_count_with_as_alias_group_by(self):
"""
Test grouping and counting all rows with as_alias.
"""
self.insert_rows()
self.insert_rows()
response = (Band.select(Band.name,
Count().as_alias("total")).group_by(
Band.name).order_by(Band.name).run_sync())
self.assertEqual(
response,
[
{
"name": "CSharps",
"total": 2
},
{
"name": "Pythonistas",
"total": 2
},
{
"name": "Rustaceans",
"total": 2
},
],
)
def test_where_less_than(self):
self.insert_rows()
response = (Band.select(
Band.name).where(Band.popularity < 1000).run_sync())
self.assertEqual(response, [{"name": "CSharps"}])
def test_order_by_ascending(self):
self.insert_rows()
response = (Band.select(Band.name).order_by(
Band.name).limit(1).run_sync())
self.assertEqual(response, [{"name": "CSharps"}])
def test_first(self):
self.insert_rows()
response = (Band.select(Band.name).order_by(
Band.name).first().run_sync())
self.assertEqual(response, {"name": "CSharps"})
def test_sum_with_where_clause(self):
self.insert_rows()
response = (Band.select(Sum(
Band.popularity)).where(Band.popularity > 500).first().run_sync())
self.assertEqual(response["sum"], 3000)
def test_frozen_performance(self):
"""
The frozen query performance should exceed the non-frozen. If not,
there's a problem.
Only test this on SQLite, as the latency from the database itself
is more predictable than with Postgres, and the test runs quickly.
"""
iterations = 50
query = Band.select().where(Band.name == "Pythonistas").first()
query_duration = timeit.repeat(lambda: query.run_sync(),
repeat=iterations,
number=1)
frozen_query = query.freeze()
frozen_query_duration = timeit.repeat(lambda: frozen_query.run_sync(),
repeat=iterations,
number=1)
# Remove the outliers before comparing
self.assertGreater(
sum(sorted(query_duration)[5:-5]),
sum(sorted(frozen_query_duration)[5:-5]),
)
def test_order_by_decending(self):
self.insert_rows()
response = (Band.select(Band.name).order_by(
Band.name, ascending=False).limit(1).run_sync())
self.assertEqual(response, [{"name": "Rustaceans"}])
def test_avg_alias(self):
self.insert_rows()
response = (Band.select(Avg(
Band.popularity, alias="popularity_avg")).first().run_sync())
self.assertEqual(float(response["popularity_avg"]), 1003.3333333333334)
def test_as_alias(self):
"""
Make sure we can specify aliases for the columns.
"""
self.insert_row()
response = Band.select(Band.name.as_alias("title")).run_sync()
self.assertEqual(response, [{"title": "Pythonistas"}])
def test_radd(self):
self.insert_row()
Band.update({Band.name: "!!!" + Band.name}, force=True).run_sync()
response = Band.select(Band.name).first().run_sync()
self.assertEqual(response["name"], "!!!Pythonistas")
def test_where_and(self):
self.insert_rows()
response = (Band.select(
Band.name).where((Band.popularity <= 1000)
& (Band.name.like("Python%"))).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
def test_chain_different_functions(self):
self.insert_rows()
response = (Band.select(Avg(Band.popularity),
Sum(Band.popularity)).first().run_sync())
self.assertEqual(float(response["avg"]), 1003.3333333333334)
self.assertEqual(response["sum"], 3010)
def test_as_alias_with_join(self):
"""
Make sure we can specify aliases for the column, when performing a
join.
"""
self.insert_row()
response = Band.select(
Band.manager.name.as_alias("manager_name")).run_sync()
self.assertEqual(response, [{"manager_name": "Guido"}])
def test_attribute_access(self):
"""
Once frozen, you shouldn't be able to call additional methods on the
query (for example `.where`).
"""
query = Band.select().freeze()
with self.assertRaises(AttributeError):
query.where(Band.name == "Pythonistas")
def test_insert(self):
self.insert_rows()
Band.insert(Band(name="Rustaceans", popularity=100)).run_sync()
response = Band.select(Band.name).run_sync()
names = [i["name"] for i in response]
self.assertIn("Rustaceans", names)
def test_where_raw(self):
"""
Make sure raw SQL passed in to a where clause works as expected.
"""
self.insert_rows()
response = (Band.select(Band.name).where(
WhereRaw("name = 'Pythonistas'")).run_sync())
self.assertEqual(response, [{"name": "Pythonistas"}])
