def percentile_aggr(self, percentile, fld):
"""Sum up the things"""
percentile_val = int(percentile[len("percentile") :])
if percentile_val not in (1, 5, 10, 25, 50, 75, 90, 95, 99):
raise GrammarError(
f"percentile values of {percentile_val} is not supported."
)
if self.drivername == "bigquery":
percentile_fn = getattr(engine_support, f"bq_percentile{percentile_val}")
return percentile_fn(fld)
elif self.drivername == "sqlite":
raise GrammarError("Percentile is not supported on sqlite")
else:
# Postgres + redshift
return func.percentile_cont(percentile_val / 100.0).within_group(fld)
def datetime_end_conv(self, _, datestr):
# Parse a datetime as the last moment of the given day
# if the date
dt = dateparser.parse(datestr)
if dt is None:
raise GrammarError(f"Can't convert '{datestr}' to a datetime.")
return convert_to_eod_datetime(dt)
def switch_statement(self, expr, *cases):
found_default = False
for case in cases:
if case.choice is None:
if found_default:
raise GrammarError("Multiple defaults found in switch")
found_default = True
def date_conv(self, _, datestr):
dt = dateparser.parse(datestr)
if dt:
dt = dt.date()
else:
raise GrammarError(f"Can't convert '{datestr}' to a date.")
return dt
def process_int_constant(token):
strval = token.value
if strval == '0':
val = 0
elif strval[0] == '0':
if strval[1] in 'xX':
base = 16
elif strval[1] in 'bB':
base = 2
else:
base = 8
val = int(strval, base)
elif strval[0] == "'":
if strval[1] == '\\':
if strval[2] in simple_escapes:
val = ord(simple_escapes[strval[2]])
else:
if strval[2] == 'x':
val = int(strval[3:-1], 16)
elif strval[2] in '01234567':
val = int(strval[2:-1], 8)
else:
raise GrammarError("Invalid string escape '%s'" % strval)
else:
val = ord(strval[1])
else:
val = int(strval)
return Token.new_borrow_pos(token.type, val, token)
def named_game(self, items):
debug('named_game', items)
if str(items[0]) in heap:
return heap[str(items[0])]
else:
# variable not assigned
raise GrammarError("Variable " + str(items[0]) +
" is not defined.")
def dt_quarter_conv(self, _, fld):
if self.drivername == "bigquery":
return func.timestamp_trunc(fld, text("quarter"))
elif self.drivername.startswith("mssql"):
raise GrammarError("quarter is not supported on mssql")
else:
# Postgres + redshift
return func.date_trunc("quarter", fld)
def dt_week_conv(self, _, fld):
"""Truncate to mondays"""
if self.drivername == "bigquery":
return func.timestamp_trunc(fld, text("week(monday)"))
elif self.drivername.startswith("mssql"):
raise GrammarError("week is not supported on mssql")
else:
# Postgres + redshift
return func.date_trunc("week", fld)
def quarter_conv(self, _, fld):
# Convert each date to the first day of each quarter
if self.drivername == "bigquery":
return func.date_trunc(fld, text("quarter"))
elif self.drivername.startswith("mssql"):
raise GrammarError("quarter is not supported on mssql")
else:
# Postgres + redshift
return func.date_trunc("quarter", fld)
def age_conv(self, _, fld):
"""Convert a date to an age"""
if self.drivername == "bigquery":
return engine_support.bq_age(fld)
elif self.drivername == "sqlite":
raise GrammarError("Age is not supported on sqlite")
else:
# Postgres + redshift
return engine_support.postgres_age(fld)
def parse(
self,
text,
forbid_aggregation=False,
enforce_aggregation=False,
debug=False,
convert_dates_with=None,
convert_datetimes_with=None,
):
"""Return a parse tree for text
Args:
text (str): A field expression
forbid_aggregation (bool, optional):
The expression may not contain aggregations. Defaults to False.
enforce_aggregation (bool, optional):
Wrap the expression in an aggregation if one is not provided. Defaults to False.
debug (bool, optional): Show some debug info. Defaults to False.
convert_dates_with (str, optional): A converter to use for date fields
convert_datetimes_with (str, optional): A converter to use for datetime fields
Raises:
GrammarError: A description of any errors and where they occur
Returns:
A tuple of
ColumnElement: A SQLALchemy expression
DataType: The datatype of the expression (bool, date, datetime, num, str)
"""
tree = self.parser.parse(text, start="col")
validator = SQLALchemyValidator(text, forbid_aggregation, self.drivername)
validator.visit(tree)
self.last_datatype = validator.last_datatype
if validator.errors:
if debug:
print("".join(validator.errors))
print("Tree:\n" + tree.pretty())
raise GrammarError("".join(validator.errors))
else:
if debug:
print("Tree:\n" + tree.pretty())
self.transformer.text = text
self.transformer.convert_dates_with = convert_dates_with
self.transformer.convert_datetimes_with = convert_datetimes_with
expr = self.transformer.transform(tree)
if (
enforce_aggregation
and not validator.found_aggregation
and self.last_datatype == "num"
):
return (func.sum(expr), self.last_datatype)
else:
return (expr, self.last_datatype)
def _raise_error(self, message):
tree = None
tok = None
# Find the first token
while tree and tree.children:
tree = tree.children[0]
if isinstance(tree, Token):
tok = tree
break
if tok:
extra_context = self._get_context_for_token(tok)
message = f"{message}\n{extra_context}"
raise GrammarError(message)
def num_div(self, num, denom):
"""SQL safe division"""
if isinstance(denom, (int, float)):
if denom == 0:
raise GrammarError("When dividing, the denominator can not be zero")
elif denom == 1:
return num
elif isinstance(num, (int, float)):
return num / denom
else:
return cast(num, Float) / denom
else:
if isinstance(num, (int, float)):
return case([(denom == 0, None)], else_=num / cast(denom, Float))
else:
return case(
[(denom == 0, None)], else_=cast(num, Float) / cast(denom, Float)
)
def process_string_escapes(token):
val = token.value
new_parts = []
start = 1 # trim off quotation
while val:
pos = val.find('\\')
if pos == -1: break
new_parts.append(val[start:pos])
start = 0
next = val[pos + 1]
consume = 1
if next in simple_escapes:
new_parts.append(simple_escapes[next])
else:
raise GrammarError("Invalid string escape '%s'" % next)
val = val[pos + 1 + consume:]
new_parts.append(val[start:-1])
new_val = ''.join(new_parts)
return Token.new_borrow_pos(token.type, new_val, token)
def datetime_conv(self, _, datestr):
dt = dateparser.parse(datestr)
if dt is None:
raise GrammarError(f"Can't convert '{datestr}' to a datetime.")
return dt
def function_definition(self, type, decl, body):
if not isinstance(decl.name_spec, FuncDeclSpec):
raise GrammarError("Function definition must have a specifier \
of type function")
return FunctionDeclaration(body=body, type=type, decl=decl)