def build_indicators(indicators: List[ReportIndicator],
dataset_columns: List[Column], dataset_query_alias):
_selects = []
_appear_in_group_by = []
columns = convent_column_list_to_dict(dataset_columns)
for indicator in indicators:
column: Column = columns.get(indicator.columnId, None)
if column is None:
continue
else:
field = Field(column.alias, None,
AliasedQuery(dataset_query_alias))
if indicator.arithmetic == "sum":
_selects.append(fn.Sum(field))
elif indicator.arithmetic == "avg":
_selects.append(fn.Avg(field))
elif indicator.arithmetic == "max":
_selects.append(fn.Max(field))
elif indicator.arithmetic == "min":
_selects.append(fn.Min(field))
elif indicator.arithmetic == "count":
_selects.append(fn.Count(field))
else:
_selects.append(field)
_appear_in_group_by.append(field)
return _selects, _appear_in_group_by
def select_average(self,
table: str,
field: str,
start: Optional[date] = None,
stop: Optional[date] = None) -> AverageResult:
target = Table(table, schema=self.schema)
target_field = Field(field, table=target)
query = Query.from_(target).select(target.date,
fn.Avg(target_field, alias='average'),
fn.Sum(target_field, alias='sum'),
fn.Count(target_field, alias='count')). \
groupby(target.date).orderby(target.date)
query = self.apply_dates(query, target, start, stop)
result = AverageResult(table=table, field=field, elements=[])
for current in self.run(query):
result.elements.append(
Average(date=date.fromordinal(current[0]),
avg=float(current[1]),
sum=float(current[2]) if current[3] else 0.,
count=int(current[3])))
return result
def test_groupby__count_distinct(self):
q = (Query.from_(self.t).groupby(self.t.foo).select(
self.t.foo,
fn.Count("*").distinct()))
self.assertEqual(
'SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY "foo"', str(q))
def test_select_aliases_mixed_with_complex_fields(self):
test_query = Query.from_(self.t).select(
self.t.foo.as_('foobar'),
fn.Count(self.t.fiz + self.t.buz)).groupby(self.t.foo)
self.assertListEqual(['foobar', 'COUNT("fiz"+"buz")'],
test_query.select_aliases())
def test_functions_using_constructor_param_nested(self):
"""
We don't show aliases of fields that are arguments of a function.
"""
q = Query.from_(self.t).select(fn.Sqrt(fn.Count("*", alias="foo"), alias="bar"))
self.assertEqual('SELECT SQRT(COUNT(*)) "bar" FROM "abc"', str(q))
def getTotalProductByType(self):
products = Table('products_shadow', 'eg_product' + self.tail)
q = Query.from_(products).select(fn.Count(products.id),
products.type).groupby(products.type)
records = self.db.fetchall(q.get_sql())
return records
def select_count(self,
table: str,
field: Optional[str] = None,
start: Optional[date] = None,
stop: Optional[date] = None) -> CountResult:
target = Table(table, schema=self.schema)
count_field = fn.Count(
Field(field, table=target) if field else target.date,
alias='count')
if field:
count_field = count_field.distinct()
query = Query.from_(target).select(target.date, count_field).groupby(
target.date).orderby(target.date)
query = self.apply_dates(query, target, start, stop)
result = CountResult(table=table,
field=field,
distinct=field is not None,
group=None,
ascending=None,
elements=[])
for current in self.run(query):
result.elements.append(
Count(date=date.fromordinal(current[0]),
group=None,
count=int(current[1])))
return result
def getTotalImage(self):
product_images = Table('product_images_shadow',
'eg_product' + self.tail)
q = Query.from_(product_images).select(fn.Count('*'))
record = self.db.fetchone(q.get_sql())
return record
def test_groupby__count_field(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo)
.select(self.t.foo, fn.Count(self.t.bar))
)
self.assertEqual('SELECT "foo",COUNT("bar") FROM "abc" GROUP BY "foo"', str(q))
def count_sql(self, estimate=True):
if estimate:
stats_table = Table("pg_stat_user_tables")
return Query.from_(stats_table).select(
stats_table.n_live_tup
).where(stats_table.relname == self.initial_table_name).where(
stats_table.schemaname == self.initial_schema_name).get_sql()
self.current_query = Query.from_(self.current_dataset)
return self.current_query.select(
pypika_fn.Count(self.current_dataset.star)).get_sql()
def getCategories(self):
categories_shadow = Table('categories_shadow')
q = Query.from_(categories_shadow).select(
fn.Count(categories_shadow.id)
)
# print(self.db)
records = self.db.fetchone(q.get_sql())
# self.db.close()
return records
def getModelIncorrect(self):
product_motor = Table('product_motor_shadow', 'eg_product' + self.tail)
products = Table('products_shadow', 'eg_product' + self.tail)
motors = Table('motors_shadow', 'eg_product' + self.tail)
sys_code = Table('syouhin_sys_code', 'eg_product' + self.tail)
mst_syouhin_model_info_all = Table('mst_syouhin_model_info_all',
'rc_products')
qSub1 = Query.from_(products).join(
product_motor, how=JoinType.inner
).on(product_motor.product_id == products.id).join(
motors, how=JoinType.inner
).on(motors.id == product_motor.motor_id).select(
products.sku,
"GROUP_CONCAT(motors_shadow.url_rewrite ORDER BY CAST(motors_shadow.url_rewrite AS UNSIGNED) SEPARATOR '-') as model",
fn.Count(motors.url_rewrite).as_('model_count')).groupby(
products.sku).limit(100000)
qSub2 = Query.from_(mst_syouhin_model_info_all).select(
mst_syouhin_model_info_all.syouhin_sys_code,
"GROUP_CONCAT(mst_syouhin_model_info_all.syouhin_model_code ORDER BY CAST(syouhin_model_code AS UNSIGNED) SEPARATOR '-') as model",
fn.Count(mst_syouhin_model_info_all.syouhin_model_code).
as_('model_count')).where(
mst_syouhin_model_info_all.syouhin_model_code != 9999).groupby(
mst_syouhin_model_info_all.syouhin_sys_code).limit(100000)
finds = ['"', 'products_shadow.GROUP_CONCAT']
replaces = ['', 'GROUP_CONCAT']
qSub1 = qSub1.get_sql()
qSub2 = qSub2.get_sql()
for find, replace in zip(finds, replaces):
qSub1 = qSub1.replace(find, replace)
qSub2 = qSub2.replace(find, replace)
q = "SELECT real_tbl.sku, real_tbl.model as current_model, real_tbl.model_count, org_tbl.model as correct_model, org_tbl.model_count " + \
"FROM ({}) as real_tbl".format(qSub1) + " " + \
"INNER JOIN ({}) as org_tbl".format(qSub2) + " ON real_tbl.sku = org_tbl.syouhin_sys_code " + \
"WHERE real_tbl.model <> org_tbl.model AND real_tbl.model_count <> org_tbl.model_count"
records = self.db.fetchall(q)
return records
def build_count_query_for_subject(console_subject):
dataset = console_subject.dataset
query = None
# indicator = report.indicators[0]
if dataset is not None:
query = _from(dataset.columns[0])
query = query.select(fn.Count("*"))
for join in dataset.joins:
query = _join(query, join)
if dataset.filters:
query = _filter(query, dataset.filters)
return query
def get_count_sql(self, config, estimate=True):
if estimate:
stats_table = Table("pg_stat_user_tables")
table_parts = self.get_source_table(config.get('source'), True)
table_name = table_parts[0]
schema_name = table_parts[1]
return Query.from_(stats_table).select(
stats_table.n_live_tup).where(
stats_table.relname == table_name).where(
stats_table.schemaname == schema_name).get_sql()
query = self.process_config(config)
query = Query.from_(query)
return query.select(fn.Count('*')).get_sql()
def getProductTop10Brand(self):
products = Table('products_shadow', 'eg_product' + self.tail)
manufacturers = Table('manufacturers_shadow', 'eg_product' + self.tail)
q = Query.from_(products).join(manufacturers).on(
products.manufacturer_id == manufacturers.id
).select(fn.Count('*'), manufacturers.url_rewrite).where(
(products.is_main == 1)
& (manufacturers.url_rewrite.isin([854, 177, 704, 345]))).groupby(
manufacturers.url_rewrite).orderby(manufacturers.url_rewrite,
order=Order.asc)
records = self.db.fetchall(q.get_sql())
return records
def _indicator(q: QueryBuilder, indicator: ReportIndicator, column: Column) -> QueryBuilder:
if indicator.arithmetic == "sum":
return q.select(fn.Sum(parse_parameter(column.parameter)))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(parse_parameter(column.parameter)))
elif indicator.arithmetic == "max":
return q.select(fn.Max(parse_parameter(column.parameter)))
elif indicator.arithmetic == "min":
return q.select(fn.Min(parse_parameter(column.parameter)))
elif indicator.arithmetic == "count":
return q.select(fn.Count(parse_parameter(column.parameter)))
else:
return q.select(fn.Max(parse_parameter(column.parameter)))
def getProductTop10Moto(self):
product_motor = Table('product_motor_shadow', 'eg_product' + self.tail)
products = Table('products_shadow', 'eg_product' + self.tail)
motors = Table('motors_shadow', 'eg_product' + self.tail)
q = Query.from_(product_motor).join(products).on(
products.id == product_motor.product_id).join(motors).on(
product_motor.motor_id == motors.id).select(
fn.Count('*'), motors.url_rewrite).where(
(products.is_main == 1) & (motors.url_rewrite.isin(
[40, 874, 163, 821, 825, 462, 463]))).groupby(
product_motor.motor_id)
records = self.db.fetchall(q.get_sql())
return records
def _get_query_count_sql(self, countquery):
values = []
table = self.pika_table
query = PostgreSQLQuery.from_(table).select(fn.Count("*"))
i = 0
criterion, where_values = self._expressions_to_criterion(
countquery.expressions, i)
query = query.where(criterion)
values.extend(where_values)
i += len(where_values)
sql = str(query.get_sql())
return sql, values
def select_count_group(self,
table: str,
field: Optional[str],
group: str,
distinct: bool = False,
start: Optional[date] = None,
stop: Optional[date] = None,
ascending: bool = True,
limit: Optional[int] = None) -> CountResult:
target = Table(table, schema=self.schema)
count_field = fn.Count(
Field(field, table=target) if field else target.date,
alias='count')
order = Order.asc if ascending else Order.desc
if distinct:
count_field = count_field.distinct()
group_field = Field(group, table=target)
query = Query.from_(target).select(target.date, group_field.as_('group'), count_field). \
groupby(target.date, group_field).orderby(target.date). \
orderby(count_field, order=order).orderby(group_field)
query = self.apply_dates(query, target, start, stop)
if limit is not None:
window = Query.from_(query).select(
query.date, query.group, query.count,
an.RowNumber(alias='row_number').over(query.date).orderby(
query.count, order=order).orderby(query.group))
query = Query.from_(window).select(window.date, window.group, window.count). \
where(window.row_number <= limit).orderby(window.date). \
orderby(window.count, order=order).orderby(window.group)
result = CountResult(table=table,
field=field,
distinct=distinct,
group=group,
ascending=ascending,
elements=[])
for current in self.run(query):
result.elements.append(
Count(date=date.fromordinal(current[0]),
group=current[1],
count=int(current[2])))
return result
def getTotalProductTop10Cate(self):
category_product = Table('category_product_shadow',
'eg_product' + self.tail)
categories = Table('categories_shadow', 'eg_product' + self.tail)
products = Table('products_shadow', 'eg_product' + self.tail)
q = Query.from_(category_product).join(categories).on(
categories.id == category_product.category_id).join(products).on(
products.id == category_product.product_id).select(
fn.Count('*'), categories.url_rewrite).where(
(products.is_main == 1) & (categories.url_rewrite.isin(
[3002, 3021, 3123, 3034, 5000]))).groupby(
categories.url_rewrite)
records = self.db.fetchall(q.get_sql())
return records
def create_select_filtered_paginated_query_count(table, filters: dict,
id_field_where: str):
"""
:param table:
:param filters: dict:
:param id_field_where: str:
"""
table = Table(table)
query = Query.from_(table).select(fn.Count(id_field_where, alias="count"))
query = set_query_filters(filters, query, table)
return query.get_sql()
def build_count_query_for_subject_chart(console_subject, columns_dict, report):
dataset = console_subject.dataset
query = None
if dataset is not None:
query = _from(dataset.columns[0])
if report.indicators:
for indicator in report.indicators:
query = _indicator(query, indicator, columns_dict.get(indicator.columnId))
else:
query = query.select(fn.Count("*"))
for join in dataset.joins:
query = _join(query, join)
if dataset.filters:
query = _filter(query, dataset.filters)
return query
def find_as_queue(self, *, location_ids: Union[None, List[int]] = None,
max_trigger_time=None,
min_trigger_time: datetime = None,
page: Optional[int] = None, per_page: Optional[int] = None,
status: PlanStatus = None,
user_id: int) -> Tuple[List[Plan], int]:
plan_table, task_table = Tables('t_plan', 't_task')
base_query = Query \
.from_(plan_table) \
.left_join(task_table) \
.on(plan_table.task_id == task_table.id) \
.where(task_table.user_id == user_id)
if location_ids:
base_query = base_query.where(plan_table.location_id.isin(location_ids))
if isinstance(max_trigger_time, datetime):
base_query = base_query.where(plan_table.trigger_time < max_trigger_time)
if min_trigger_time:
base_query = base_query.where(plan_table.trigger_time >= min_trigger_time)
if status:
base_query = base_query.where(plan_table.status == status.value)
counting_query = base_query \
.select(functions.Count(0).as_('COUNT'))
query = base_query \
.select(plan_table.star)\
.orderby(plan_table.trigger_time, order=Order.asc)
if page and per_page:
query = query\
.limit(per_page)\
.offset((page - 1) * per_page)
print('counting sql', counting_query.get_sql(quote_char=None))
print('sql', query.get_sql(quote_char=None))
with self.get_connection() as connection:
with connection.cursor() as cursor:
cursor.execute(query.get_sql(quote_char=None))
plan_dicts = cursor.fetchall()
cursor = self.execute_sql(counting_query.get_sql(quote_char=None))
row = cursor.fetchone()
return [self._row2entity(row) for row in plan_dicts], row['COUNT']
def _indicator(q: QueryBuilder, indicator: ReportIndicator,
column: Column) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
if indicator.arithmetic == "sum":
return q.select(fn.Sum(value_))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(value_))
elif indicator.arithmetic == "max":
return q.select(fn.Max(value_))
elif indicator.arithmetic == "min":
return q.select(fn.Min(value_))
elif indicator.arithmetic == "count":
return q.select(fn.Count(value_))
else:
return q.select(fn.Max(value_))
def check_permissions_from_header(itgs, authorization, permissions):
"""A convenience method to check that the given authorization header is
formatted correctly, corresponds to a real unexpired token, and that
token has all of the given list of permissions.
For most endpoints, calling this immediately after initializing the lazy
integrations is the fastest and easiest way to check permissions.
@param itgs The lazy integrations to use
@param authorization The authorization header provided
@param permissions The list of permissions required, where each item is
the string name of the permission. May be an empty list or a single
string
@return (True, user_id) if the authorization is valid and has
all of the required permissions, (False, None) otherwise.
"""
if isinstance(permissions, str):
permissions = [permissions]
authtoken = get_authtoken_from_header(authorization)
if authtoken is None:
return (False, None)
info = get_auth_info_from_token_auth(
itgs, models.TokenAuthentication(token=authtoken)
)
if info is None:
return (False, None)
auth_id, user_id = info[:2]
if not permissions:
return (True, user_id)
perms = Table('permissions')
authtoken_perms = Table('authtoken_permissions')
itgs.read_cursor.execute(
Query.from_(authtoken_perms).select(ppfns.Count('*'))
.join(perms).on(perms.id == authtoken_perms.permission_id)
.where(perms.name.isin([Parameter('%s') for _ in permissions]))
.where(authtoken_perms.authtoken_id == Parameter('%s'))
.get_sql(),
(*permissions, auth_id)
)
(num_perms_found,) = itgs.read_cursor.fetchone()
if num_perms_found == len(permissions):
return (True, user_id)
return (False, None)
def __init__(self, d_rng, minesite):
super().__init__()
a, b, d = self.a, self.b, self.d
# get a groupby of all fcnumbers where unit.minesite = forthills
q2 = Query.from_(a) \
.select(a.FCNumber) \
.select(fn.Count(a.FCNumber).as_('Count')) \
.left_join(b).on_field('FCNumber') \
.left_join(d).on_field('Unit') \
.where((d.MineSite == minesite) & (b.ReleaseDate.between(*d_rng))) \
.groupby(a.FCNumber)
self.cols = [b.FCNumber, b.SubjectShort, b.Subject.as_(
'Info'), b.Classification, q2.Count, b.ReleaseDate, b.ExpiryDate]
self.q = Query.from_(b) \
.inner_join(q2).on_field('FCNumber')
def __init__(self, d_upper: dt, minesite: str = 'FortHills'):
super().__init__()
a, b = self.a, self.b
# make full year range
d_lower = d_upper + relativedelta(years=-1) + delta(days=1)
_year_month = cfn('FORMAT', ['date', 'format']) # year_month(a.DateAdded, 'yyyy-MM')
year_month = _year_month(a.DateTSISubmission, 'yyyy-MM')
cols = [year_month.as_('period'), fn.Count(pk.terms.Star()).as_('num')]
q = Query.from_(a) \
.select(*cols) \
.left_join(b).on_field('Unit') \
.where(a.StatusTSI == 'Closed') \
.where(b.MineSite == minesite) \
.where(~a.Title.like('fc %')) \
.where(a.DateTSISubmission.between(d_lower, d_upper)) \
.groupby(year_month)
f.set_self(vars())
class Anomalies:
tbl = Table('anomalies')
function = tbl.call.as_('function')
symbol = tbl.symbol.as_('symbol')
state_var = Error(tbl.state_var.as_('kind')).as_('error')
dirname = DirName(tbl.location).as_('directory')
filename = BaseName(tbl.location).as_('file')
path = Path(tbl.location).as_('path')
os_path = OsPath(tbl.location).as_('os_path')
lineno = Line(tbl.location).as_('line')
# Other:
total = fn.Count(tbl.star).as_('count')
location = Loc(tbl.location).as_('location')
def query(self):
return Query.from_(self.tbl)
def as_str(self, field_or_fields):
if hasattr(field_or_fields, "alias"):
return field.alias
else:
return tuple(x.alias for x in field_or_fields)
def getPolicyDocuments(query_obj="",
AuthRole="",
Limit="",
return_count=False):
query_obj = query_obj.select(PD.title,
PD.document).orderby(PD.title,
order=Order.asc)
if not return_count:
query_obj = query_obj.limit(Limit)
if return_count:
query_obj = Query.from_(query_obj).select((fn.Count(0)).as_("total"))
try:
employee_data = frappe.db.sql(query_obj.get_sql(quote_char="`"),
as_dict=True)
return employee_data
except:
return []
def __init__(self, d_rng, minesite):
super().__init__(minesite=minesite)
a, b, d = self.a, self.b, self.d
# get all FCs complete during month, datecompletesms
# group by FC number, count
self.formats.update({
'Hours': '{:,.1f}',
'Total Hours': '{:,.1f}'})
self.cols = [
a.FCNumber.as_('FC Number'),
a.Classification.as_('Type'),
a.Subject,
fn.Count(a.FCNumber).as_('Completed'),
b.hours.as_('Hours')]
self.q = self.q \
.groupby(a.FCNumber, a.Subject, a.Classification, b.hours) \
.orderby(a.Classification)
self.add_fltr_args([
dict(vals=dict(MinDateComplete=d_rng), term='between'),
dict(vals=dict(MineSite=minesite), table=d)])
