API

A developer building SQL statements only needs to create a QueryFactory with the desired engine. Assemble the query with the available functions and compile it to get the SQL statement as a string and the parameters as a tuple.

Engines

An engine is responsible for creating query instances. These methods are called by the QueryFactory. It also provides methods to handle common query builder actions (like escaping an identifier). When a database needs an exception, a specific Engine class can derive from BasicEngine and implement the different behaviour.

class sql_smith.engine.BasicEngine

Basic engine

A default implementation of an engine.

escape_identifier(identifier: str) str

Escapes an identifier.

The default implementation returns the identifier as is.

escape_like(parameter: str) str

Escapes the LIKE argument

A backslash is used to escape wildcards. Standard wildcards are underscore and percent sign.

export_parameter(param) str

Export a parameter.

A boolean will be exported as true or false. A None value will be exported as NULL.

extract_params() Callable[[StatementInterface], tuple]

Create a lambda to extract parameters.

extract_sql() Callable[[StatementInterface], str]

Create a lambda to extract SQL.

flatten_params(*args: StatementInterface) tuple

Create a tuple with all parameters found.

flatten_sql(separator: str, *args: StatementInterface) str

Transform StatementInterface arguments to a string.

make_delete() DeleteQuery

Creates a DeleteQuery.

make_insert() InsertQuery

Creates an InsertQuery.

make_select() SelectQuery

Creates a SelectQuery.

make_update() UpdateQuery

Creates an UpdateQuery.

class sql_smith.engine.CommonEngine

An engine that supports common SQL standard.

escape_identifier(identifier: str) str

Escapes identifiers by putting quotes around it.

class sql_smith.engine.MysqlEngine

A custom engine for MySQL SQL dialect.

escape_identifier(identifier: str) str

Escapes the identifier by surrounding it with backticks.

make_insert() InsertQuery

Creates a custom MySql INSERT query.

The MySQL insert query supports IGNORE.

make_select() SelectQuery

Creates a custom MySql SELECT query.

The MySQL select query supports SQL_CALC_FOUND_ROWS.

class sql_smith.engine.PostgresEngine

A custom engine for Postgres SQL dialect.

make_insert() InsertQuery

Creates a custom Postgres INSERT query.

The Postgres insert query supports RETURNING.

make_update() UpdateQuery

Creates a custom Postgres INSERT query.

The Postgres update query supports RETURNING.

class sql_smith.engine.SqlServerEngine

A custom engine for SQL Server SQL dialect.

escape_identifier(identifier: str) str

Escapes identifiers with brackets.

escape_like(parameter: str) str

Escape like argument.

Also escapes character ranges.

make_delete() DeleteQuery

Creates a custom SQL Server DELETE query.

SQL Server handles LIMIT differently.

make_select() SelectQuery

Creates a custom SQL Server SELECT query.

SQL Server handles OFFSET and LIMIT differently.

class sql_smith.engine.SqliteEngine

A custom engine for Sqlite SQL dialect.

export_parameter(param) str

Export a parameter.

SQLite doesn’t support boolean storage class. True is translated to 1, False to 0.

QueryFactory

The query factory is responsible for creating queries.

class sql_smith.QueryFactory(engine: EngineInterface = None)

Factory class for creating queries

Functions

sql_smith.functions.alias(field_name, field_alias: str) ExpressionInterface

Create an alias for a column or a function.

>>> alias('users.id', 'uid')  # "users"."id" AS "uid"
sql_smith.functions.criteria(pattern: str, *args) CriteriaInterface

Create a criteria.

>>> c = criteria(
>>>     "{} = {}",
>>>     func(
>>>         'YEAR',
>>>         identify('start_date')
>>>     ),
>>>     literal(2021)
>>> )  # YEAR("start_date") = 2021
sql_smith.functions.express(pattern: str, *args)

Create an expression.

>>> express('{} + 1', identify('visit'))  # "visit" + 1
sql_smith.functions.field(name)

Starts a criteria for a column. Use it to create a condition.

>>> field('users.id').eq(100)  # "users".id = ?
sql_smith.functions.func(function: str, *args) ExpressionInterface

Create a function.

>>> func('COUNT', 'user.id')  # COUNT("users"."id")
sql_smith.functions.group(c: CriteriaInterface) CriteriaInterface

Create a group of criteria.

>>> group(
>>>     field('username').eq('tom')
>>>     .or_(field('first_name').eq('Tom'))
>>> ).and_(
>>>     field('is_active').eq(1)
>>> )
>>> # ("username" = ? OR "first_name" = ?) AND "is_active" = ?
sql_smith.functions.identify(name) StatementInterface

Identify a name.

>>> identify('users.id')  # "users"."id"
sql_smith.functions.identify_all(*names) tuple

Identify all names.

>>> identify_all('id', 'username')  # ("id", "username")
sql_smith.functions.listing(values: tuple | list, separator: str = ', ') Listing

Create a listing.

>>> listing((1, 1, 2, 3, 5))  # ?, ?, ?, ?, ?
>>> listing(identify_all('id', 'username', 'email'))  # "id", "username", "email"
sql_smith.functions.literal(value) StatementInterface

Create a literal.

sql_smith.functions.on(left: str, right: str)

Create an on clause.

sql_smith.functions.order(column, direction: str | None = None) StatementInterface

Create an order clause.

sql_smith.functions.param(value) StatementInterface

Create a parameter.

>>> func('POINT', param(1), param(2))  # POINT(? , ?)
sql_smith.functions.search(name)

Start a LIKE clause.

>>> search('username').contains('admin')  # "username" LIKE '%admin%'

Queries

class sql_smith.query.AbstractQuery(engine: EngineInterface)

Base class for queries.

compile() Query

Compiles the query and returns the Query object

class sql_smith.query.DeleteQuery(engine: EngineInterface)

Implements the DELETE query.

class sql_smith.query.InsertQuery(engine: EngineInterface)

Implements the INSERT query.

columns(*columns) InsertQuery

Sets the columns to insert.

into(table: str) InsertQuery

Sets the table.

map(column_values: Dict[str, Any])

Maps a dictionary to columns and values.

values(*values) InsertQuery

Appends values.

class sql_smith.query.Query(sql: str, params: tuple)

The result of a compile.

property params: tuple

Returns all parameters of the SQL statement.

property sql: str

Returns the compiled SQL statement as a string.

class sql_smith.query.SelectQuery(engine: EngineInterface)

Implements a SELECT query.

add_columns(*columns)

Add columns to the selection.

columns(*columns) SelectQuery

Set the columns to select.

distinct(state: bool = True) SelectQuery

Add or remove DISTINCT.

full_join(table: str, criteria: CriteriaInterface) SelectQuery

Add a FULL join.

group_by(*columns)

Add a GROUP BY clause.

having(criteria: CriteriaInterface)

Add an HAVING clause.

inner_join(table: str, criteria: CriteriaInterface) SelectQuery

Add an INNER join.

join(table: str, criteria: CriteriaInterface, join_type: str = '') SelectQuery

Add a join.

left_join(table: str, criteria: CriteriaInterface) SelectQuery

Add a LEFT join.

right_join(table: str, criteria: CriteriaInterface) SelectQuery

Add a RIGHT join.

with_(name: str | None = None, query: ExpressionInterface | None = None, recursive: bool = False) SelectQuery

Add a query as CTE.

When no name and query is passed, all CTE’s for this query will be removed. When no query is passed, the CTE with the given name will be removed.

class sql_smith.query.UnionQuery(engine: EngineInterface, left: StatementInterface, right: StatementInterface)

Implements a union query.

all(state: bool = True) UnionQuery

Sets ALL.

class sql_smith.query.UpdateQuery(engine: EngineInterface)

Implements an UPDATE query.

set(value_dict: dict[str, Any])

Sets the column and values with a dictionary.

table(table: str | StatementInterface) UpdateQuery

Sets the table.