SELECT Queries

Builds SELECT queries using SqlSelect.

All SQL output below shows the result of get_sql(). Where queries have parameters, get_args() is shown alongside.

Basic Select

qb = (SQL.select()
      .from_table('films')
      .column('title')
      .column('director')
      .where('year', '>', 2000))

SELECT title, director
FROM films
WHERE year > %s

-- get_args(): [2000]

Multiple Columns

Add columns individually or in bulk:

# Individual
qb.column('name', 'alias')

# Bulk
qb.columns('col1', 'col2', 'col3')

# With aliases (tuple form)
qb.columns(('count(*)', 'total'), 'name')

The columns() method processes each item through SqlColumn.from_list(): strings become SqlColumn(name), and tuples are unpacked as positional arguments to SqlColumn(*item), so ('count(*)', 'total') becomes SqlColumn('count(*)', 'total'). For clarity, prefer passing explicit SqlColumn instances:

qb.columns(SQL.column('count(*)', 'total'), 'name')

DISTINCT

PostgreSQL only: DISTINCT ON

qb = (SQL.select()
      .from_table('weather_reports')
      .distinct('location')
      .columns('location', 'time', 'report'))

SELECT
    DISTINCT ON (location)
    location, time, report
FROM weather_reports

Tables and Joins

Multiple tables:

qb = (SQL.select()
      .from_table('employees', 'e')
      .from_table('departments', 'd')
      .column('e.name')
      .where('e.dept_id = d.id'))

SELECT e.name
FROM
    employees e,
    departments d
WHERE e.dept_id = d.id

Joins:

qb = (SQL.select()
      .from_table('employees', 'e')
      .join(SQL.join('JOIN', 'departments', 'd', 'd.id = e.dept_id'))
      .column('e.name')
      .column('d.name', 'dept_name'))

SELECT e.name, d.name AS dept_name
FROM
    employees e
    JOIN departments d ON d.id = e.dept_id

Convenience methods for common join types:

qb.join_table('departments', 'd', 'd.id = e.dept_id')
qb.left_join_table('managers', 'm', 'm.id = e.manager_id')

Subqueries as tables:

sub = (SQL.select()
       .from_table('orders')
       .column('customer_id')
       .column('sum(amount)', 'total')
       .group_by('customer_id'))

qb = (SQL.select()
      .from_table(sub, 'totals')
      .column('*'))

SELECT *
FROM
    (
        SELECT customer_id, sum(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) totals

WHERE Clause

The .where() method accepts the same parameter variants as SqlTerm.

Simple conditions:

qb.where('status', '=', 'active')

Complex conditions using boolean expressions (see SqlAnd):

qb.where(SQL.and_expr()
         .add('status', '=', 'active')
         .add('age', '>', 18))

WHERE
    status = %s
    AND age > %s

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

NULL handling (automatic):

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

Subquery in WHERE:

qb.where('id', 'IN',
         SQL.select()
         .from_table('active_users')
         .column('id'))

WHERE
    id IN (
        SELECT id
        FROM active_users
    )

GROUP BY and HAVING

qb = (SQL.select()
      .from_table('films')
      .column('kind')
      .column('sum(len)', 'total')
      .group_by('kind')
      .having(SQL.cat('sum(len) < interval ', SQL.value('5 hours'))))

SELECT kind, sum(len) AS total
FROM films
GROUP BY kind
HAVING sum(len) < interval %s

-- get_args(): ['5 hours']

ORDER BY, LIMIT, OFFSET

qb = (SQL.select()
      .from_table('products')
      .column('name')
      .order_by('price DESC', 'name')
      .limit(10)
      .offset(20))

SELECT name
FROM products
ORDER BY price DESC, name
LIMIT 10
OFFSET 20

WINDOW Clauses

PostgreSQL only.

See PgWindow for the full window API.

qb = (SQL.select()
      .from_table('employees')
      .column('name')
      .column('salary')
      .column('avg(salary) OVER w', 'dept_avg')
      .window(SQL.window('w')
              .partition_by('department')
              .order_by('salary DESC')))

SELECT name, salary, avg(salary) OVER w AS dept_avg
FROM employees
WINDOW
    w AS (
        PARTITION BY department
        ORDER BY salary DESC
    )

Common Table Expressions (WITH)

See SqlCte for the CTE API.

qb = (SQL.select()
      .with_cte_query('active',
                      SQL.select()
                      .from_table('users')
                      .column('*')
                      .where('active', '=', True))
      .from_table('active')
      .column('name'))

WITH active AS (
    SELECT *
    FROM users
    WHERE active = %s
)
SELECT name
FROM active

-- get_args(): [True]

Materialized CTEs (PostgreSQL only):

qb.with_cte(SQL.cte('expensive_query', some_select).materialized())

Recursive CTEs:

qb = (SQL.select()
      .recursive()
      .with_cte_query('tree', recursive_query)
      .from_table('tree')
      .column('*'))

UNION and Set Operations

See SqlUnion for the union API.

qb = (SQL.select()
      .column('name')
      .from_table('employees')
      .union_query(SQL.select()
                   .column('name')
                   .from_table('contractors')))

SELECT name
FROM employees
UNION
SELECT name
FROM contractors

For UNION ALL:

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

SELECT INTO

PostgreSQL only.

qb = (SQL.select()
      .column('*')
      .from_table('films')
      .into('films_recent')
      .where('date_prod', '>=', '2002-01-01'))

SELECT *
INTO films_recent
FROM films
WHERE date_prod >= %s

-- get_args(): ['2002-01-01']

FETCH

PostgreSQL only.

qb = (SQL.select()
      .columns('id', 'name')
      .from_table('employees')
      .order_by('salary DESC')
      .fetch(SQL.cat('FIRST ', 5, ' ROWS ONLY')))

SELECT id, name
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY

FOR UPDATE / Locking

PostgreSQL only.

See PgForLocking for the full locking API.

Simple locking:

qb.for_locking('UPDATE')

Advanced locking:

qb.for_locking(SQL.for_locking('NO KEY UPDATE')
               .of('employees', 'tasks')
               .locking('SKIP LOCKED'))

FOR NO KEY UPDATE OF employees, tasks SKIP LOCKED

Subqueries

Pass a SqlSelect as the table argument to from_table() to use it as a derived table. The formatter automatically wraps it in parentheses:

sub = (SQL.select()
       .from_table('orders')
       .column('max(amount)')
       .where('customer_id = c.id'))

qb = SQL.select().from_table(sub, 'o').column('*')

SELECT *
FROM
    (
        SELECT max(amount)
        FROM orders
        WHERE customer_id = c.id
    ) o