Class Hierarchy

sqlexpr has two class hierarchies: SqlObj for SQL expressions and query objects, and SqlParam for parameter placeholders.

SqlParam

class SqlParam

Base class for parameter placeholders. These are not SQL expressions — they represent bound parameter values that emit a placeholder and optionally collect an argument. They are handled specially by the formatter via fmt.param().

sql(fmt)

Emit the placeholder via fmt.param(self).

SqlParam
├── SqlValue
└── SqlNamedValue

SqlObj

class SqlObj

Base class for all SQL expression and query objects. Every class that produces SQL structure inherits from SqlObj. This is what allows objects to be composed — any method that accepts a SqlObj will accept any sqlexpr object (a term, a subquery, a cat expression, etc.).

You do not instantiate SqlObj directly. It defines the interface that SqlFormat uses to render queries.

sql(fmt)

Render this object into the formatter. Called by SqlFormat during query rendering. Each subclass implements this to write its SQL representation.

Parameters:

fmt – The SqlFormat instance accumulating the output.

is_empty()

Returns True if this object produces no SQL output. Used by boolean expressions and WHERE clauses to skip empty terms.

Return type:

bool

is_complex()

Returns True if this object should be rendered on multiple lines (e.g. a multi-term AND/OR expression). Used by the formatter to decide whether to break after WHERE.

Return type:

bool

get_len()

Returns the character length of the rendered output, or None if the length cannot be determined without rendering (e.g. subqueries). Used by the formatter to decide whether a SELECT list fits on one line.

Return type:

int or None

SqlObj
├── SqlCat
├── SqlColumn
├── SqlColumnList
├── SqlColumnValue
├── SqlTerm
├── SqlBoolOp
│   ├── SqlAnd
│   └── SqlOr
├── SqlCte
│   └── PgCte
├── SqlJoin
├── SqlFromItem
├── SqlUnion
├── PgWindow
├── PgForLocking
└── SqlStatement
    ├── SqlCreateTable
    ├── SqlCreateIndex
    ├── SqlDropTable
    └── SqlQuery
        ├── SqlSelect
        │   └── PgSelect
        ├── SqlInsert
        │   ├── PgInsert
        │   ├── MysqlInsert
        │   └── SqliteInsert
        ├── SqlUpdate
        │   └── PgUpdate
        └── SqlDelete
            ├── PgDelete
            └── MysqlDelete

SqlStatement is a subclass of SqlObj for any top-level SQL statement that can be sent to the database as a complete unit. It provides the comment() method for attaching SQL comments to statements.

SqlQuery is a subclass of SqlStatement that marks objects which represent DML queries (SELECT, INSERT, UPDATE, DELETE). It adds WITH/CTE support. This distinction is used throughout the formatter to decide when to wrap a value in parentheses as a subquery.

Special handling of SqlQuery instances occurs in:

  • SqlTerm — when value is a SqlQuery, it is rendered indented inside parentheses: op (\n    subquery\n). When expr is a SqlQuery with op='EXISTS', it renders as EXISTS (\n    subquery\n).

  • SqlInsert (VALUES clause) — when a column value is a SqlQuery, it is wrapped in parentheses as a scalar subquery within the VALUES list.

  • SqlColumn — when name is a SqlQuery, it is rendered in parentheses as a derived column. When alias is a SqlQuery, it renders as a CTE-style AS (subquery).

  • SqlFromItem (used by from_table()) — when the table is a SqlQuery, it is rendered as a derived table: (\n    subquery\n) alias.

  • SqlJoin — when the table is a SqlQuery, same parenthesized rendering.

In contrast, a plain SqlObj (like SqlCat) is rendered inline without parentheses or indentation.

SqlStatement

class SqlStatement

Base class for any top-level SQL statement. Subclassed by SqlQuery (DML with CTEs) and DDL classes (SqlCreateTable, SqlCreateIndex, SqlDropTable).

Provides comment support — a SQL comment block emitted before the statement.

comment(comment)

Set a SQL comment that will be emitted as ``– `` prefixed lines before the statement.

Parameters:

comment – A string (may contain newlines for multi-line comments).

Returns:

self for method chaining.

qb = (SQL.select()
      .from_table('users')
      .column('*')
      .comment('Fetch all active users'))

# -- Fetch all active users
# SELECT *
# FROM users

Multi-line comments:

qb.comment('First line\nSecond line')

# -- First line
# -- Second line
# SELECT ...

Interaction with SqlFormat

When you call SQL.format(query), the formatter:

  1. Calls query.sql(fmt) on the top-level query object.

  2. Each object writes its SQL by calling methods on the fmt instance:

    • fmt.write(text) — write literal SQL text.

    • fmt.write_text(value) — write a value: if it’s a SqlObj or SqlParam, calls value.sql(fmt); otherwise converts to string and writes literally.

    • fmt.write_query(query) — write a SqlQuery as a parenthesized, indented subquery: (\n    query\n).

    • fmt.param(value_obj) — emit a parameter placeholder and collect the argument. Accepts SqlValue or SqlNamedValue.

    • fmt.comment(text) — write a ``– `` prefixed comment block.

    • fmt.indent() / fmt.dedent() — manage indentation.

    • fmt.mark() — set a potential line-break point.

  3. After rendering completes, fmt.get_sql() returns the formatted string and fmt.get_args() returns the collected parameters.

This means any SqlObj can be nested inside any other — a SqlSelect inside a SqlTerm, a SqlCat inside a SqlColumn, etc. The formatter handles indentation and line wrapping automatically.

Parameter Handling

Parameters are handled via SqlParam objects (SqlValue and SqlNamedValue). When a parameter is encountered during rendering, it calls fmt.param(self) which emits the appropriate placeholder for the formatter’s style and collects the argument value.

The rules for when a parameter is created vs. when SQL is written literally:

  • SqlValue (SQL.value()) — emits a positional placeholder and adds its value to the argument list.

  • SqlNamedValue (SQL.named_value()) — emits a named placeholder and adds its value to the argument dict.

  • SqlTerm (SQL.term()) — if value is not None, not a SqlObj, and not a SqlParam, wraps it in SqlValue and emits a placeholder. If value is a SqlParam, calls its .sql() directly. If value is a SqlObj (e.g. a subquery), it is rendered via its .sql() method in parentheses.

  • SqlInsert (SQL.insert()) — for each column/value pair, if the value is a SqlParam it is emitted directly; if it is a SqlObj it is rendered as SQL; otherwise it is wrapped in SqlValue.

  • SqlCat (SQL.cat()) — does not create parameters. Non-SqlObj parts are converted to strings and written literally. Use SQL.value() or SQL.named_value() inside a SQL.cat() to create a parameterized value.

In summary: if you pass a plain Python value (string, int, bool, etc.) where a SQL value is expected, it becomes a parameter. If you pass a SqlObj, it is rendered as SQL. If you want a literal string in the SQL output (not parameterized), wrap it in SQL.cat().