API Reference¶

QueryBuilder¶

Bases: object

SQL query builder using constructor-based API.

Creates dynamic SQL queries with automatic table aliasing and relationship management. Eliminates complex if/else logic for dynamic WHERE clauses and JOINs. Perfect for APIs, admin interfaces, reporting systems, and any application that needs dynamic SQL generation.

Features:

Constructor-based API (no method chaining required)
Automatic table aliasing with conflict resolution
Django-style WHERE conditions with logical operators
Flexible JOIN system with via chains
Parameterized queries for SQL injection protection
Hybrid input support (strings or objects)

Examples

Basic query generation:

>>> from sql_generator import QueryBuilder, Table
>>> qb = QueryBuilder([Table('users')], ['users.name', 'users.email'])
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, use.email
FROM users use
>>> print(params)
[]

Query with WHERE conditions and parameters:

>>> qb = QueryBuilder(
...     [Table('users')],
...     ['users.name'],
...     where={'users.active__eq': True, 'users.age__gt': 18}
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name
FROM users use
WHERE use.active = %s AND use.age > %s
>>> print(params)
[True, 18]

Query with JOINs:

>>> from sql_generator import TableJoinAttribute
>>> users = Table('users', joins={'orders': TableJoinAttribute('id', 'user_id')})
>>> orders = Table('orders')
>>> qb = QueryBuilder([users, orders], ['users.name', 'orders.total'], joins=['orders'])
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, ord.total
FROM users use
INNER JOIN orders ord ON use.id = ord.user_id

Complex query with aggregation and all clauses:

>>> from sql_generator import SelectColumn, AggFunction
>>> qb = QueryBuilder(
...     [users, orders],
...     ['users.name', SelectColumn('COUNT(*)', alias='order_count')],
...     joins=['orders'],
...     where={'users.active__eq': True, 'orders.total__gt': 100},
...     group_by=['users.id', 'users.name'],
...     order_by=['order_count DESC'],
...     limit=50
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, COUNT(*) AS order_count
FROM users use
INNER JOIN orders ord ON use.id = ord.user_id
WHERE use.active = %s AND ord.total > %s
GROUP BY use.id, use.name
ORDER BY order_count DESC
LIMIT 50
>>> print(params)
[True, 100]

Logical operators in WHERE conditions:

>>> qb = QueryBuilder(
...     [Table('users')],
...     ['users.name'],
...     where={'users.active__eq': True, 'or__users.role__eq': 'admin'}
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name
FROM users use
WHERE use.active = %s OR use.role = %s
>>> print(params)
[True, 'admin']

Dynamic query generation for APIs:

>>> def get_users(filters=None, include_orders=False, sort_by=None):
...     tables = [Table('users')]
...     select_cols = ['users.name', 'users.email']
...     joins = []
...
...     if include_orders:
...         tables.append(Table('orders'))
...         select_cols.append('orders.total')
...         joins.append('orders')
...
...     return QueryBuilder(
...         tables=tables,
...         select=select_cols,
...         joins=joins or None,
...         where=filters,
...         order_by=[sort_by] if sort_by else None
...     ).build()
>>>
>>> sql, params = get_users(
...     filters={'users.active__eq': True},
...     include_orders=True,
...     sort_by='users.name ASC'
... )

Note

Table aliases are auto-generated (first 3+ characters) with conflict resolution
All string inputs are normalized to objects during initialization
First table in tables list becomes the FROM clause
JOIN deduplication removes exact duplicate JOIN strings while preserving order

build() → tuple[str, list][source]¶

Generate SQL query string and parameters from QueryBuilder configuration.

Constructs a complete SQL SELECT statement using all components provided during initialization. Automatically handles table aliasing, JOIN deduplication, and parameterized queries for safe execution.

Returns:

A tuple containing:

str: Complete SQL query string with newline-separated clauses
list: List of parameter values for parameterized query execution

Return type:

tuple[str, list]

Examples

Basic query generation:

>>> from sql_generator import QueryBuilder, Table
>>> qb = QueryBuilder([Table('users')], ['users.name', 'users.email'])
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, use.email
FROM users use
>>> print(params)
[]

Query with parameters:

>>> qb = QueryBuilder(
...     [Table('users')],
...     ['users.name'],
...     where={'users.active__eq': True, 'users.age__gt': 18}
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name
FROM users use
WHERE use.active = %s AND use.age > %s
>>> print(params)
[True, 18]

Complex query with all clauses:

>>> from sql_generator import TableJoinAttribute, SelectColumn
>>> users = Table('users', joins={'orders': TableJoinAttribute('id', 'user_id')})
>>> orders = Table('orders')
>>> qb = QueryBuilder(
...     [users, orders],
...     ['users.name', SelectColumn('COUNT(*)', alias='order_count')],
...     joins=['orders'],
...     where={'users.active__eq': True, 'orders.total__gt': 100},
...     group_by=['users.id', 'users.name'],
...     order_by=['order_count DESC'],
...     limit=50
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, COUNT(*) AS order_count
FROM users use
INNER JOIN orders ord ON use.id = ord.user_id
WHERE use.active = %s AND ord.total > %s
GROUP BY use.id, use.name
ORDER BY order_count DESC
LIMIT 50
>>> print(params)
[True, 100]

Using the generated SQL with database connections:

>>> import psycopg
>>> sql, params = qb.build()
>>> cursor.execute(sql, params)
>>> results = cursor.fetchall()

Note

Parameters use %s placeholders for PostgreSQL/MySQL compatibility
For SQLite, replace %s with ? in the returned SQL string
JOIN clauses are deduplicated while preserving order
Table aliases are automatically applied throughout the query
All clauses follow standard SQL order: SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY, LIMIT

Core Classes¶

Core data classes and enums for SQL query construction.

This module provides the foundational building blocks for constructing SQL queries in a type-safe, object-oriented manner. It includes data classes for representing tables, columns, joins, and WHERE conditions, along with enums for operators and aggregate functions.

Key Components:

Table: Represents database tables with relationship definitions

SelectColumn: Handles SELECT clause columns with aggregation and aliasing

Join/ViaStep: Manages table joins including complex via chains

WhereCondition: Represents WHERE clause conditions with logical operators

GroupBy/OrderBy: Column references for GROUP BY and ORDER BY clauses

Enums:

JoinType: SQL join types (INNER, LEFT, RIGHT, FULL OUTER)

AggFunction: Aggregate functions (COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT)

Operator: Comparison operators for WHERE clauses (=, <, >, LIKE, IN, etc.)

Example

>>> from sql_generator.QueryObjects import Table, TableJoinAttribute, SelectColumn, AggFunction
>>> # Define table relationships
>>> users = Table("users", joins={"orders": TableJoinAttribute("id", "user_id")})

>>> # Create SELECT columns with aggregation
>>> name_col = SelectColumn("name", table="users")
>>> count_col = SelectColumn("id", table="orders", agg_function=AggFunction.COUNT, alias="order_count")

Note

These classes are typically used through the QueryBuilder interface rather than directly. They provide the internal representation for SQL query components.

class sql_generator.QueryObjects.AggFunction(*values)[source]¶

Bases: Enum

Common SQL aggregate functions for use in SELECT clauses.

Supported functions:

COUNT: Count rows or non-null values

SUM: Sum numeric values

AVG: Calculate average of numeric values

MIN: Find minimum value

MAX: Find maximum value

COUNT_DISTINCT: Count unique non-null values

Examples

Basic aggregate functions:

>>> from sql_generator import SelectColumn, AggFunction
>>> col = SelectColumn("id", table="orders", agg_function=AggFunction.COUNT)
>>> # Generates: COUNT(ord.id)

>>> col = SelectColumn("total", table="orders", agg_function=AggFunction.SUM, alias="revenue")
>>> # Generates: SUM(ord.total) AS revenue

>>> col = SelectColumn("price", table="products", agg_function=AggFunction.AVG, alias="avg_price")
>>> # Generates: AVG(pro.price) AS avg_price

>>> col = SelectColumn("category", table="products", agg_function=AggFunction.COUNT_DISTINCT)
>>> # Generates: COUNT(DISTINCT pro.category)

Min/Max functions:

>>> col = SelectColumn("created_at", table="orders", agg_function=AggFunction.MIN, alias="first_order")
>>> # Generates: MIN(ord.created_at) AS first_order

>>> col = SelectColumn("total", table="orders", agg_function=AggFunction.MAX, alias="largest_order")
>>> # Generates: MAX(ord.total) AS largest_order

Complete query:

>>> from sql_generator import QueryBuilder, Table, TableJoinAttribute
>>> users = Table('users', joins={'orders': TableJoinAttribute('id', 'user_id')})
>>> orders = Table('orders')
>>> qb = QueryBuilder(
...     [users, orders],
...     [
...         'users.name',
...         SelectColumn('id', table='orders', agg_function=AggFunction.COUNT, alias='order_count'),
...         SelectColumn('total', table='orders', agg_function=AggFunction.SUM, alias='total_spent')
...     ],
...     joins=['orders'],
...     group_by=['users.id', 'users.name']
... )
>>> sql, params = qb.build()
>>> print(sql)
SELECT use.name, COUNT(ord.id) AS order_count, SUM(ord.total) AS total_spent
FROM users use
INNER JOIN orders ord ON use.id = ord.user_id
GROUP BY use.id, use.name

class sql_generator.QueryObjects.ColumnReference(column: str, table: str | None = None)[source]¶

Bases: object

Base class for column references with table alias resolution.

Provides common functionality for referencing database columns in SQL clauses, with automatic table alias resolution and SELECT alias recognition.

column¶

Column name to reference

Type:: str

table¶

Table name for column reference (optional)

Type:: str | None

Note

This is a base class - use GroupBy or OrderBy subclasses instead.

to_sql(table_aliases: dict[str, str], select_aliases: set[str]) → str[source]¶

Convert to SQL with table alias resolution

Parameters:

table_aliases – Mapping of table names to their aliases
select_aliases – Set of column aliases from SELECT clause

Returns:

SQL column reference with proper table alias or SELECT alias

class sql_generator.QueryObjects.GroupBy(column: str, table: str | None = None)[source]¶

Bases: ColumnReference

Represents a column in GROUP BY clause.

Inherits from ColumnReference to provide table alias resolution and SELECT alias recognition. Used to group query results by one or more columns.

Examples

>>> # Group by table column
>>> GroupBy("category", table="products")  # → pro.category

>>> # Group by column without table (assumes single table or unambiguous)
>>> GroupBy("status")  # → status

>>> # Group by SELECT alias (from aggregate or computed column)
>>> GroupBy("total_orders")  # → total_orders (if it's a SELECT alias)

Note

Automatically resolves table aliases and recognizes SELECT column aliases. When a column name matches a SELECT alias, the alias is used directly.

class sql_generator.QueryObjects.Join(join_key: str, via_steps: list[ViaStep] | None = None)[source]¶

Bases: object

Specifies a join operation with optional via chain.

join_key¶

Key from table’s joins dictionary

Type:: str

via_steps¶

Optional via chain with join types for each step. If not provided, uses INNER JOIN for direct joins.

Type:: list[sql_generator.QueryObjects.ViaStep] | None

class sql_generator.QueryObjects.JoinType(*values)[source]¶

Bases: Enum

SQL join types for table relationships.

Defines the different ways tables can be joined in SQL queries, determining which rows are included in the result set based on matching conditions.

Join Types:

INNER: Returns only rows that have matching values in both tables

LEFT: Returns all rows from left table, matching rows from right table (NULL if no match)

RIGHT: Returns all rows from right table, matching rows from left table (NULL if no match)

FULL: Returns all rows from both tables, with NULLs where no match exists

Examples

>>> # Inner join - only users with orders
>>> ViaStep("orders", JoinType.INNER)

>>> # Left join - all users, with order data if available
>>> ViaStep("orders", JoinType.LEFT)

>>> # Right join - all orders, with user data if available
>>> ViaStep("users", JoinType.RIGHT)

>>> # Full outer join - all users and all orders
>>> ViaStep("orders", JoinType.FULL)

class sql_generator.QueryObjects.Operator(*values)[source]¶

Bases: Enum

SQL comparison operators for WHERE clauses.

Provides type-safe access to SQL comparison operators for building WHERE conditions. Supports equality, inequality, range, pattern matching, list membership, and null checks.

Comparison Operators:

EQ: Equal to (=) - exact match

NE: Not equal to (!=) - excludes exact matches

LT: Less than (<) - numeric/date comparison

LE: Less than or equal (<=) - numeric/date comparison

GT: Greater than (>) - numeric/date comparison

GE: Greater than or equal (>=) - numeric/date comparison

Pattern Matching:

LIKE: Case-sensitive pattern matching with wildcards (% and _)

ILIKE: Case-insensitive pattern matching

List Operations:

IN: Value exists in list of options

NOT_IN: Value does not exist in list of options

Range Operations:

BETWEEN: Value falls within inclusive range (requires 2-element list/tuple)

Null Checks:

IS_NULL: Column value is NULL (no value parameter needed)

IS_NOT_NULL: Column value is not NULL (no value parameter needed)

Examples

>>> # Equality and comparison:
>>> WhereCondition("age", Operator.EQ, 25)          # age = 25
>>> WhereCondition("price", Operator.GT, 100.0)     # price > 100.0

>>> # Pattern matching:
>>> WhereCondition("name", Operator.LIKE, "%john%")  # name LIKE '%john%'

>>> # List membership:
>>> WhereCondition("status", Operator.IN, ["active", "pending"])  # status IN ('active', 'pending')

>>> # Range queries:
>>> WhereCondition("age", Operator.BETWEEN, [18, 65])  # age BETWEEN 18 AND 65

>>> # Null checks:
>>> WhereCondition("deleted_at", Operator.IS_NULL)   # deleted_at IS NULL

class sql_generator.QueryObjects.OrderBy(column: str, table: str | None = None, direction: str = 'ASC')[source]¶

Bases: ColumnReference

Represents a column in ORDER BY clause with sort direction.

Inherits from ColumnReference to provide table alias resolution and SELECT alias recognition. Adds sort direction (ASC/DESC) for ordering query results.

column¶

Column name to sort by (inherited from ColumnReference)

Type:: str

table¶

Table name for column reference (inherited, optional)

Type:: str | None

direction¶

Sort direction - “ASC” (ascending) or “DESC” (descending)

Type:: str

Examples

>>> # Order by table column, default ascending
>>> OrderBy("name", table="users")  # → u.name ASC

>>> # Order by column with explicit direction
>>> OrderBy("price", table="products", direction="DESC")  # → pro.price DESC

>>> # Order by SELECT alias
>>> OrderBy("total_orders", direction="DESC")  # → total_orders DESC

>>> # Case-insensitive direction (automatically normalized)
>>> OrderBy("created_at", direction="desc")  # → created_at DESC

Note

Direction is automatically normalized to uppercase and validated. Supports both table columns and SELECT aliases for ordering.

to_sql(table_aliases: dict[str, str], select_aliases: set[str]) → str[source]¶

Convert to SQL ORDER BY clause with direction

Parameters:

table_aliases – Mapping of table names to their aliases
select_aliases – Set of column aliases from SELECT clause

Returns:

SQL ORDER BY clause with column reference and direction

class sql_generator.QueryObjects.SelectColumn(column: str, table: str | None = None, alias: str | None = None, agg_function: AggFunction | None = None, distinct: bool = False)[source]¶

Bases: object

Represents a column in a SELECT clause with optional aggregation and aliasing.

column¶

Column name or expression (e.g., ‘name’, ‘*’, ‘NOW()’)

Type:: str

table¶

Table name for column reference (optional)

Type:: str | None

alias¶

Column alias for AS clause (optional)

Type:: str | None

agg_function¶

Aggregate function to apply (optional)

Type:: sql_generator.QueryObjects.AggFunction | None

distinct¶

Whether to apply DISTINCT to the column (optional)

Type:: bool

Examples

>>> # Simple column
>>> SelectColumn("name", table="users")

>>> # Column with alias
>>> SelectColumn("created_at", table="orders", alias="order_date")

>>> # Aggregate function
>>> SelectColumn("id", table="orders", agg_function=AggFunction.COUNT, alias="total_orders")

>>> # Distinct values
>>> SelectColumn("status", table="users", distinct=True)

>>> # Expression without table
>>> SelectColumn("NOW()", alias="current_time")

to_sql(table_aliases: dict[str, str]) → str[source]¶: Convert to complete SQL string with table alias replacement

class sql_generator.QueryObjects.Table(name: str, primary_key: str | None = None, alias: str | None = None, joins: dict[str, TableJoinAttribute] | None = None)[source]¶

Bases: object

Represents a database table and its relationships to other tables.

name¶

Database table name

Type:: str

primary_key¶

Primary key column name (optional, defaults to “id”)

Type:: str | None

alias¶

User-defined alias (optional, auto-generated if not provided)

Type:: str | None

joins¶

Dictionary mapping join keys to Join definitions

Type:: dict[str, sql_generator.QueryObjects.TableJoinAttribute] | None

Examples

>>> Table("users", joins={
>>> "orders": TableJoinAttribute("id", "user_id"),
>>> "profiles": TableJoinAttribute("id", "user_id")
>>> })

>>> # With custom primary key
>>> Table("products", primary_key="product_id", joins={
>>> "categories": TableJoinAttribute("category_id", "id")
>>> })

class sql_generator.QueryObjects.TableJoinAttribute(source_column: str, target_column: str, table_name: str | None = None)[source]¶

Bases: object

Defines how to join from a source table to a target table.

source_column¶

Column name on the source table (e.g., ‘id’)

Type:: str

target_column¶

Column name on the target table (e.g., ‘user_id’)

Type:: str

table_name¶

Override target table name if different from join key

Type:: str | None

Examples

>>> # Direct join: users.id = orders.user_id
>>> TableJoinAttribute("id", "user_id")

>>> # Join with table name override
>>> TableJoinAttribute("id", "user_id", table_name="addresses")

get_table_name(join_key: str) → str[source]¶

Return table_name if specified, otherwise defaults to join_key.

Parameters:: join_key (str) – The join key name to use as fallback table name
Returns:: The table_name attribute if set, otherwise the join_key parameter
Return type:: str

class sql_generator.QueryObjects.ViaStep(table_name: str, join_type: JoinType = JoinType.INNER)[source]¶

Bases: object

Represents a single step in a multi-table join chain (via path).

Used to define intermediate tables and join types when joining tables that don’t have direct relationships. Each ViaStep specifies one hop in the join chain, allowing complex multi-table joins with different join types at each step.

table_name¶

Name of the intermediate table to join through

Type:: str

join_type¶

Type of SQL join to use for this step (defaults to INNER)

Type:: sql_generator.QueryObjects.JoinType

Examples

>>> # Simple via step with default INNER join:
>>> ViaStep("orders")

>>> # Via step with explicit LEFT join:
>>> ViaStep("order_items", JoinType.LEFT)

>>> # Building a complete via chain:
>>> Join("products", via_steps=[
>>> ViaStep("orders", JoinType.INNER),      # users INNER JOIN orders
>>> ViaStep("order_items", JoinType.LEFT),  # orders LEFT JOIN order_items
>>> ViaStep("products", JoinType.INNER)     # order_items INNER JOIN products
>>> ])

Note

ViaStep objects are typically used within Join objects to define multi-hop join paths. The join chain starts from the primary table and follows each ViaStep in sequence to reach the target table.

class sql_generator.QueryObjects.WhereCondition(column: str, operator: Operator, value: Any | None = None, table: str | None = None, logical_operator: str = 'AND')[source]¶

Bases: object

Represents a WHERE clause condition with logical operator support.

column¶

Column name to filter on

Type:: str

operator¶

Comparison operator (=, <, >, LIKE, IN, etc.)

Type:: sql_generator.QueryObjects.Operator

value¶

Value to compare against (None for IS NULL/IS NOT NULL)

Type:: Any | None

table¶

Table name for column reference (optional)

Type:: str | None

logical_operator¶

How to join with previous condition (“AND” or “OR”, defaults to “AND”)

Type:: str

Examples

Basic equality condition:

>>> WhereCondition("age", Operator.GE, 18, table="users")
>>> # → users.age >= 18

String pattern matching:

>>> WhereCondition("name", Operator.LIKE, "%john%", table="users")
>>> # → users.name LIKE '%john%'

List membership:

>>> WhereCondition("status", Operator.IN, ["active", "pending"])
>>> # → status IN ('active', 'pending')

Null checks:

>>> WhereCondition("deleted_at", Operator.IS_NULL)
>>> # → deleted_at IS NULL

Range conditions:

>>> WhereCondition("price", Operator.BETWEEN, [10.0, 100.0], table="products")
>>> # → products.price BETWEEN 10.0 AND 100.0

With logical operators:

>>> WhereCondition("age", Operator.LT, 65, logical_operator="OR")
>>> # → OR age < 65

>>> # From dict format examples:
>>> # {'id__eq': 1} → WhereCondition("id", Operator.EQ, 1, logical_operator="AND")
>>> # {'or__age__lt': 35} → WhereCondition("age", Operator.LT, 35, logical_operator="OR")
>>> # {'and__status__in': ["active"]} →
>>> WhereCondition("status", Operator.IN, ["active"], logical_operator="AND")

to_sql(table_aliases: dict[str, str]) → tuple[str, any][source]¶

Convert to SQL WHERE condition with parameterized value.

Parameters:

table_aliases (dict[str, str]) – Mapping of table names to their aliases

Returns:

A tuple containing the SQL condition string with %s placeholders: and the parameter values for safe parameterized queries. Parameters may be None (for IS NULL/IS NOT NULL), a single value, or a list of values.

Return type:

tuple[str, any]