Formatting

The format() classmethod on each dialect class converts a query object into a SQL string with proper indentation and line wrapping.

Basic Usage

from sqlexpr import PG as SQL

qb = (SQL.select()
      .from_table('users')
      .column('name', 'email')
      .where('active', '=', True))
fmt = SQL.format(qb)

sql = fmt.get_sql()
args = fmt.get_args()
sql, args = fmt.get_sqlargs()

SELECT name, email
FROM users
WHERE active = %s

Options

Right margin (default 80):

fmt = SQL.format(qb, right_margin=120)

Indent size (default 4):

fmt = SQL.format(qb, indent=2)

Line Wrapping

The formatter automatically wraps lines that exceed the right margin. It tracks potential break points (marks) and splits at the most recent mark when a line gets too long.

For example, a SELECT with many columns:

SELECT
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11,
    col12, col13

Parameter Styles

Different databases use different placeholder styles for bound parameters. Each dialect class uses the correct style automatically.

Every parameter in a query must either supply a value or omit it — you cannot mix the two. Omitting all values produces a reusable template; supplying all values produces executable SQL with bound arguments. See Template Mode below for details.

All examples below use the same query:

qb = (SQL.update('accounts')
      .set('balance', 100)
      .where(SQL.and_expr()
             .add('org_id', '=', 7)
             .add('status', '=', 'active')))

format (%s) — PG, MYSQL, MSSQL

Used by PostgreSQL (psycopg2), MySQL (pymysql), MSSQL (pymssql). Placeholders are %s. Arguments are collected as a list.

fmt = SQL.format(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = %s
# WHERE
#     org_id = %s
#     AND status = %s
fmt.get_args()   # [100, 7, 'active']

qmark (?) — SQLITE

Used by SQLite (sqlite3). Placeholders are ?. Arguments are collected as a list.

from sqlexpr import SQLITE as SQL

fmt = SQL.format(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = ?
# WHERE
#     org_id = ?
#     AND status = ?
fmt.get_args()   # [100, 7, 'active']

named (:name) — ORACLE

Used by Oracle (cx_Oracle, oracledb). Placeholders are :name. Arguments are collected as a dict.

Named styles require SQL.named_value(name, value) instead of raw values:

from sqlexpr import ORACLE as SQL

qb = (SQL.update('accounts')
      .set('balance', SQL.named_value('balance', 100))
      .where(SQL.and_expr()
             .add('org_id', '=', SQL.named_value('org_id', 7))
             .add('status', '=', SQL.named_value('status', 'active'))))
fmt = SQL.format(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = :balance
# WHERE
#     org_id = :org_id
#     AND status = :status
fmt.get_args()   # {'balance': 100, 'org_id': 7, 'status': 'active'}

numeric (:1, :2) — ORACLE

Also supported by Oracle. Placeholders are :1, :2, :3, etc. Arguments are collected as a list.

from sqlexpr import ORACLE as SQL

qb = (SQL.update('accounts')
      .set('balance', 100)
      .where(SQL.and_expr()
             .add('org_id', '=', 7)
             .add('status', '=', 'active')))
fmt = SQL.format_numeric(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = :1
# WHERE
#     org_id = :2
#     AND status = :3
fmt.get_args()   # [100, 7, 'active']

pyformat (%(name)s) — PG, MYSQL

Used by PostgreSQL (psycopg2 named mode) and MySQL (MySQLdb). Placeholders are %(name)s. Arguments are collected as a dict.

qb = (SQL.update('accounts')
      .set('balance', SQL.named_value('balance', 100))
      .where(SQL.and_expr()
             .add('org_id', '=', SQL.named_value('org_id', 7))
             .add('status', '=', SQL.named_value('status', 'active'))))
fmt = SQL.format_pyformat(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = %(balance)s
# WHERE
#     org_id = %(org_id)s
#     AND status = %(status)s
fmt.get_args()   # {'balance': 100, 'org_id': 7, 'status': 'active'}

Named Parameter Deduplication

When the same named parameter appears multiple times, the value only needs to be supplied on one occurrence — it doesn’t matter which one:

qb = (SQL.select()
      .from_table('documents')
      .column(SQL.cat('metadata -> ', SQL.named_value('elem', 'author'), " ->> 'name'"))
      .column(SQL.cat('metadata -> ', SQL.named_value('elem'), " ->> 'title'"))
      .where('id', '=', SQL.named_value('id', 42)))
fmt = SQL.format_pyformat(qb)
fmt.get_sql()
# SELECT metadata -> %(elem)s ->> 'name', metadata -> %(elem)s ->> 'title'
# FROM documents
# WHERE id = %(id)s
fmt.get_args()   # {'elem': 'author', 'id': 42}

The value can be supplied on any occurrence of the name. If the same name appears with conflicting values, a ValueError is raised.

Template Mode

Template mode produces a reusable SQL string with placeholders but no collected arguments. This is useful for preparing statements that will be executed multiple times with different parameter values.

Template mode is implicit: omit the value when constructing SQL.value() or SQL.named_value(name).

Positional template

qb = (SQL.update('accounts')
      .set('balance', SQL.value())
      .where(SQL.and_expr()
             .add('org_id', '=', SQL.value())
             .add('status', '=', SQL.value())))
fmt = SQL.format(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = %s
# WHERE
#     org_id = %s
#     AND status = %s
fmt.get_args()   # []

Named template

qb = (SQL.update('accounts')
      .set('balance', SQL.named_value('balance'))
      .where(SQL.and_expr()
             .add('org_id', '=', SQL.named_value('org_id'))
             .add('status', '=', SQL.named_value('status'))))
fmt = SQL.format_pyformat(qb)
fmt.get_sql()
# UPDATE accounts
# SET balance = %(balance)s
# WHERE
#     org_id = %(org_id)s
#     AND status = %(status)s
fmt.get_args()   # {}

The resulting SQL can then be executed repeatedly:

sql = fmt.get_sql()
cur.execute(sql, {'balance': 100, 'org_id': 7, 'status': 'active'})
cur.execute(sql, {'balance': 200, 'org_id': 8, 'status': 'pending'})

Mixing valued and unvalued parameters in the same query raises a ValueError.