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()
