SqlTerm

class SqlTerm(expr, op=None, value=None)

A single SQL condition. Created via SQL.term(expr, op, value). Inherits from SqlObj.

Parameters:

  • expr – Left-hand expression — a column name string, SqlCat, SqlQuery subquery, or other SqlObj.

  • op – Operator string ('=', '>', '<', '<>', 'IN', 'LIKE', 'EXISTS', etc.). Optional.

  • value – Right-hand value — a Python object (creates a placemarker), SqlQuery subquery (rendered in parentheses), SqlParam (rendered as a placeholder without parentheses), other SqlObj (rendered in parentheses), or None for IS NULL/IS NOT NULL. Optional.

SqlTerm is also created implicitly by:

The behaviour depends on the combination of parameters:

Variant 1: Raw expression (expr only)

When only expr is provided with no op or value, it is written as literal SQL.

SQL.term('active = true')
# active = true

SQL.select().from_table('t').column('*').where('a.id = b.id')
# WHERE a.id = b.id

Variant 2: Simple comparison (expr, op, scalar value)

A standard comparison with a parameterized value. The scalar becomes a bind parameter placeholder in the output.

SQL.term('age', '>', 18)
# sql:       age > %s
# args:      [18]
# mogrified: age > 18

SQL.term('name', '=', 'Alice')
# sql:       name = %s
# args:      ['Alice']
# mogrified: name = 'Alice'

SQL.term('archived', '=', True)
# sql:       archived = %s
# mogrified: archived = true

Variant 3: NULL handling (expr, op, None)

When value is None, the operator is translated to SQL NULL syntax:

  • op='=' renders as IS NULL

  • op='<>' renders as IS NOT NULL

SQL.term('deleted_at', '=', None)
# deleted_at IS NULL

SQL.term('status', '<>', None)
# status IS NOT NULL

Variant 4: Subquery value (expr, op, SqlQuery)

When value is a SqlQuery (e.g. from SQL.select()), it is rendered as a parenthesized subquery after the operator.

SQL.term('producer_id', 'IN',
         SQL.select()
         .from_table('producers')
         .column('id')
         .where('name', '=', 'foo'))
# producer_id IN (
#     SELECT id
#     FROM producers
#     WHERE name = 'foo'
# )

SQL.term('id', '=',
         SQL.select()
         .from_table('config')
         .column('max_id'))
# id = (
#     SELECT max_id
#     FROM config
# )

Variant 5: EXISTS (SqlQuery expr, op=’EXISTS’)

When expr is a SqlQuery and op is 'EXISTS', the term renders as EXISTS (subquery). No value is used.

SQL.term(SQL.select()
         .from_table('orders')
         .column('1')
         .where('orders.user_id = users.id'),
         'EXISTS')
# EXISTS (
#     SELECT 1
#     FROM orders
#     WHERE orders.user_id = users.id
# )

Variant 6: Parameter placeholder (expr, op, SqlParam)

When value is a SqlParam instance (SQL.value() or SQL.named_value()), the placeholder is rendered directly without parentheses. Use this for explicit control over parameter style.

SQL.term('id', '=', SQL.named_value('id', 5))
# named style:    id = :id
# pyformat style: id = %(id)s
# args:           {'id': 5}

SQL.term('org_id', '=', SQL.value(7))
# format style: org_id = %s
# qmark style:  org_id = ?
# args:         [7]

SQL.term('status', '=', SQL.named_value('status'))
# Template slot (no value collected):
# named style: status = :status
# args:        {}

Variant 7: SqlObj value (expr, op, SqlObj)

When value is a SqlObj that is not a SqlQuery or SqlParam (e.g. a SqlCat), it is rendered in parentheses after the operator.

SQL.term('price', '>', SQL.cat('avg_price * 1.5'))
# price > (avg_price * 1.5)

Variant 8: SqlCat as expr

When expr is a SqlCat, it is rendered directly without extra parentheses (unlike other SqlObj types which get wrapped in parens).

SQL.term(SQL.cat("data ->> 'key'"), '=', 'value')
# sql:       data ->> 'key' = %s
# args:      ['value']
# mogrified: data ->> 'key' = 'value'

Variant 9: Other SqlObj as expr

When expr is a SqlObj that is not a SqlCat or SqlQuery, it is wrapped in parentheses.

SQL.term(SQL.and_expr()
         .add('a', '=', 1)
         .add('b', '=', 2), 'IS NOT NULL')
# (a = %s
#  AND b = %s) IS NOT NULL