Expressions

sqlexpr provides composable expression objects for building WHERE clauses, column expressions, and other SQL fragments. See the reference docs for SqlTerm, SqlAnd, SqlOr, SqlValue, SqlCat, and SqlColumn.

SqlTerm

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

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

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

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

With a subquery:

SQL.term('id', 'IN',
         SQL.select()
         .from_table('active')
         .column('id'))

id IN (
    SELECT id
    FROM active
)

EXISTS:

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
)

SqlAnd / SqlOr

Combine terms with boolean logic:

expr = (SQL.and_expr()
        .add('status', '=', 'active')
        .add('age', '>', 18)
        .add('country', '=', 'AU'))

status = %s
AND age > %s
AND country = %s

-- get_args(): ['active', 18, 'AU']

Nested boolean expressions:

expr = (SQL.and_expr()
        .add('active', '=', True)
        .add(SQL.or_expr()
             .add('role', '=', 'admin')
             .add('role', '=', 'superuser')))

active = %s
AND (role = %s
     OR role = %s)

-- get_args(): [True, 'admin', 'superuser']

SqlValue

SQL.value(42)
# sql: %s
# args: [42]

SQL.value('hello')
# sql: %s
# args: ['hello']

SqlCat

Concatenate SQL fragments and values:

SQL.cat('now() - interval ', SQL.value('1 hour'))
# sql: now() - interval %s
# args: ['1 hour']

SqlColumn

SQL.column('count(*)', 'total')
# sql: count(*) AS total

SQL.column('name')
# sql: name