def test_set_object_local_id(self): """ Test the method _set_object_local_id(self, obj, local_id) Test scenario: Set the local_id of the specified object when the pvc_id is none """ obj_id = self.powerVCMapping.id self.powerVCMapping.pvc_id = None self.powerVCMapping.local_id = None self.powerVCMapping.status = None self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(id=obj_id).AndReturn(query) self.aMox.StubOutWithMock(query, 'one') query.one().AndReturn(self.powerVCMapping) self.aMox.StubOutWithMock(session, 'merge') session.merge(self.powerVCMapping).AndReturn("") self.aMox.ReplayAll() self.powervcagentdb._set_object_local_id(self.powerVCMapping, 'test') self.aMox.VerifyAll() self.assertEqual(self.powerVCMapping.status, 'Creating') self.assertEqual(self.powerVCMapping.local_id, 'test') self.aMox.UnsetStubs()
def test_delete_existing_object(self): """ Test the method _delete_object(self, obj) when the object exists Test scenario: When the data is in the database, the delete operation should complete successfully """ self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(id=self.powerVCMapping['id']).AndReturn(query) self.aMox.StubOutWithMock(query, 'one') query.one().AndReturn(self.powerVCMapping) self.aMox.StubOutWithMock(session, 'begin') session.begin(subtransactions=True).AndReturn(transaction(None, None)) self.aMox.StubOutWithMock(session, 'delete') returnValue = session.delete(self.powerVCMapping).AndReturn(True) self.aMox.ReplayAll() self.powervcagentdb._delete_object(self.powerVCMapping) self.aMox.VerifyAll() self.assertEqual(returnValue, True) self.aMox.UnsetStubs()
def test_get_object(self): """ Test the method _get_object() using a sync key Test scenario: Get the object with sync_key """ obj_type = self.powerVCMapping.obj_type sync_key = self.powerVCMapping.sync_key self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(obj_type=obj_type, sync_key=sync_key).AndReturn(query) self.aMox.StubOutWithMock(query, 'one') query.one().AndReturn(self.powerVCMapping) self.aMox.ReplayAll() returnValue = self.powervcagentdb._get_object(obj_type=obj_type, sync_key=sync_key) self.aMox.VerifyAll() self.assertEqual(returnValue, self.powerVCMapping) self.aMox.UnsetStubs()
def test_get_object(self): """ Test the method _get_object() using a sync key Test scenario: Get the object with sync_key """ obj_type = self.powerVCMapping.obj_type sync_key = self.powerVCMapping.sync_key self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by( obj_type=obj_type, sync_key=sync_key).AndReturn(query) self.aMox.StubOutWithMock(query, 'one') query.one().AndReturn(self.powerVCMapping) self.aMox.ReplayAll() returnValue = self.powervcagentdb._get_object( obj_type=obj_type, sync_key=sync_key) self.aMox.VerifyAll() self.assertEqual(returnValue, self.powerVCMapping) self.aMox.UnsetStubs()
def filter_query(query: Query, **kwargs): """Filter an input query for the given kwargs. Args: query (Query): An existing query from the database. **kwargs: A column from the database. Returns: query (Query): The filtered query. """ for key, value in kwargs.items(): query = query.filter_by(**{key: value}) return query
def test_get_objects_with_status(self): """Test the method def _get_objects(self, obj_type, status) Test scenario: Get the object when the status is not None """ self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status).AndReturn(query) self.aMox.StubOutWithMock(query, 'all') query.all().AndReturn(self.powerVCMapping) self.aMox.ReplayAll() returnValue = self.powervcagentdb._get_objects( obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status) self.aMox.VerifyAll() self.assertEqual(returnValue, self.powerVCMapping) self.aMox.UnsetStubs()
def _apply_ip_or_cidr_filter(self, query: Query, ip_or_cidr: str) -> Query: """Apply an appropriate filter for an IP or CIDR block. If the underlying database is not PostgreSQL, which supports native CIDR membership queries, cheat and turn the CIDR block into a string wildcard. This will only work for CIDR blocks on class boundaries, but the intended supported database is PostgreSQL anyway. """ if "/" in ip_or_cidr: if self._session.get_bind().name == "postgresql": return query.filter(text(":c >> ip_address")).params( c=ip_or_cidr ) else: if ":" in str(ip_or_cidr): net = re.sub("::/[0-9]+$", ":%", ip_or_cidr) else: net = re.sub(r"(\.0)+/[0-9]+$", ".%", ip_or_cidr) return query.filter(TokenChangeHistory.ip_address.like(net)) else: return query.filter_by(ip_address=str(ip_or_cidr))
def _apply_kwargs(instance: Query, **kwargs) -> Query: for expression in kwargs.pop('filters', []): if _is_ordering_expression(expression): instance = instance.order_by(expression) else: instance = instance.filter(expression) if 'offset' in kwargs: offset = kwargs.pop('offset') foffset = lambda instance: instance.offset(offset) else: foffset = lambda instance: instance if 'limit' in kwargs: limit = kwargs.pop('limit') flimit = lambda instance: instance.limit(limit) else: flimit = lambda instance: instance instance = instance.filter_by(**kwargs) instance = foffset(instance) instance = flimit(instance) return instance
def default_query(qry: Query) -> Query: return qry.filter_by(is_sqllab_view=False)
# explict join using relationship Query(User).join(User.addresses) Query(User).join('addresses') Query(User).join(Address, User.addresses) # In join query, table on the left side of the join is decided # by the leftmost entity in the Query object's list of # entities by default. Use select_from() to specify a table. Query(User, Address).select_from(User).join(User.addresses) # left join Query(User).outerjoin(Address) # filter and filter_by, can be chained. query.filter_by(id=1) # uses keyword arguments query.filter(User.id == 1) # use SQL expression object query.filter(text("id<10")) # use textual SQL (query.filter(text("id<:value and name=:name")) # bind parameters .params(value=10, name='hello')) # other option query.order_by(User.id) query.distinct() query.limit() query.offset() # emit query and get result ################################# # the select Query instance will not emit a query until the
# 直到返回的不再是同一个对象或者方法 # 2,first 拿到查询的第一个结果 user = User.query.first() # 3,get方法 要通过主键去获取 主键唯一,是一个位置参数 user = User.query.get(5) # 位置传参,不能 # 表内有多个主键的情况下,使用元祖传入进去 # 或者使用字典 kv方式传入进去 # 如果没获取到,会返回一个None,获取到的话会返回一个模型 # 如果没返回数据,想要其报错的话, Query.get_or_404() # 4,filter_by() # 第一种 使用filter_by方法 Query.filter_by() # 5 filter() 进行更为复杂的查询 Query.filter() # 位置传参 User.query.filter(User.email.endswith("xxx")).all() # 支持字段很多,在ColumnOperators中进行确认 ColumnOperators # ----------------------------------------------------- app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) # 延迟绑定也是可以的,下看另一种用法 # db.init_app(app) # 初始化db绑定app,但是一定要在app路由生效之前(注意代码执行顺序) class User(db.Model):