Dialect Classes

Each dialect class (PG, MYSQL, SQLITE, ORACLE, MSSQL) provides access to the builder classes and formatters for that database. The attributes are the builder classes themselves, so calling e.g. SQL.select() instantiates the PostgreSQL-specific SELECT builder (PgSelect).

from sqlexpr import PG as SQL

qb = SQL.select()  # creates a PgSelect instance

All dialect classes share a common set of builder attributes. Where a dialect needs specialised behaviour, it overrides the attribute with a subclass — for example SQL.select is PgSelect (which adds DISTINCT ON, WINDOW, etc.) while MYSQL.select is the base SqlSelect.

Common Attributes

The following are available on all dialect classes.

`select()`	Create a SELECT query. Returns SqlSelect.
`insert(table, alias=None)`	Create an INSERT query. Returns SqlInsert.
`update(table, alias=None)`	Create an UPDATE query. Returns SqlUpdate.
`column_list(*columns)`	Create a column-list SET clause for UPDATE. Returns SqlColumnList.
`delete(table)`	Create a DELETE query. Returns SqlDelete.
`term(expr, op=None, value=None)`	Create a single condition. Returns SqlTerm.
`and_expr(*terms)`	Combine conditions with AND. Returns SqlAnd.
`or_expr(*terms)`	Combine conditions with OR. Returns SqlOr.
`value(value=NOT_SPECIFIED)`	A positional parameter placeholder. Returns SqlValue.
`named_value(name, value=NOT_SPECIFIED)`	A named parameter placeholder. Returns SqlNamedValue.
`cat(*parts)`	Concatenate literal SQL fragments and value objects. Returns SqlCat.
`column(name, alias=None)`	A column reference with an optional alias. Returns SqlColumn.
`cte(alias, query)`	A Common Table Expression for WITH clauses. Returns SqlCte.
`join(kind, table, alias=None, expr=None)`	A JOIN clause. Returns SqlJoin.
`union(kind, select)`	A set operation (UNION, INTERSECT, EXCEPT). Returns SqlUnion.

PG

PostgreSQL dialect class. Overrides and additions:

`PG.select()`	SELECT with DISTINCT ON, INTO, WINDOW, FETCH, FOR locking. Returns PgSelect.
`PG.insert(table, alias=None)`	INSERT with ON CONFLICT and RETURNING. Returns PgInsert.
`PG.update(table, alias=None)`	UPDATE with ONLY, RETURNING, RETURNING WITH. Returns PgUpdate.
`PG.delete(table)`	DELETE with ONLY and RETURNING. Returns PgDelete.
`PG.cte(alias, query)`	CTE with MATERIALIZED support. Returns PgCte.
`PG.window(name)`	A named WINDOW definition. Returns PgWindow.
`PG.for_locking(kind)`	A FOR locking clause (UPDATE, SHARE, etc.). Returns PgForLocking.
`PG.create_table(name)`	CREATE TABLE with PG extensions. Returns SqlCreateTable.
`PG.drop_table(*names)`	DROP TABLE. Returns SqlDropTable.
`PG.create_index(name, table)`	CREATE INDEX with PG extensions. Returns SqlCreateIndex.
`PG.bulk_copy(table, *columns)`	COPY-based bulk insert. Returns SqlBulkCopy.
`PG.format(query)`	Render with `%s` placeholders. Returns SqlFormat.
`PG.format_pyformat(query)`	Render with `%(name)s` placeholders. Returns SqlFormatPyformat.

MYSQL

MySQL dialect class. Overrides and additions:

`MYSQL.delete(table, alias=None)`	DELETE with alias and JOIN support. Returns MysqlDelete.
`MYSQL.insert(table, alias=None)`	INSERT with IGNORE support. Returns MysqlInsert.
`MYSQL.format(query)`	Render with `%s` placeholders. Returns SqlFormat.
`MYSQL.format_pyformat(query)`	Render with `%(name)s` placeholders. Returns SqlFormatPyformat.

SQLITE

SQLite dialect class. Overrides and additions:

`SQLITE.insert(table, alias=None)`	INSERT with OR IGNORE support. Returns SqliteInsert.
`SQLITE.format(query)`	Render with `?` placeholders. Returns SqlFormatQmark.

ORACLE

Oracle dialect class. Formatters:

`ORACLE.format(query)`	Render with `:name` placeholders. Returns SqlFormatNamed.
`ORACLE.format_numeric(query)`	Render with `:1`, `:2` placeholders. Returns SqlFormatNumeric.

MSSQL

Microsoft SQL Server dialect class. Formatters:

MSSQL.format(query)

Render with %s placeholders. Returns SqlFormat.