async def get_report_content_all(
query: Query = Depends(_get_report_content_query)):
try:
contentsall = query.all()
return contentsall
except:
raise HTTPException(status_code=400)
async def get_my_reports(
query:Query=Depends(get_current_users_reports)
):
try:
return query.all()
except:
raise HTTPException(status_code=400)
async def get_my_headers(
headers: Query = Depends(get_current_users_header_query)
):
try:
return headers.all()
except:
raise HTTPException(status_code=400)
def query_paginate(query: Query, page: int, limit: int) -> Tuple[list, int]:
if limit:
page_ob = query.paginate(page=page, per_page=limit, error_out=False)
result = page_ob.items
count = page_ob.total
else:
result = query.all()
count = len(result)
return result, count
def get_paginated_result(query: Query, skip: int, limit: int):
count = query.order_by(None).count()
query = query.offset(skip)
if limit >= 0:
query = query.limit(limit)
return {
'pagination': {'skip': skip, 'limit': limit, 'all_records_count': count},
'result': query.all(),
}
def clone(db: Session, query: Query):
source_obj = query.all()[0]
db.expunge(source_obj) # expunge the object from session
make_transient(
source_obj
) # http://docs.sqlalchemy.org/en/rel_1_1/orm/session_api.html#sqlalchemy.orm.session.make_transient
delattr(source_obj, 'id')
return source_obj
def __get_query_result(self,
query: Query) -> Tuple[List[Tuple[Any]], List[str]]:
"""Fetch query result and returns result and column names of query. Ensures that query result is in form
List of Tuples."""
columns_names = self.__get_columns_name(query)
result = query.all()
if not self.__is_proper_format(result):
result = self.__to_list_of_tuples(result)
return result, columns_names
def list_query(self, query: Query, **kwargs):
_limit = kwargs.pop('limit', None)
_offset = kwargs.pop('offset', None)
if _limit:
query = query.limit(_limit)
if _offset:
query = query.offset(_offset)
logger.debug(f'list_query query: {render_query(query, self.session)}')
return query.all()
def get_eligible_facts(self,
query: Query,
skip: int = None,
limit: int = None) -> List[models.Fact]:
begin_overall_start = time.time()
if skip:
query = query.offset(skip)
if limit:
query = query.limit(limit)
facts = query.all()
overall_end_time = time.time()
overall_total_time = overall_end_time - begin_overall_start
logger.info("overall time facts: " + str(overall_total_time))
return facts
def fetch_all(cls,
session: Session,
limit: int,
offset: int,
company_id: int = None) -> List['Match']:
query = Query(cls, session=session)
if company_id:
query = query.filter(
or_(Match.left_company_id == company_id,
Match.right_company_id == company_id))
query = query.limit(limit).offset(offset)
return query.all()
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 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 get_or_404(query: Query, query_type: str = "one"):
"""
.. todo :: use flask-sqlalchemy's get_or_404 instead
:param query: The queryset that should be queried. E.g.
``session.db.query(Model).filter(something=='Foo')``
:param query_type: String identifier for method. Either ``one`` or ``all``
"""
try:
if query_type == "one":
return query.one()
elif query_type == "all":
return query.all()
else:
raise ValueError(f"Unexpected type {query_type}. Allowed types: one, all")
except NoResultFound:
raise NotFound()
def fetch_as_tree(query: Query,
relationship: Optional[InstrumentedAttribute] = None,
root_value: Union[str, None] = None,
) -> List[DeclarativeMeta]:
"""Builds the full tree for the given table (adjacency list).
This method is efficient in the sense that it issues only one SELECT
query to the database.
Args:
query: A query that selects all relevant model records, that
is, all records that make up the adjacency list. For
example: `session.query(Album).order_by(Album.nameLC)`.
relationship: The parent/child relationship that describes
both fields (columns). For example: `Album.parent`
If not set, one record is fetched from the query to read
its `parent` property.
root_value: Parent property's value at the root nodes.
Default is None, which commonly retrieves the whole
structure.
Returns:
A list of root nodes with child nodes (the tree) pre-fetched
recursively.
"""
if relationship is None:
# Fetch one record to discover the parent relationship.
relationship = next(iter(query)).__class__.parent
parent_field = relationship.expression.right.name
child_field = relationship.expression.left.name
back_populates = relationship.property.back_populates
nodes = query.all()
children = defaultdict(list)
for node in nodes:
children[getattr(node, parent_field)].append(node)
for node in nodes:
set_committed_value(node, back_populates,
children[getattr(node, child_field)])
return children[root_value]
def get_all(instance: Query, offset: int=None, limit: int=None, filters: list=()) -> list:
"""
Gets all instances of the query instance
:param instance: sqlalchemy queriable
:param offset: Offset for request
:param limit: Limit for request
:param filters: Filters and OrderBy Clauses
"""
for expression in filters:
if _is_ordering_expression(expression):
instance = instance.order_by(expression)
else:
instance = instance.filter(expression)
if offset is not None:
instance = instance.offset(offset)
if limit is not None:
instance = instance.limit(limit)
return instance.all()
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 orm_get_rows(self,
table_name,
filter_text=None,
distinct=False,
delete=False):
"""
Returns all rows from selected columns in a table, provides options to filter your query and return only
distinct values.
Parameters
----------
table_name: str | DeclarativeMeta
The table name you want to search within. Alternatively an ORM model may be passed directly.
filter_text: str | dict
Text that you want to filter source by. Allows a dict of multiple filter texts to be passed.
distinct: bool
True indicates you only want distinct source without duplicates.
delete : bool
True indicates you want to delete all returned rows from the target table.
Returns
----------
source : list
"""
self.session = Session(bind=self.sa_engine)
if type(table_name) == str:
table_model = self.reflect_database_table(table_name=table_name)
else:
table_model = table_name
if filter_text is None:
if distinct:
results = Query(table_model, self.session).distinct().all()
else:
results = Query(table_model, self.session).all()
elif type(filter_text) == dict:
query = Query(table_model, self.session)
for attr, value in filter_text.items():
if value == '':
pass
else:
query = Query(table_model, self.session).filter(
getattr(table_model, attr) == value, )
if distinct:
results = query.distinct().all()
else:
results = query.all()
elif type(filter_text) == BinaryExpression:
if distinct:
results = Query(
table_model,
self.session).filter(filter_text).distinct().all()
else:
results = Query(table_model,
self.session).filter(filter_text).all()
else:
if distinct:
results = Query(
table_model,
self.session).filter(filter_text).distinct().all()
else:
results = Query(table_model,
self.session).filter(filter_text).all()
if delete and len(results) > 0:
print(
f'Attempting to delete {len(results)} from {table_name.name}.')
try:
if filter_text is None:
Query(table_model,
self.session).delete(synchronize_session=False)
else:
Query(table_model,
self.session).filter(filter_text).delete(
synchronize_session=False)
self.session.commit()
return
except Exception as e:
print(
f'ERROR:: The delete operation was unsuccessful, operation aborted.'
)
self.session.rollback()
raise DbObjectError(e)
return results
def entity_search_execute_orm(query: Query):
"""
Execute query
:param query: query to execute
"""
return query.all()
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
# 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 all(self, query_builder: Query) -> List[Base]:
return query_builder.all()
def _query_with_filters(
response: Response,
query: Query,
range: Optional[List[int]] = None,
sort: Optional[List[str]] = None,
filters: Optional[List[str]] = None,
) -> List:
if filters is not None:
for filter in chunked(filters, 2):
if filter and len(filter) == 2:
field = filter[0]
value = filter[1]
value_as_bool = value.lower() in ("yes", "y", "ye", "true",
"1", "ja", "insync")
if value is not None:
if field.endswith("_gt"):
query = query.filter(
SubscriptionTable.__dict__[field[:-3]] > value)
elif field.endswith("_gte"):
query = query.filter(
SubscriptionTable.__dict__[field[:-4]] >= value)
elif field.endswith("_lte"):
query = query.filter(
SubscriptionTable.__dict__[field[:-4]] <= value)
elif field.endswith("_lt"):
query = query.filter(
SubscriptionTable.__dict__[field[:-3]] < value)
elif field.endswith("_ne"):
query = query.filter(
SubscriptionTable.__dict__[field[:-3]] != value)
elif field == "insync":
query = query.filter(
SubscriptionTable.insync.is_(value_as_bool))
elif field == "tags":
# For node and port selector form widgets
sub_values = value.split("-")
query = query.filter(
func.lower(ProductTable.tag).in_(
[s.lower() for s in sub_values]))
elif field == "tag":
# For React table 7
sub_values = value.split("-")
query = query.filter(
func.lower(ProductTable.tag).in_(
[s.lower() for s in sub_values]))
elif field == "product":
sub_values = value.split("-")
query = query.filter(
func.lower(ProductTable.name).in_(
[s.lower() for s in sub_values]))
elif field == "status":
# For React table 7
statuses = value.split("-")
query = query.filter(
SubscriptionTable.status.in_(
[s.lower() for s in statuses]))
elif field == "statuses":
# For port subscriptions
sub_values = value.split("-")
query = query.filter(
SubscriptionTable.status.in_(
[s.lower() for s in sub_values]))
elif field == "organisation":
try:
value_as_uuid = UUID(value)
except (ValueError, AttributeError):
msg = "Not a valid customer_id, must be a UUID: '{value}'"
logger.exception(msg)
raise_status(HTTPStatus.BAD_REQUEST, msg)
query = query.filter(
SubscriptionTable.customer_id == value_as_uuid)
elif field == "tsv":
logger.debug("Running full-text search query.",
value=value)
query = query.search(value)
elif field in SubscriptionTable.__dict__:
query = query.filter(
cast(SubscriptionTable.__dict__[field],
String).ilike("%" + value + "%"))
if sort is not None and len(sort) >= 2:
for item in chunked(sort, 2):
if item and len(item) == 2:
if item[0] in ["product", "tag"]:
field = "name" if item[0] == "product" else "tag"
if item[1].upper() == "DESC":
query = query.order_by(
expression.desc(ProductTable.__dict__[field]))
else:
query = query.order_by(
expression.asc(ProductTable.__dict__[field]))
else:
if item[1].upper() == "DESC":
query = query.order_by(
expression.desc(
SubscriptionTable.__dict__[item[0]]))
else:
query = query.order_by(
expression.asc(
SubscriptionTable.__dict__[item[0]]))
if range is not None and len(range) == 2:
try:
range_start = int(range[0])
range_end = int(range[1])
if range_start >= range_end:
raise ValueError("range start must be lower than end")
except (ValueError, AssertionError):
msg = "Invalid range parameters"
logger.exception(msg)
raise_status(HTTPStatus.BAD_REQUEST, msg)
total = query.count()
query = query.slice(range_start, range_end)
response.headers[
"Content-Range"] = f"subscriptions {range_start}-{range_end}/{total}"
return query.all()
def _query_with_filters(
response: Response,
model: BaseModel,
query: Query,
range: Optional[List[int]] = None,
sort: Optional[List[str]] = None,
filters: Optional[List[str]] = None,
) -> List:
if filters is not None:
for filter in chunked(filters, 2):
if filter and len(filter) == 2:
field = filter[0]
value = filter[1]
value_as_bool = value.lower() in (
"yes",
"y",
"ye",
"true",
"1",
"ja",
"insync",
)
if value is not None:
if field.endswith("_gt"):
query = query.filter(
model.__dict__[field[:-3]] > value)
elif field.endswith("_gte"):
query = query.filter(
model.__dict__[field[:-4]] >= value)
elif field.endswith("_lte"):
query = query.filter(
model.__dict__[field[:-4]] <= value)
elif field.endswith("_lt"):
query = query.filter(
model.__dict__[field[:-3]] < value)
elif field.endswith("_ne"):
query = query.filter(
model.__dict__[field[:-3]] != value)
elif field == "tsv":
logger.debug("Running full-text search query.",
value=value)
query = query.search(value)
elif field in model.__dict__:
query = query.filter(
cast(model.__dict__[field],
String).ilike("%" + value + "%"))
if sort is not None and len(sort) >= 2:
for sort in chunked(sort, 2):
if sort and len(sort) == 2:
if sort[1].upper() == "DESC":
query = query.order_by(
expression.desc(model.__dict__[sort[0]]))
else:
query = query.order_by(
expression.asc(model.__dict__[sort[0]]))
if range is not None and len(range) == 2:
try:
range_start = int(range[0])
range_end = int(range[1])
assert range_start < range_end
except (ValueError, AssertionError):
msg = "Invalid range parameters"
logger.exception(msg)
raise_status(HTTPStatus.BAD_REQUEST, msg)
total = query.count()
query = query.slice(range_start, range_end)
response.headers[
"Content-Range"] = f"items {range_start}-{range_end}/{total}"
return query.all()
def q_all_tq(query: Query):
count = query.count()
all = query.all()
tq = tqdm(all, total=count, smoothing=0.05)
return tq
def make_flashcard_run(endpoint: str,
query: Query,
sorting: FlashcardSorting = FlashcardSorting.NONE,
start_at_word_id: Optional[SupportsInt] = None) \
-> Response:
"""Uses a query to fetch a set of words from the database, and
redirects to the correct endpoint.
Parameters
----------
endpoint : str
Endpoint to be redirected to after words have been fetched.
query : sqlalchemy.orm.Query
Query used to retrieve words from the database.
sorting : FlashcardSorting
In what order should the words be presented. See documentation
for FlashcardSorting for more information.
start_at_word_id : int | str | None
ID of the word to display first. If not specified, start with
the first word available. If you specify some integer, make
sure the word with that ID is actually present among the words
retrieved by the input query!
Returns
-------
werkzeug.Response
"""
words = [word for word in query.all()]
if not words:
app.logger.error("Query %s returned no words!", query)
return redirect(url_for("home.index"))
if sorting is FlashcardSorting.NONE:
app.logger.debug("No sorting required")
elif sorting is FlashcardSorting.ALPHABETICAL:
app.logger.debug("Alphabetical sorting required")
words.sort(key=lambda word: remove_niqqudot(word.hebrew))
elif sorting is FlashcardSorting.SHUFFLE:
app.logger.debug("Random sorting (shuffling) required")
random.shuffle(words)
else:
raise ValueError
words_ids = [word.id for word in words]
try:
start_index = words_ids.index(int(start_at_word_id)) # type: ignore
except TypeError: # start_at_word_id is None
start_index = 0
except ValueError: # start_at_word_id is int-castable
app.logger.error(
"Could not find word with desired ID %i "
"among words queried from the database", start_at_word_id)
return redirect(url_for("home.index"))
# Set word list and reset other state
FlashcardRunState(
words=words_ids,
index=start_index,
side=FlashcardSide.prompt_side(),
progress=[0, len(words)],
).to_flask_session()
return redirect(url_for(endpoint))
def list_people(qs: Query = Depends(get_queryset)):
return qs.all()
async def get_content_list(query: Query = Depends(_get_my_content_query)):
try:
return query.all()
except:
raise HTTPException(status_code=400)
