def get_sql(self, **kwargs):
dialect = kwargs.get("dialect", None)
values = ",".join(term.get_sql(**kwargs) for term in self.values)
sql = ("ARRAY[{}]"
if dialect in (Dialects.POSTGRESQL,
Dialects.REDSHIFT) else "[{}]").format(values)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, **kwargs):
sql = '{name} AT TIME ZONE {interval}\'{zone}\''.format(
name=self.field.get_sql(**kwargs),
interval='INTERVAL ' if self.interval else '',
zone=self.zone,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, **kwargs: Any) -> str:
with_alias = kwargs.pop("with_alias", False)
with_namespace = kwargs.pop("with_namespace", False)
quote_char = kwargs.pop("quote_char", None)
dialect = kwargs.pop("dialect", None)
# FIXME escape
function_sql = self.get_function_sql(
with_namespace=with_namespace, quote_char=quote_char, dialect=dialect
)
if self.schema is not None:
function_sql = "{schema}.{function}".format(
schema=self.schema.get_sql(
quote_char=quote_char, dialect=dialect, **kwargs
),
function=function_sql,
)
if with_alias:
return format_alias_sql(
function_sql, self.alias, quote_char=quote_char, **kwargs
)
return function_sql
def get_sql(self, subquery: Any = None, **kwargs: Any) -> str:
sql = "{term} {not_}IN {container}".format(
term=self.term.get_sql(**kwargs),
container=self.container.get_sql(subquery=True, **kwargs),
not_="NOT " if self._is_negated else "",
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(
self,
with_alias=False,
with_namespace=False,
quote_char=None,
dialect=None,
**kwargs
):
# FIXME escape
function_sql = self.get_function_sql(
with_namespace=with_namespace, quote_char=quote_char, dialect=dialect
)
if self.schema is not None:
function_sql = "{schema}.{function}".format(
schema=self.schema.get_sql(
quote_char=quote_char, dialect=dialect, **kwargs
),
function=function_sql,
)
if with_alias:
return format_alias_sql(
function_sql, self.alias, quote_char=quote_char, **kwargs
)
return function_sql
def get_sql(
self,
quote_char: Optional[str] = None,
secondary_quote_char: str = "'",
param_wrapper: Optional[NamedParameterWrapper] = None,
**kwargs: Any,
) -> str:
if param_wrapper and isinstance(self.value, str):
# add quotes if it's a string value
value_sql = self.get_value_sql(quote_char=quote_char, **kwargs)
sql = param_wrapper.get_sql(param_value=value_sql, **kwargs)
else:
# * BUG: pypika doesen't parse timedeltas
if isinstance(self.value, timedelta):
self.value = format_timedelta(self.value)
sql = self.get_value_sql(
quote_char=quote_char,
secondary_quote_char=secondary_quote_char,
param_wrapper=param_wrapper,
**kwargs,
)
return format_alias_sql(sql,
self.alias,
quote_char=quote_char,
**kwargs)
def get_sql(self, quote_char=None, secondary_quote_char="'", **kwargs):
sql = self.get_value_sql(quote_char=quote_char,
secondary_quote_char=secondary_quote_char,
**kwargs)
return format_alias_sql(sql,
self.alias,
quote_char=quote_char,
**kwargs)
def get_sql(self, **kwargs: Any) -> str:
# FIXME escape
sql = "{term} BETWEEN {start} AND {end}".format(
term=self.term.get_sql(**kwargs),
start=self.start.get_sql(**kwargs),
end=self.end.get_sql(**kwargs),
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, quote_char: Optional[str] = None, **kwargs: Any) -> str:
return format_alias_sql(
format_quotes(self.value,
kwargs.get("secondary_quote_char") or ""),
self.alias or self.value,
quote_char=quote_char,
**kwargs,
)
def get_sql(self, quote_char: str = '"', with_alias: bool = False, **kwargs: Any) -> str:
sql = "{left}{comparator}{right}".format(
comparator=self.comparator.value,
left=self.left.get_sql(quote_char=quote_char, **kwargs),
right=self.right.get_sql(quote_char=quote_char, **kwargs),
)
if with_alias:
return format_alias_sql(sql, self.alias, **kwargs)
return sql
def get_sql(self, **kwargs):
quote_char = kwargs.get("quote_char")
# FIXME escape
table_sql = format_quotes(self._table_name, quote_char)
if self._schema is not None:
table_sql = "{schema}.{table}".format(
schema=self._schema.get_sql(**kwargs), table=table_sql)
return format_alias_sql(table_sql, self.alias, **kwargs)
def get_sql(self,
with_namespace=False,
quote_char=None,
dialect=None,
**kwargs):
array = self._array.get_sql()
sql = "{name}({array})".format(
name=self.name,
array='"%s"' % array if isinstance(self._array, Field) else array,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self,
with_namespace: bool = False,
quote_char: Optional[str] = None,
dialect: Optional[Dialects] = None,
**kwargs: Any) -> str:
array = self._array.get_sql()
sql = "{name}({array})".format(
name=self.name,
array='"%s"' % array if isinstance(self._array, Field) else array,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, with_alias=False, **kwargs):
sql = '{left}{comparator}{right}{nested_comparator}{nested}'.format(
left=self.left.get_sql(**kwargs),
comparator=self.comparator.value,
right=self.right.get_sql(**kwargs),
nested_comparator=self.nested_comparator.value,
nested=self.nested.get_sql(**kwargs))
if with_alias:
return format_alias_sql(sql=sql, alias=self.alias, **kwargs)
return sql
def get_sql(self):
if self._converter_cls:
converted = []
for value in self._values:
converter = self._converter_cls(value,
**self._converter_options)
converted.append(converter.get_sql())
sql = "".join(["[", ",".join(converted), "]"])
else:
sql = str(self._values)
return format_alias_sql(sql, self.alias)
def get_sql(self,
with_alias=False,
with_namespace=False,
quote_char=None,
dialect=None,
**kwargs):
sql = "{name}({field},{length})".format(
name=self.name,
field=self._field if isinstance(self._field, Field) else "'%s'" %
str(self._field),
length=self._length,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = " UNION{type} {union}"
kwargs.setdefault("dialect", self.base_query.dialect)
# This initializes the quote char based on the base query, which could be a dialect specific query class
# This might be overridden if quote_char is set explicitly in kwargs
kwargs.setdefault("quote_char", self.base_query.QUOTE_CHAR)
base_querystring = self.base_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs
)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs
)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException(
"Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".format(
query1=base_querystring, query2=union_querystring
)
)
querystring += union_template.format(
type=union_type.value, union=union_querystring
)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = "({query})".format(query=querystring, **kwargs)
if with_alias:
return format_alias_sql(
querystring, self.alias or self._table_name, **kwargs
)
return querystring
def get_sql(self,
with_alias: bool = False,
with_namespace: bool = False,
quote_char: Optional[str] = None,
dialect: Optional[Dialects] = None,
**kwargs: Any) -> str:
left = self._left_array.get_sql()
right = self._right_array.get_sql()
sql = "{name}({left},{right})".format(
name=self.name,
left='"%s"' %
left if isinstance(self._left_array, Field) else left,
right='"%s"' %
right if isinstance(self._right_array, Field) else right,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self,
with_alias=False,
with_namespace=False,
quote_char=None,
dialect=None,
**kwargs):
left = self._left_array.get_sql()
right = self._right_array.get_sql()
sql = "{name}({left},{right})".format(
name=self.name,
left='"%s"' %
left if isinstance(self._left_array, Field) else left,
right='"%s"' %
right if isinstance(self._right_array, Field) else right,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, with_alias=False, **kwargs):
if not self._cases:
raise CaseException(
"At least one 'when' case is required for a CASE statement.")
cases = " ".join("WHEN {when} THEN {then}".format(
when=criterion.get_sql(**kwargs), then=term.get_sql(**kwargs))
for criterion, term in self._cases)
else_ = " ELSE {}".format(self._else.get_sql(
**kwargs)) if self._else else ""
case_sql = "CASE {cases}{else_} END".format(cases=cases, else_=else_)
if with_alias:
return format_alias_sql(case_sql, self.alias, **kwargs)
return case_sql
def get_sql(self, with_alias=False, **kwargs):
is_mul = self.operator in self.mul_order
is_left_add, is_right_add = [
getattr(side, "operator", None) in self.add_order
for side in [self.left, self.right]
]
arithmetic_sql = "{left}{operator}{right}".format(
operator=self.operator.value,
left=("({})" if is_mul and is_left_add else "{}").format(
self.left.get_sql(**kwargs)),
right=("({})" if is_mul and is_right_add else "{}").format(
self.right.get_sql(**kwargs)),
)
if with_alias:
return format_alias_sql(arithmetic_sql, self.alias, **kwargs)
return arithmetic_sql
def get_sql(self, **kwargs: Any) -> str:
with_alias = kwargs.pop("with_alias", False)
with_namespace = kwargs.pop("with_namespace", False)
quote_char = kwargs.pop("quote_char", None)
field_sql = format_quotes(self.name, quote_char)
# Need to add namespace if the table has an alias
if self.table and (with_namespace or self.table.alias):
table_name = self.table.get_table_name()
field_sql = "{namespace}.{name}".format(
namespace=format_quotes(table_name, quote_char), name=field_sql,
)
field_alias = getattr(self, "alias", None)
if with_alias:
return format_alias_sql(
field_sql, field_alias, quote_char=quote_char, **kwargs
)
return field_sql
def get_sql(self,
with_alias=False,
with_namespace=False,
quote_char=None,
dialect=None,
**kwargs):
args = []
for p in self.args:
if hasattr(p, "get_sql"):
args.append('toString("{arg}")'.format(
arg=p.get_sql(with_alias=False, **kwargs)))
else:
args.append(str(p))
sql = "{name}({args},[{patterns}])".format(
name=self.name,
args=",".join(args),
patterns=",".join(["'%s'" % i for i in self._patterns]),
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self,
with_alias=False,
with_namespace=False,
quote_char=None,
secondary_quote_char="'",
**kwargs):
field_sql = format_quotes(self.name, quote_char)
# Need to add namespace if the table has an alias
if self.table and (with_namespace or self.table.alias):
field_sql = "{namespace}.{name}" \
.format(
namespace=format_quotes(self.table.alias or self.table._table_name, quote_char),
name=field_sql,
)
field_alias = getattr(self, 'alias', None)
if with_alias:
return format_alias_sql(field_sql,
field_alias,
quote_char=quote_char,
**kwargs)
return field_sql
def get_sql(self, **kwargs: Any) -> str:
sql = "({})".format(",".join(term.get_sql(**kwargs) for term in self.values))
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, with_alias=False, subquery=False, **kwargs):
self._set_kwargs_defaults(kwargs)
if not (self._selects or self._insert_table or self._delete_from
or self._update_table):
return ""
if self._insert_table and not (self._selects or self._values):
return ""
if self._update_table and not self._updates:
return ""
has_joins = bool(self._joins)
has_multiple_from_clauses = 1 < len(self._from)
has_subquery_from_clause = 0 < len(self._from) and isinstance(
self._from[0], QueryBuilder)
has_reference_to_foreign_table = self._foreign_table
kwargs["with_namespace"] = any([
has_joins,
has_multiple_from_clauses,
has_subquery_from_clause,
has_reference_to_foreign_table,
])
if self._update_table:
querystring = self._update_sql(**kwargs)
if self._joins:
querystring += " " + " ".join(
join.get_sql(**kwargs) for join in self._joins)
querystring += self._set_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
return querystring
if self._delete_from:
querystring = self._delete_sql(**kwargs)
elif not self._select_into and self._insert_table:
if self._replace:
querystring = self._replace_sql(**kwargs)
else:
querystring = self._insert_sql(**kwargs)
if self._columns:
querystring += self._columns_sql(**kwargs)
if self._values:
querystring += self._values_sql(**kwargs)
return querystring
else:
querystring += " " + self._select_sql(**kwargs)
else:
if self._with:
querystring = self._with_sql(**kwargs)
else:
querystring = ""
querystring += self._select_sql(**kwargs)
if self._insert_table:
querystring += self._into_sql(**kwargs)
if self._from:
querystring += self._from_sql(**kwargs)
if self._force_indexes:
querystring += self._force_index_sql(**kwargs)
if self._joins:
querystring += " " + " ".join(
join.get_sql(**kwargs) for join in self._joins)
if self._prewheres:
querystring += self._prewhere_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
if self._groupbys:
querystring += self._group_sql(**kwargs)
if self._mysql_rollup:
querystring += self._rollup_sql()
if self._havings:
querystring += self._having_sql(**kwargs)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = "({query})".format(query=querystring)
if with_alias:
return format_alias_sql(querystring, self.alias, **kwargs)
return querystring
def get_sql(self, secondary_quote_char: str = "'", **kwargs: Any) -> str:
sql = format_quotes(self._recursive_get_sql(self.value), secondary_quote_char)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, with_alias: bool = False, **kwargs: Any) -> str:
sql = "{term} IS NULL".format(term=self.term.get_sql(**kwargs), )
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, **kwargs: Any) -> str:
sql = "({term} & {value})".format(
term=self.term.get_sql(**kwargs), value=self.value,
)
return format_alias_sql(sql, self.alias, **kwargs)
def get_sql(self, **kwargs: Any) -> str:
sql = "NULL"
return format_alias_sql(sql, self.alias, **kwargs)