Terms

Relevant small pieces in a SQL query.

Value

---

source

NullValue

 NullValue ()

NULL value for use in queries.

---

source

Value

 Value (value)

A simple wrapper for a value that will be used in a query.

---

source

Term

 Term ()

Initialize self. See help(type(self)) for accurate signature.

test_eq(Value(2).get_sql(), '2')
test_eq(Value('abc').get_sql(), "'abc'")
test_eq(Value(2).as_('col1').get_sql(), '2 as col1')
test_eq(Value('abc').as_('col1').get_sql(), "'abc' as col1")
test_eq(Value([1,2,3]).get_sql(), '(1, 2, 3)')
test_eq(Value(['col1', 'col2', 'col3']).get_sql(), "('col1', 'col2', 'col3')")
test_eq(NullValue().get_sql(), 'NULL')
test_eq(NullValue().as_('col1').get_sql(), 'NULL as col1')
test_eq(Value(Value(2)).get_sql(), '2')

Field

---

source

Criteria

 Criteria (this, op, other)

Constructor for criteria from two terms and automatically adds parentheses in appropriate places.

---

source

ArithmeticExpression

 ArithmeticExpression (this, op, other)

Constructor for arithmetic expressions from two terms and automatically adds parentheses in appropriate places.

---

source

Field

 Field (name)

A simple wrapper for a field that will be used in a query. Quotes can be added to the field name by setting the quote_char parameter in get_sql() method.

---

source

FieldBase

 FieldBase ()

Collection of methods to convert to ArithmeticExpression and Criteria

a = Field('a')
b = Field('b')
c = Field('c')

# Test Field
test_eq(a.get_sql(), 'a')
test_eq(c.as_('c1').get_sql(), 'c AS c1')

# Test ArithmeticExpression
test_eq((c-1).as_('new_c').get_sql(), 'c1 - 1 AS new_c')
test_eq((a+1<13).get_sql(), 'a + 1 < 13')
test_eq(((a + 1)/3).get_sql(), '(a + 1) / 3')
test_eq(((a + 1)/(b - 4)).get_sql(), '(a + 1) / (b - 4)')
test_eq(((a + 1>2) & ((b-1<10) | (b>23)) ).get_sql(), 
        'a + 1 > 2 and (b - 1 < 10 or b > 23)')
test_eq((((a + 1>2) & (b-1<=10)) | (b>100)).get_sql(), '(a + 1 > 2 and b - 1 <= 10) or b > 100')

# Test Criteria
test_eq(a.eq(b).get_sql(), 'a = b')
test_eq(a.ne(b).get_sql(), 'a <> b')
test_eq(a.gt(b).get_sql(), 'a > b')
test_eq(a.ge(b).get_sql(), 'a >= b')
test_eq(a.lt(b).get_sql(), 'a < b')
test_eq(a.le(b).get_sql(), 'a <= b')
test_eq(a.like(b).get_sql(), 'a LIKE b')
test_eq(a.not_like(b).get_sql(), 'a NOT LIKE b')
test_eq(a.ilike('%what').get_sql(), "a ILIKE '%what'")
test_eq(a.not_ilike('%hh%').get_sql(), "a NOT ILIKE '%hh%'")
test_eq(a.isin([2, 3, 5]).get_sql(), 'a IN (2, 3, 5)')
test_eq(a.notin([2, 3, 5]).get_sql(), 'a NOT IN (2, 3, 5)')
test_eq(a.isnull().get_sql(), 'a IS NULL')
test_eq(a.notnull().get_sql(), 'a IS NOT NULL')

# Test negate
test_eq((a-1>1).negate().get_sql(), 'NOT (a - 1 > 1)')

# Test quoted field
d = Field('tbl.d')
test_eq(a.get_sql(quote_char='"'), '"a"')
test_eq(d.get_sql(quote_char='"'), '"tbl"."d"')
test_eq((d - 1).get_sql(quote_char='"'), '"tbl"."d" - 1')
test_eq((d - 1 > 2).get_sql(quote_char='"'), '"tbl"."d" - 1 > 2')
test_eq((d - 3).as_('new_d').get_sql(quote_char='"'), '"tbl"."d" - 3 AS "new_d"')

Custom Functions

---

source

custom_func

 custom_func (func=None, window_func=False, dialect=None)

return Field

---

source

DelayedFunc

 DelayedFunc (func, args, kwargs, window_func=True)

Delay the execution of stored function until exec is run.

	Type	Default	Details
func
args
kwargs
window_func	bool	True	whether this function is a window function
Returns	None

---

source

kwargs_func

 kwargs_func (func, *args, **kwargs)

Allow arbitrary kwargs. Only pass those kwargs that are specified in func to func.

For functions that need to be parsed differently for different dialects, you can use the custom_func decorator.

@custom_func
def add_months(column, num, dialect='sql'):
    if dialect=='sql':
        return f'DATE_ADD(month, {num}, {column})'
    elif dialect=='snowflake':
        return f'MONTH_ADD({column}, {num})'


test_eq((add_months("col1", 3)-2 > 2).get_sql(), 'DATE_ADD(month, 3, col1) - 2 > 2')
test_eq((add_months("col1", 3)-2 > 2).get_sql(dialect='snowflake'), 'MONTH_ADD(col1, 3) - 2 > 2')

You can even overwrite or extend the existing functions by using the custom_func decorator with specified dialects keyword argument.

@custom_func(dialect='athena')
def add_months(column, num):
    return f"DATE_ADD('month', {num}, {column})"

test_eq((add_months("col1", 5).as_('new_date')).get_sql(dialect='athena'),
        "DATE_ADD('month', 5, col1) AS new_date")

Window Clause

---

source

Following

 Following (N=None)

Initialize self. See help(type(self)) for accurate signature.

---

source

Preceding

 Preceding (N=None)

Initialize self. See help(type(self)) for accurate signature.

---

source

OverClause

 OverClause (expression)

Constructor for OVER clause.

---

source

DelayedFunc.over

 DelayedFunc.over (partition_by)

---

source

ArithmeticExpression.over

 ArithmeticExpression.over (partition_by)

test_eq(
    OverClause('SUM(col1)').over('col0').orderby('col2').rows(Preceding(3), CURRENT_ROW).get_sql(), 
    'SUM(col1) OVER (PARTITION BY col0 ORDER BY col2 ROWS BETWEEN 3 PRECEDING AND CURRENT_ROW)')

@custom_func(window_func=True)
def LAG(column, offset=1, default=None):
    if default is None:
        return f"LAG({column}, {offset})"
    else:
        return f"LAG({column}, {offset}, {default})"

test_eq(
    LAG(Field('col2'), 1).over('col1').orderby('col3').range(Preceding(), Following(2)).get_sql(),
    'LAG(col2, 1) OVER (PARTITION BY col1 ORDER BY col3 RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)'
)

Case

---

source

Case

 Case ()

Constructor for CASE statement.

test_eq(Case().when(Field('column1')>3, True).else_(False).get_sql(), 
        'CASE\nWHEN column1 > 3 THEN True\nELSE False\nEND')
test_eq(Case().when(Field('column1')>3, 1).when(Field('column1')<1, -1).else_(0).as_('b').get_sql(), 
        'CASE\nWHEN column1 > 3 THEN 1\nWHEN column1 < 1 THEN -1\nELSE 0\nEND AS b')