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.

API¶

Engines¶

QueryFactory¶

Functions¶

Queries¶

sql-smith

Navigation

Related Topics