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_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_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_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_get_or_create_very_complex(self):
"""
Make sure `get_or_create` works with very complex where clauses.
"""
self.insert_rows()
# When the row already exists in the db:
instance = (Band.objects().get_or_create(
(Band.name == "Pythonistas")
& (Band.popularity > 0)
& (Band.popularity < 5000)).run_sync())
self.assertIsInstance(instance, Band)
self.assertEqual(instance._was_created, False)
# When the row doesn't exist in the db:
instance = (Band.objects().get_or_create(
(Band.name == "Pythonistas2")
& (Band.popularity > 10)
& (Band.popularity < 5000)).run_sync())
self.assertIsInstance(instance, Band)
self.assertEqual(instance._was_created, True)
# The values in the > and < should be ignored, and the default should
# be used for the column.
self.assertEqual(instance.popularity, 0)
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")
async def run_transaction():
try:
async with Band._meta.db.transaction():
Manager.create_table()
Band.create_table()
Band.raw("MALFORMED QUERY ... SHOULD ERROR")
except Exception:
pass
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_delete(self):
self.insert_rows()
Band.delete().where(Band.name == "CSharps").run_sync()
response = Band.count().where(Band.name == "CSharps").run_sync()
self.assertEqual(response, 0)
def test_validation(self):
"""
Make sure you can't delete all the data without forcing it.
"""
with self.assertRaises(DeletionError):
Band.delete().run_sync()
Band.delete(force=True).run_sync()
def test_set_default(self):
Manager.alter().set_default(Manager.name, "Pending").run_sync()
# Bypassing the ORM to make sure the database default is present.
Band.raw("INSERT INTO manager (id, name) VALUES (DEFAULT, DEFAULT);"
).run_sync()
manager = Manager.objects().first().run_sync()
self.assertEqual(manager.name, "Pending")
def _test_drop(self, column: str):
self.insert_row()
Band.alter().drop_column(column).run_sync()
response = Band.raw("SELECT * FROM band").run_sync()
column_names = response[0].keys()
self.assertNotIn("popularity", column_names)
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_all_columns_excluding(self):
self.assertEqual(
Band.all_columns(exclude=[Band.id]),
[Band.name, Band.manager, Band.popularity],
)
self.assertEqual(
Band.all_columns(exclude=["id"]),
[Band.name, Band.manager, Band.popularity],
)
def test_rmul(self):
self.insert_row()
Band.update({
Band.popularity: 2 * Band.popularity
}, force=True).run_sync()
response = Band.select(Band.popularity).first().run_sync()
self.assertEqual(response["popularity"], 2000)
def test_add(self):
self.insert_row()
Band.update({
Band.popularity: Band.popularity + 10
}, force=True).run_sync()
response = Band.select(Band.popularity).first().run_sync()
self.assertEqual(response["popularity"], 1010)
def test_update_values_with_kwargs(self):
"""
Make sure updating work, when passing the new values via kwargs.
"""
self.insert_rows()
Band.update().values(name="Pythonistas3").where(
Band.name == "Pythonistas").run_sync()
self.check_response()
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_succeeds(self):
transaction = Band._meta.db.atomic()
transaction.add(Manager.create_table(), Band.create_table())
transaction.run_sync()
self.assertTrue(Band.table_exists().run_sync())
self.assertTrue(Manager.table_exists().run_sync())
transaction.add(Band.alter().drop_table(),
Manager.alter().drop_table())
transaction.run_sync()
def _test_add_column(self, column: Column, column_name: str,
expected_value: t.Any):
self.insert_row()
Band.alter().add_column(column_name, column).run_sync()
response = Band.raw("SELECT * FROM band").run_sync()
column_names = response[0].keys()
self.assertIn(column_name, column_names)
self.assertEqual(response[0][column_name], expected_value)
def test_drop_tables(self):
"""
Make sure the tables are dropped.
"""
self.assertEqual(Manager.table_exists().run_sync(), True)
self.assertEqual(Band.table_exists().run_sync(), True)
drop_tables(Manager, Band)
self.assertEqual(Manager.table_exists().run_sync(), False)
self.assertEqual(Band.table_exists().run_sync(), False)
def test_update_values(self):
"""
Make sure updating work, when passing the new values via the `values`
method.
"""
self.insert_rows()
Band.update().values({
Band.name: "Pythonistas3"
}).where(Band.name == "Pythonistas").run_sync()
self.check_response()
def test_get__prefetch(self):
self.insert_rows()
# With prefetch clause
band = (Band.objects().get(
(Band.name == "Pythonistas")).prefetch(Band.manager).run_sync())
self.assertIsInstance(band.manager, Manager)
# Just passing it straight into objects
band = (Band.objects(Band.manager).get(
(Band.name == "Pythonistas")).run_sync())
self.assertIsInstance(band.manager, Manager)
def test_update_with_string_keys(self):
"""
Make sure updating work, when passing a dictionary of values, which
uses column names as keys, instead of Column instances.
"""
self.insert_rows()
Band.update({
"name": "Pythonistas3"
}).where(Band.name == "Pythonistas").run_sync()
self.check_response()
def test_set_length(self):
query = """
SELECT character_maximum_length FROM information_schema.columns
WHERE table_name = 'band'
AND table_catalog = 'piccolo'
AND column_name = 'name'
"""
for length in (5, 20, 50):
Band.alter().set_length(Band.name, length=length).run_sync()
response = Band.raw(query).run_sync()
self.assertEqual(response[0]["character_maximum_length"], length)
def test_update_values_with_string_keys(self):
"""
Make sure updating work, when passing the new values via the `values`
method, using a column name as a dictionary key.
"""
self.insert_rows()
Band.update().values({
"name": "Pythonistas3"
}).where(Band.name == "Pythonistas").run_sync()
self.check_response()
def test_insert_curly_braces(self):
"""
You should be able to insert curly braces without an error.
"""
self.insert_rows()
Band.insert(Band(name="{}", popularity=100)).run_sync()
response = Band.select(Band.name).run_sync()
names = [i["name"] for i in response]
self.assertIn("{}", names)
def setUp(self):
for table in self.tables:
table.create_table().run_sync()
manager_1 = Manager(name="Guido")
manager_1.save().run_sync()
band_1 = Band(
name="Pythonistas", manager=manager_1.id, popularity=1000
)
band_1.save().run_sync()
manager_2 = Manager(name="Graydon")
manager_2.save().run_sync()
band_2 = Band(name="Rustaceans", manager=manager_2.id)
band_2.save().run_sync()
venue = Venue(name="Grand Central", capacity=1000)
venue.save().run_sync()
concert = Concert(band_1=band_1.id, band_2=band_2.id, venue=venue.id)
concert.save().run_sync()
ticket = Ticket(concert=concert, price=decimal.Decimal(50.0))
ticket.save().run_sync()
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"})
