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"})