Supporting Multiple SQL Dialects

sqlexpr supports multiple SQL dialects through dialect classes. Each dialect class provides a complete, self-contained API — users import one class and use it for everything.

Available Dialects

from sqlexpr import PG        # PostgreSQL
from sqlexpr import MYSQL     # MySQL
from sqlexpr import SQLITE    # SQLite
from sqlexpr import ORACLE    # Oracle
from sqlexpr import MSSQL     # Microsoft SQL Server

Each dialect class exposes builder classes as attributes and a format() classmethod for rendering queries. The formatter handles the correct placeholder style and dialect identifier automatically.

See Dialect Classes for the full list of attributes on each dialect class.

Usage

from sqlexpr import MYSQL as SQL

qb = (SQL.insert('contact_claim')
      .ignore()
      .columns_kwargs(contact_id=1, claim_id=2))
fmt = SQL.format(qb)
sql = fmt.get_sql()    # INSERT IGNORE INTO contact_claim ...
args = fmt.get_args()  # [2, 1]

Design Principles

Query construction is largely dialect-independent — the tree of clauses (SELECT, FROM, WHERE, JOIN, etc.) is the same across databases. What varies is rendering (placeholder style) and dialect-specific SQL features.

The architecture separates concerns into layers:

Base classes (sqlexpr/select.py, sqlexpr/insert.py, etc.) — portable SQL that works across all databases.
Dialect subclasses (sqlexpr/postgres/select.py, etc.) — add dialect-specific features by overriding clause methods.
Dialect classes (PG, MYSQL, etc.) — wire together the correct builder classes and formatter for each dialect.

Placeholder Styles

Each dialect uses the appropriate DB-API placeholder style:

Dialect class	Style	Placeholder	Args type
`PG.format`	format	`%s`	list
`PG.format_pyformat`	pyformat	`%(name)s`	dict
`MYSQL.format`	format	`%s`	list
`MYSQL.format_pyformat`	pyformat	`%(name)s`	dict
`SQLITE.format`	qmark	`?`	list
`ORACLE.format`	named	`:name`	dict
`ORACLE.format_numeric`	numeric	`:1`, `:2`	list
`MSSQL.format`	format	`%s`	list

Dialect-Specific Features

Features are placed on the dialect that supports them:

PostgreSQL (PG): DISTINCT ON, WINDOW, FOR UPDATE/SHARE, FETCH, INTO, ON CONFLICT, RETURNING, RETURNING WITH, ONLY, MATERIALIZED CTEs, bulk COPY, CREATE TABLE (partitions, inheritance), CREATE INDEX (advanced options)
MySQL (MYSQL): INSERT IGNORE, multi-table DELETE with alias and JOINs
SQLite (SQLITE): INSERT OR IGNORE

Adding a New Dialect

Create a package under sqlexpr/ (e.g. sqlexpr/newdb/).
Add a DIALECT_NEWDB constant to sqlexpr/utils.py.
Create dialect-specific builder subclasses as needed, overriding the _sql_* clause methods from the base classes.
Create a dialect class that wires together the builders and provides a format() classmethod with the correct formatter and dialect constant.
Add the dialect class to sqlexpr/__init__.py.

Module Structure

sqlexpr/
    __init__.py              # Re-exports all dialect classes
    utils.py                 # Dialect constants, base utilities
    format.py                # SqlFormat and placeholder subclasses
    select.py                # SqlSelect (portable)
    insert.py                # SqlInsert (portable)
    update.py                # SqlUpdate (portable)
    delete.py                # SqlDelete (portable)
    query.py                 # SqlQuery, SqlCte, SqlColumn
    expr.py                  # SqlAnd, SqlOr, SqlTerm
    postgres/
        __init__.py          # PG dialect class
        select.py            # PgSelect, PgWindow, PgForLocking
        insert.py            # PgInsert (ON CONFLICT, RETURNING)
        update.py            # PgUpdate (ONLY, RETURNING)
        delete.py            # PgDelete (ONLY, RETURNING)
        query.py             # PgCte (MATERIALIZED)
    mysql/
        __init__.py          # MYSQL dialect class
        insert.py            # MysqlInsert (IGNORE)
        delete.py            # MysqlDelete (alias, JOINs)
    sqlite/
        __init__.py          # SQLITE dialect class
        insert.py            # SqliteInsert (OR IGNORE)
    oracle/
        __init__.py          # ORACLE dialect class
    mssql/
        __init__.py          # MSSQL dialect class