API Reference

This section contains the auto-generated API documentation.

sqlo - A modern, type-safe, and extensible SQL query builder for Python.

class sqlo.Q

Bases: object

Query Builder Factory.

static delete_from(table)

Parameters:

table (str)

Return type:

DeleteQuery

classmethod get_dialect()

Get the current default dialect.

static insert_into(table)

Parameters:

table (str)

Return type:

InsertQuery

static raw(sql, params=None)

Parameters:

• sql (str)

• params (Any)

Return type:

Raw

static select(*columns)

Parameters:

columns (str | Raw | Func)

Return type:

SelectQuery[Any]

classmethod set_debug(debug=True)

Enable or disable debug mode (prints SQL to stdout).

Parameters:

debug (bool)

classmethod set_dialect(dialect)

Set the default dialect for all queries.

static update(table)

Parameters:

table (str)

Return type:

UpdateQuery

class sqlo.Raw(sql, params=None)

Bases: Expression

Raw SQL fragment.

WARNING: Raw SQL bypasses parameter binding and can be vulnerable to SQL injection. Only use Raw() with trusted input or when you need to reference columns/functions.

For dynamic user input, always use parameterized queries instead.

Parameters:

• sql (str)

• params (list[Any] | tuple[Any, ...] | None)

class sqlo.Func(name, *args)

Bases: Expression

Represents a SQL function call.

This class allows you to wrap any SQL function (e.g., COUNT, MAX, AVG, custom functions) and use it within your queries. It supports arguments and aliasing.

Parameters:

• name (str) – The name of the SQL function (e.g., “COUNT”).

• *args (Any) – Arguments to pass to the function.

Example

>>> f = Func("COUNT", "*")
>>> f.as_("total")

over(window=None)

Create a window function with OVER clause.

Parameters:

window (Any | None) – Optional Window object for PARTITION BY / ORDER BY

Returns:

WindowFunc object

Return type:

Any

Example

>>> func.row_number().over(Window.partition_by("dept"))

class sqlo.Condition(column=None, value=None, operator='=')

Bases: Expression

Represents a SQL condition for use in WHERE or HAVING clauses.

This class allows you to build simple or complex conditions with support for compact operator syntax (e.g., “age>”) and standard syntax (e.g., “age >”). Conditions can be combined using bitwise operators (& and |) or static methods.

Parameters:

• column (str, optional) – Column name with optional operator (e.g., “age>=” or “age >=”).

• value (Any, optional) – The value to compare against. Can be a list for IN operator.

• operator (str, optional) – The comparison operator if not included in column. Defaults to “=”.

Examples

>>> # Simple condition
>>> c = Condition("age>=", 18)
>>> sql, params = c.build()
>>>
>>> # IN operator
>>> c = Condition("status", ["pending", "done"], operator="IN")
>>>
>>> # IS NULL
>>> c = Condition("deleted_at", operator="IS NULL")
>>>
>>> # Static factory methods
>>> c = Condition.null("deleted_at")
>>> c = Condition.in_("status", ["pending", "done"])
>>> c = Condition.exists(subquery)
>>>
>>> # Combining conditions
>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> combined = c1 & c2  # AND
>>> combined = c1 \| c2  # OR

static and_(*conditions)

Combine multiple conditions with AND logic.

Parameters:

*conditions (Condition) – Variable number of Condition objects

Returns:

Combined Condition object

Return type:

Condition

Example

>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> c3 = Condition("verified", True)
>>> combined = Condition.and_(c1, c2, c3)
>>> # SQL: (`age` >= ? AND `country` = ? AND `verified` = ?)

build(dialect=None)

Build the condition and return (sql, params) tuple.

Parameters:

dialect – Optional dialect to use for quoting. Defaults to MySQLDialect.

Returns:

Tuple of (sql_string, params_tuple)

Return type:

tuple[str, tuple[Any, …]]

Example

>>> c = Condition("age>=", 18)
>>> sql, params = c.build()
>>> assert sql == "`age` >= ?"
>>> assert params == (18,)

static exists(subquery)

Create an EXISTS condition with a subquery.

Parameters:

subquery (SelectQuery) – SelectQuery object

Returns:

Condition object

Return type:

Condition

Example

>>> subquery = Q.select("1").from_("orders").where(Condition("user_id", 123))
>>> c = Condition.exists(subquery)
>>> sql, params = c.build()
>>> # SQL: EXISTS (SELECT 1 FROM `orders` WHERE `user_id` = ?)

static in_(column, values)

Create an IN condition.

Parameters:

• column (str) – Column name

• values (list[Any] | tuple[Any, ...] | SelectQuery) – List/tuple of values or a SelectQuery subquery

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.in_("status", ["pending", "done"])
>>> sql, params = c.build()
>>> # SQL: `status` IN (?, ?)

static not_exists(subquery)

Create a NOT EXISTS condition with a subquery.

Parameters:

subquery (SelectQuery) – SelectQuery object

Returns:

Condition object

Return type:

Condition

Example

>>> subquery = Q.select("1").from_("orders").where(Condition("user_id", 123))
>>> c = Condition.not_exists(subquery)
>>> sql, params = c.build()
>>> # SQL: NOT EXISTS (SELECT 1 FROM `orders` WHERE `user_id` = ?)

static not_in(column, values)

Create a NOT IN condition.

Parameters:

• column (str) – Column name

• values (list[Any] | tuple[Any, ...] | SelectQuery) – List/tuple of values or a SelectQuery subquery

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.not_in("status", ["canceled", "failed"])
>>> sql, params = c.build()
>>> # SQL: `status` NOT IN (?, ?)

static not_null(column)

Create an IS NOT NULL condition.

Parameters:

column (str) – Column name to check for NOT NULL

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.not_null("email")
>>> sql, params = c.build()
>>> # SQL: `email` IS NOT NULL

static null(column)

Create an IS NULL condition.

Parameters:

column (str) – Column name to check for NULL

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.null("deleted_at")
>>> sql, params = c.build()
>>> # SQL: `deleted_at` IS NULL

static or_(*conditions)

Combine multiple conditions with OR logic.

Parameters:

*conditions (Condition) – Variable number of Condition objects

Returns:

ComplexCondition object

Return type:

Condition | ComplexCondition

Example

>>> c1 = Condition("status", "pending")
>>> c2 = Condition("status", "processing")
>>> combined = Condition.or_(c1, c2)
>>> # SQL: (`status` = ? OR `status` = ?)

class sqlo.JSON(column)

Bases: Expression

Represents a JSON column.

Example

>>> JSON("data").extract("name")

Parameters:

column (str)

extract(path)

Extract a value from the JSON column.

Parameters:

path (str)

Return type:

JSONPath

class sqlo.JSONPath(column, path)

Bases: Expression

Represents a JSON path extraction.

Parameters:

• column (str)

• path (str)

class sqlo.Window

Bases: object

Represents a SQL window specification for window functions.

Example

>>> Window.partition_by("department").order_by("-salary")
>>> Window.order_by("date").rows_between("UNBOUNDED PRECEDING", "CURRENT ROW")

and_order_by(*columns)

Add ORDER BY clause to the window.

Parameters:

columns (str)

Return type:

Window

and_partition_by(*columns)

Add PARTITION BY clause to the window.

Parameters:

columns (str)

Return type:

Window

build(dialect)

Build the window specification SQL.

Parameters:

dialect (Any)

Return type:

str

classmethod order_by(*columns)

Create a window with ORDER BY clause.

Parameters:

columns (str)

Return type:

Window

classmethod partition_by(*columns)

Create a window with PARTITION BY clause.

Parameters:

columns (str)

Return type:

Window

range_between(start, end)

Add RANGE BETWEEN frame clause.

Parameters:

• start (str) – Start of the frame

• end (str) – End of the frame

Return type:

Window

rows_between(start, end)

Add ROWS BETWEEN frame clause.

Parameters:

• start (str) – Start of the frame (e.g., “UNBOUNDED PRECEDING”, “1 PRECEDING”, “CURRENT ROW”)

• end (str) – End of the frame (e.g., “CURRENT ROW”, “1 FOLLOWING”, “UNBOUNDED FOLLOWING”)

Return type:

Window

class sqlo.query.select.SelectQuery(*columns, dialect=None, debug=False)

Bases: WhereClauseMixin, Query, Generic[T]

Parameters:

columns (str | Raw | Func | list)

from_(table, alias=None)

Parameters:

• table (str | SelectQuery)

• alias (str | None)

Return type:

SelectQuery

join(table, on=None, join_type='INNER')

Parameters:

• table (str)

• on (str | None)

• join_type (str)

Return type:

SelectQuery

inner_join(table, on=None)

Parameters:

• table (str)

• on (str | None)

Return type:

SelectQuery[T]

left_join(table, on=None)

Parameters:

• table (str)

• on (str | None)

Return type:

SelectQuery[T]

right_join(table, on=None)

Parameters:

• table (str)

• on (str | None)

Return type:

SelectQuery[T]

cross_join(table)

Parameters:

table (str)

Return type:

SelectQuery[T]

where(column, value=None, operator='=')

Parameters:

• column (str | Raw | Condition | ComplexCondition)

• value (Any)

• operator (str)

Return type:

SelectQuery[T]

where_in(column, values)

Add an IN WHERE condition.

Parameters:

• column (str)

• values (list[Any] | SelectQuery)

Return type:

SelectQuery[T]

where_not_in(column, values)

Add a NOT IN WHERE condition.

Parameters:

• column (str)

• values (list[Any] | SelectQuery)

Return type:

SelectQuery[T]

or_where(column, value=None, operator='=')

Add an OR WHERE condition.

Parameters:

• column (str | Raw | Condition | ComplexCondition)

• value (Any)

• operator (str)

Return type:

SelectQuery[T]

where_null(column)

Add an IS NULL WHERE condition.

Parameters:

column (str)

Return type:

SelectQuery[T]

where_not_null(column)

Add an IS NOT NULL WHERE condition.

Parameters:

column (str)

Return type:

SelectQuery[T]

where_between(column, value1, value2)

Add a BETWEEN WHERE condition.

Parameters:

• column (str)

• value1 (Any)

• value2 (Any)

Return type:

SelectQuery[T]

where_not_between(column, value1, value2)

Add a NOT BETWEEN WHERE condition.

Parameters:

• column (str)

• value1 (Any)

• value2 (Any)

Return type:

SelectQuery[T]

where_like(column, pattern)

Add a LIKE WHERE condition.

Parameters:

• column (str)

• pattern (str)

Return type:

SelectQuery[T]

where_not_like(column, pattern)

Add a NOT LIKE WHERE condition.

Parameters:

• column (str)

• pattern (str)

Return type:

SelectQuery[T]

order_by(*columns)

Parameters:

columns (str | Raw)

Return type:

SelectQuery[T]

limit(limit)

Parameters:

limit (int)

Return type:

SelectQuery[T]

offset(offset)

Parameters:

offset (int)

Return type:

SelectQuery[T]

group_by(*columns)

Parameters:

columns (str)

Return type:

SelectQuery[T]

when(condition, callback)

Parameters:

• condition (Any)

• callback (Callable[[SelectQuery[T]], None])

Return type:

SelectQuery[T]

paginate(page, per_page)

Parameters:

• page (int)

• per_page (int)

Return type:

SelectQuery

force_index(*indexes)

Parameters:

indexes (str)

Return type:

SelectQuery

use_index(*indexes)

Parameters:

indexes (str)

Return type:

SelectQuery

ignore_index(*indexes)

Parameters:

indexes (str)

Return type:

SelectQuery

optimizer_hint(hint)

Add an optimizer hint (MySQL 8.0+).

Parameters:

hint (str)

Return type:

SelectQuery

explain()

Return type:

SelectQuery

distinct()

Return type:

SelectQuery

having(column, value=None, operator='=')

Parameters:

• column (str | Raw | Condition | ComplexCondition)

• value (Any)

• operator (str)

Return type:

SelectQuery

union(query)

Parameters:

query (SelectQuery)

Return type:

SelectQuery[T]

union_all(query)

Parameters:

query (SelectQuery)

Return type:

SelectQuery[T]

as_(alias)

Set an alias for this query (used in FROM clauses).

Parameters:

alias (str)

Return type:

SelectQuery

build()

Build the query and return (sql, params).

Return type:

tuple[str, tuple[Any, …]]

class sqlo.query.insert.InsertQuery(table, dialect=None, debug=False)

Bases: Query

Parameters:

table (str)

values(values)

Parameters:

values (dict[str, Any] | list[dict[str, Any]])

Return type:

InsertQuery

ignore()

Return type:

InsertQuery

on_duplicate_key_update(values)

Parameters:

values (dict[str, Any])

Return type:

InsertQuery

from_select(columns, select_query)

Insert data from a SELECT query.

Parameters:

• columns (list[str])

• select_query (SelectQuery)

Return type:

InsertQuery

build()

Build the query and return (sql, params).

Return type:

tuple[str, tuple[Any, …]]

class sqlo.query.update.UpdateQuery(table, dialect=None, debug=False)

Bases: WhereClauseMixin, Query

Parameters:

table (str)

set(values)

Parameters:

values (dict[str, Any])

Return type:

UpdateQuery

join(table, on=None, join_type='INNER')

Add a JOIN clause (MySQL multi-table UPDATE).

Parameters:

• table (str)

• on (str | None)

• join_type (str)

Return type:

UpdateQuery

left_join(table, on=None)

Add a LEFT JOIN clause.

Parameters:

• table (str)

• on (str | None)

Return type:

UpdateQuery

where(column, value=None, operator='=')

Parameters:

• column (str | Raw | Condition | ComplexCondition)

• value (Any)

• operator (str)

Return type:

UpdateQuery

limit(limit)

Parameters:

limit (int)

Return type:

UpdateQuery

order_by(*columns)

Parameters:

columns (str)

Return type:

UpdateQuery

allow_all_rows()

Allow UPDATE without WHERE clause (updates all rows).

This is a safety feature to prevent accidental mass updates. You must call this method explicitly if you want to update all rows.

Returns:

Self for method chaining

Return type:

UpdateQuery

Example

>>> Q.update("users").set({"active": False}).allow_all_rows().build()

batch_update(values, key)

Perform a batch update using CASE WHEN.

Parameters:

• values (list[dict[str, Any]]) – List of dictionaries containing values to update. Each dictionary must contain the key column.

• key (str) – The column name to use as the key (e.g., “id”).

Return type:

UpdateQuery

build()

Build the query and return (sql, params).

Return type:

tuple[str, tuple[Any, …]]

class sqlo.query.delete.DeleteQuery(table, dialect=None, debug=False)

Bases: WhereClauseMixin, Query

Parameters:

table (str)

join(table, on=None, join_type='INNER')

Add a JOIN clause (MySQL multi-table DELETE).

Parameters:

• table (str)

• on (str | None)

• join_type (str)

Return type:

DeleteQuery

left_join(table, on=None)

Add a LEFT JOIN clause.

Parameters:

• table (str)

• on (str | None)

Return type:

DeleteQuery

where(column, value=None, operator='=')

Parameters:

• column (str | Raw | Condition | ComplexCondition)

• value (Any)

• operator (str)

Return type:

DeleteQuery

limit(limit)

Parameters:

limit (int)

Return type:

DeleteQuery

order_by(*columns)

Parameters:

columns (str)

Return type:

DeleteQuery

allow_all_rows()

Allow DELETE without WHERE clause (deletes all rows).

This is a safety feature to prevent accidental mass deletions. You must call this method explicitly if you want to delete all rows.

Returns:

Self for method chaining

Return type:

DeleteQuery

Example

>>> Q.delete_from("temp_table").allow_all_rows().build()

build()

Build the query and return (sql, params).

Return type:

tuple[str, tuple[Any, …]]

class sqlo.expressions.Expression

Bases: object

Base class for SQL expressions.

alias: str | None = None

as_(alias)

Set an alias for the expression.

Parameters:

alias (str)

Return type:

Expression

class sqlo.expressions.Raw(sql, params=None)

Bases: Expression

Raw SQL fragment.

WARNING: Raw SQL bypasses parameter binding and can be vulnerable to SQL injection. Only use Raw() with trusted input or when you need to reference columns/functions.

For dynamic user input, always use parameterized queries instead.

Parameters:

• sql (str)

• params (list[Any] | tuple[Any, ...] | None)

class sqlo.expressions.Func(name, *args)

Bases: Expression

Represents a SQL function call.

This class allows you to wrap any SQL function (e.g., COUNT, MAX, AVG, custom functions) and use it within your queries. It supports arguments and aliasing.

Parameters:

• name (str) – The name of the SQL function (e.g., “COUNT”).

• *args (Any) – Arguments to pass to the function.

Example

>>> f = Func("COUNT", "*")
>>> f.as_("total")

over(window=None)

Create a window function with OVER clause.

Parameters:

window (Any | None) – Optional Window object for PARTITION BY / ORDER BY

Returns:

WindowFunc object

Return type:

Any

Example

>>> func.row_number().over(Window.partition_by("dept"))

class sqlo.expressions.FunctionFactory

Bases: object

Factory for creating SQL function expressions.

static count(expression='*')

Parameters:

expression (str)

Return type:

Func

static sum(expression)

Parameters:

expression (str)

Return type:

Func

static avg(expression)

Parameters:

expression (str)

Return type:

Func

static min(expression)

Parameters:

expression (str)

Return type:

Func

static max(expression)

Parameters:

expression (str)

Return type:

Func

class sqlo.expressions.Condition(column=None, value=None, operator='=')

Bases: Expression

Represents a SQL condition for use in WHERE or HAVING clauses.

This class allows you to build simple or complex conditions with support for compact operator syntax (e.g., “age>”) and standard syntax (e.g., “age >”). Conditions can be combined using bitwise operators (& and |) or static methods.

Parameters:

• column (str, optional) – Column name with optional operator (e.g., “age>=” or “age >=”).

• value (Any, optional) – The value to compare against. Can be a list for IN operator.

• operator (str, optional) – The comparison operator if not included in column. Defaults to “=”.

Examples

>>> # Simple condition
>>> c = Condition("age>=", 18)
>>> sql, params = c.build()
>>>
>>> # IN operator
>>> c = Condition("status", ["pending", "done"], operator="IN")
>>>
>>> # IS NULL
>>> c = Condition("deleted_at", operator="IS NULL")
>>>
>>> # Static factory methods
>>> c = Condition.null("deleted_at")
>>> c = Condition.in_("status", ["pending", "done"])
>>> c = Condition.exists(subquery)
>>>
>>> # Combining conditions
>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> combined = c1 & c2  # AND
>>> combined = c1 \| c2  # OR

build(dialect=None)

Build the condition and return (sql, params) tuple.

Parameters:

dialect – Optional dialect to use for quoting. Defaults to MySQLDialect.

Returns:

Tuple of (sql_string, params_tuple)

Return type:

tuple[str, tuple[Any, …]]

Example

>>> c = Condition("age>=", 18)
>>> sql, params = c.build()
>>> assert sql == "`age` >= ?"
>>> assert params == (18,)

static null(column)

Create an IS NULL condition.

Parameters:

column (str) – Column name to check for NULL

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.null("deleted_at")
>>> sql, params = c.build()
>>> # SQL: `deleted_at` IS NULL

static not_null(column)

Create an IS NOT NULL condition.

Parameters:

column (str) – Column name to check for NOT NULL

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.not_null("email")
>>> sql, params = c.build()
>>> # SQL: `email` IS NOT NULL

static in_(column, values)

Create an IN condition.

Parameters:

• column (str) – Column name

• values (list[Any] | tuple[Any, ...] | SelectQuery) – List/tuple of values or a SelectQuery subquery

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.in_("status", ["pending", "done"])
>>> sql, params = c.build()
>>> # SQL: `status` IN (?, ?)

static not_in(column, values)

Create a NOT IN condition.

Parameters:

• column (str) – Column name

• values (list[Any] | tuple[Any, ...] | SelectQuery) – List/tuple of values or a SelectQuery subquery

Returns:

Condition object

Return type:

Condition

Example

>>> c = Condition.not_in("status", ["canceled", "failed"])
>>> sql, params = c.build()
>>> # SQL: `status` NOT IN (?, ?)

static exists(subquery)

Create an EXISTS condition with a subquery.

Parameters:

subquery (SelectQuery) – SelectQuery object

Returns:

Condition object

Return type:

Condition

Example

>>> subquery = Q.select("1").from_("orders").where(Condition("user_id", 123))
>>> c = Condition.exists(subquery)
>>> sql, params = c.build()
>>> # SQL: EXISTS (SELECT 1 FROM `orders` WHERE `user_id` = ?)

static not_exists(subquery)

Create a NOT EXISTS condition with a subquery.

Parameters:

subquery (SelectQuery) – SelectQuery object

Returns:

Condition object

Return type:

Condition

Example

>>> subquery = Q.select("1").from_("orders").where(Condition("user_id", 123))
>>> c = Condition.not_exists(subquery)
>>> sql, params = c.build()
>>> # SQL: NOT EXISTS (SELECT 1 FROM `orders` WHERE `user_id` = ?)

static and_(*conditions)

Combine multiple conditions with AND logic.

Parameters:

*conditions (Condition) – Variable number of Condition objects

Returns:

Combined Condition object

Return type:

Condition

Example

>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> c3 = Condition("verified", True)
>>> combined = Condition.and_(c1, c2, c3)
>>> # SQL: (`age` >= ? AND `country` = ? AND `verified` = ?)

static or_(*conditions)

Combine multiple conditions with OR logic.

Parameters:

*conditions (Condition) – Variable number of Condition objects

Returns:

ComplexCondition object

Return type:

Condition | ComplexCondition

Example

>>> c1 = Condition("status", "pending")
>>> c2 = Condition("status", "processing")
>>> combined = Condition.or_(c1, c2)
>>> # SQL: (`status` = ? OR `status` = ?)

class sqlo.expressions.ComplexCondition(operator, left, right)

Bases: Expression

Represents a complex SQL condition combining multiple conditions with AND/OR logic.

This class is typically created automatically when using bitwise operators (& or |) on Condition objects, or when using Condition.and_() / Condition.or_() static methods. It properly handles operator precedence and nested conditions.

Parameters:

• operator (str) – The logical operator (“AND” or “OR”).

• left (Union[Condition, ComplexCondition]) – The left-hand condition.

• right (Union[Condition, ComplexCondition]) – The right-hand condition.

Examples

>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> c3 = Condition("verified", True)
>>>
>>> # Creates ComplexCondition automatically
>>> complex = (c1 & c2) \| c3
>>> # Represents: (age >= 18 AND country = 'US') OR verified = True

build(dialect=None)

Build the complex condition and return (sql, params) tuple.

Parameters:

dialect – Optional dialect to use for quoting. Defaults to MySQLDialect.

Returns:

Tuple of (sql_string, params_tuple)

Return type:

tuple[str, tuple[Any, …]]

Example

>>> c1 = Condition("age>=", 18)
>>> c2 = Condition("country", "US")
>>> complex = c1 \| c2
>>> sql, params = complex.build()
>>> # SQL: (`age` >= ? OR `country` = ?)

class sqlo.expressions.JSONPath(column, path)

Bases: Expression

Represents a JSON path extraction.

Parameters:

• column (str)

• path (str)

class sqlo.expressions.JSON(column)

Bases: Expression

Represents a JSON column.

Example

>>> JSON("data").extract("name")

Parameters:

column (str)

extract(path)

Extract a value from the JSON column.

Parameters:

path (str)

Return type:

JSONPath