def _extract_simulation_template( self, session: Session, global_settings_id: Optional[int] = None ) -> SimulationTemplate: """ Extract a SimulationTemplate instance using the given database session """ qr = Query(GlobalSetting).with_session(session) if global_settings_id is not None: qr = qr.filter(GlobalSetting.id == global_settings_id) global_settings: GlobalSetting = qr.first() if global_settings is None: raise SchematisationError( f"Global settings with id: {global_settings_id} not found." ) dwf_laterals = DWFCalculator(session, global_settings.use_0d_inflow).laterals initial_waterlevels = InitialWaterlevelExtractor(session, global_settings_id) settings = SettingsExtractor(session, global_settings.id) return SimulationTemplate( events=Events( structure_controls=StructureControlExtractor( session, control_group_id=global_settings.control_group_id ).all_controls(), laterals=LateralsExtractor(session).as_list(), dwf_laterals=dwf_laterals, boundaries=BoundariesExtractor(session).as_list(), ), settings=settings.all_settings(), initial_waterlevels=initial_waterlevels.all_initial_waterlevels(), )
def query_iterator(query: Query): finished = False while True: item = query.first() if item is None: break yield item
def global_settings(self) -> GlobalSetting: if self._global_settings is None: qr = Query(GlobalSetting).with_session(self.session) if self._global_settings_id is not None: qr = qr.filter(GlobalSetting.id == self._global_settings_id) self._global_settings = qr.first() return self._global_settings
def execute_query(self, query: Query, use_list=True, count=False): """ Executes a new database query and return its result. :param query: The SQLAlchemy query object :param use_list: Flag to specify if want the returned elements as a list or not :param count: Flag to specify if we only want to count the rows that match the query :return: The query result """ try: if self._session_object is not None: query = query.with_session(self._session_object) if count: return query.count() elif use_list: return query.all() else: return query.first() except exc.SQLAlchemyError as e: self.app.logger.error( "Can't execute the requested query. Details: %s", str(e)) raise DBInternalError("Can't execute the requested query")
def apply_filter_from_string(model, query: Query, filters: Sequence[str]): for f in filters: as_lower = f.lower() if f == "": pass elif as_lower.startswith("order by"): attribute_name = as_lower.replace("order by", "") descending = False if attribute_name.endswith("desc") or attribute_name.endswith( "descending"): descending = True if attribute_name.endswith("desc"): attribute_name = attribute_name.replace("desc", "") else: attribute_name = attribute_name.replace("descending", "") to_order_on = getattr(model, attribute_name.strip()) if descending: to_order_on = to_order_on.desc() query = query.order_by(to_order_on) elif as_lower == "first": return query.first() elif as_lower.startswith("limit"): to_limit = as_lower.replace("limit", "") query = query.limit(int(to_limit.strip())) else: raise NotImplementedError(f"Have not implemented filter: {f}") return query.all()
def find_or_404(query: Query): instance = query.first() if instance: return instance raise NotFound()
(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 # following method invoked query.all() # fetch all as list query.first() # fetch one or None query.one() # fetch one or NoResultFound or MultipleResultsFound query.one_or_none() # fetch one or None or MultipleResultsFound query.scalar() # invokes the one() method and return the first column result = query[1:3] # list with limit and offset # batch operator ######################################### # batch update and delete will emit the query immediately, # return the number of row that changed. changed_number = (Query(User) .filter_by(name='a') .update({User.name: 'b'}, synchronize_session=False)) delete_number = Query(User).filter_by(ID=6).delete()
def first(self, query_builder: Query) -> Optional[Base]: return query_builder.first()
def query_user_by_phone(self, phone, **kwargs): query = Query(User, self.session).filter(User.phone == phone) return query.first()
def query_user_by_name(self, username, **kwargs): query = Query(User, self.session).filter(User.username == username) return query.first()
def query_user_by_id(self, user_id, **kwargs): query = Query(User, self.session).filter(User.user_id == user_id) return query.first()