SqlSelect

class SqlSelect

Builds a SELECT query. Created via SQL.select(). Inherits from SqlStatement (via SqlQuery).

In the examples below, qb refers to a SqlSelect instance:

qb = SQL.select()

column(name, alias=None)

Add a single column to the SELECT list.

Parameters:

name – Column expression — a string, SqlCat, or subquery.
alias – Optional alias string. Renders as name AS alias.

qb.column('name')
# SELECT name

qb.column('count(*)', 'total')
# SELECT count(*) AS total

qb.column(SQL.cat('a + b'), 'sum')
# SELECT a + b AS sum

columns(*columns)

Add multiple columns at once.

Parameters:: columns – Strings, tuples, or SqlColumn instances. A single list argument is also accepted. Tuples are unpacked as SqlColumn(*tuple).

qb.columns('id', 'name', 'email')
# SELECT id, name, email

reset_columns(): Clear the column list. Useful when reusing a base query.

has_column(alias)

Check if a column alias already exists.

Parameters:: alias – The alias string to check.
Return type:: bool

if not qb.has_column('total'):
    qb.column('count(*)', 'total')

get_columns()

Returns the list of SqlColumn objects.

Return type:: list[SqlColumn]

distinct(*expr)

Mark as DISTINCT, optionally with DISTINCT ON expressions.

Parameters:: expr – Zero or more column expressions for DISTINCT ON. If none given, renders as plain DISTINCT.

qb.distinct()
# SELECT
#     DISTINCT
#     ...

qb.distinct('location')
# SELECT
#     DISTINCT ON (location)
#     ...

from_table(table, alias=None)

Add a FROM table.

Parameters:

table – Table name string, SqlCat expression, or SqlQuery subquery (SQL.select(), or a SQL.insert()/SQL.update()/ SQL.delete() with a RETURNING clause).
alias – Optional alias string.

qb.from_table('users')
# FROM users

qb.from_table('users', 'u')
# FROM users u

qb.from_table(SQL.cat('generate_series(1,10)'))
# FROM generate_series(1,10)

qb.from_table(other_select, 'sub')
# FROM
#     (
#         SELECT *
#         FROM t
#     ) sub

join(join)

Add a SqlJoin instance.

Parameters:: join – A SqlJoin object (created via SQL.join()).

qb.join(SQL.join('JOIN', 'orders', 'o', 'o.user_id = u.id'))
# JOIN orders o ON o.user_id = u.id

join_table(table, alias, expr)

Shorthand for adding a JOIN.

Parameters:

table – Table name string or expression.
alias – Table alias.
expr – Join condition string or SqlObj.

qb.join_table('orders', 'o', 'o.user_id = u.id')
# JOIN orders o ON o.user_id = u.id

left_join_table(table, alias, expr)

Shorthand for adding a LEFT JOIN. Same parameters as join_table().

qb.left_join_table('addresses', 'a', 'a.user_id = u.id')
# LEFT JOIN addresses a ON a.user_id = u.id

where(*expr)

Set the WHERE clause. Accepts either a single SqlObj (or None to clear), or positional args (expr, op, value) which are wrapped in a SqlTerm. See SqlTerm for the full list of supported parameter variants.

qb.where('active', '=', True)
# sql:       WHERE active = %s
# args:      [True]
# mogrified: WHERE active = true

qb.where(SQL.and_expr()
         .add('age', '>', 18)
         .add('country', '=', 'AU'))
# WHERE
#     age > %s
#     AND country = %s
# args: [18, 'AU']

qb.where(None)  # clear the WHERE clause

get_where()

Returns the current WHERE expression, or None.

Return type:: SqlObj or None

group_by(*columns)

Set GROUP BY columns.

Parameters:: columns – One or more column name strings.

qb.group_by('department')
# GROUP BY department

qb.group_by('department', 'role')
# GROUP BY department, role

having(expr)

Set the HAVING clause.

Parameters:: expr – A SqlObj expression (typically SqlTerm, SqlAnd, or SqlCat).

qb.having(SQL.cat('count(*) > ', SQL.value(5)))
# sql:       HAVING count(*) > %s
# args:      [5]
# mogrified: HAVING count(*) > 5

window(expr)

Add a WINDOW clause.

Parameters:: expr – A PgWindow instance (created via SQL.window()).

qb.window(SQL.window('w').partition_by('dept').order_by('salary DESC'))
# WINDOW
#     w AS (
#         PARTITION BY dept
#         ORDER BY salary DESC
#     )

union(union)

Add a SqlUnion instance.

Parameters:: union – A SqlUnion object (created via SQL.union()).

qb.union(SQL.union('UNION ALL', other_select))

union_query(select)

Shorthand for adding a UNION with another select.

Parameters:: select – A SqlQuery (SQL.select(), SQL.insert(), SQL.update(), or SQL.delete() with RETURNING) to union with.

qb.union_query(SQL.select().from_table('b').column('id'))
# UNION
# SELECT id
# FROM b

order_by(*order_by)

Set ORDER BY expressions.

Parameters:: order_by – One or more strings specifying column and direction.

qb.order_by('name')
# ORDER BY name

qb.order_by('salary DESC', 'name ASC')
# ORDER BY salary DESC, name ASC

limit(limit)

Set the LIMIT value.

Parameters:: limit – An integer or SqlObj expression.

qb.limit(10)
# LIMIT 10

offset(offset)

Set the OFFSET value.

Parameters:: offset – An integer or SqlObj expression.

qb.offset(20)
# OFFSET 20

fetch(fetch)

Set the FETCH clause (SQL standard alternative to LIMIT).

Parameters:: fetch – A SqlObj expression describing the fetch spec.

qb.fetch(SQL.cat('FIRST ', 5, ' ROWS ONLY'))
# FETCH FIRST 5 ROWS ONLY

into(into)

Set the INTO target for SELECT INTO.

Parameters:: into – Target table name string.

qb.into('films_recent')
# SELECT *
# INTO films_recent
# FROM films

for_locking(expr)

Add a FOR locking clause.

Parameters:: expr – A string (e.g. 'UPDATE') or PgForLocking instance (created via SQL.for_locking()).

qb.for_locking('UPDATE')
# FOR UPDATE

qb.for_locking(SQL.for_locking('NO KEY UPDATE').of('t').locking('SKIP LOCKED'))
# FOR NO KEY UPDATE OF t SKIP LOCKED

recursive(): Mark the WITH clause as RECURSIVE.

with_cte(cte)

Add a SqlCte instance to the WITH clause.

Parameters:: cte – A SqlCte object (created via SQL.cte()).

cte = SQL.cte('active_users', active_query).materialized()
qb.with_cte(cte)
# WITH active_users AS MATERIALIZED (
#     ...
# )

with_cte_query(alias, query)

Shorthand for adding a CTE.

Parameters:

alias – CTE name string.
query – A SqlQuery (SQL.select(), SQL.insert(), SQL.update(), or SQL.delete() with RETURNING) for the CTE body.

qb.with_cte_query('active',
                  SQL.select().from_table('users').column('*')
                  .where('active', '=', True))
# WITH active AS (
#     SELECT *
#     FROM users
#     WHERE active = %s
# )
# args: [True]