Getting Started

Purpose

sqlexpr is a query-building library for people who know SQL. It does not validate your SQL — it will happily let you construct illegal or nonsensical queries. Its purpose is to let you build queries that vary based on application logic without resorting to ugly and difficult-to-follow string manipulation.

Rather than concatenating SQL strings with if statements, you construct a query object and call methods to add clauses. This makes it straightforward to build queries where the columns, joins, filters, and grouping vary based on runtime conditions:

qb = SQL.select().from_table('orders').column('id').column('total')

if include_customer:
    qb.join_table('customers', 'c', 'c.id = orders.customer_id')
    qb.column('c.name', 'customer_name')

if status_filter:
    qb.where('status', '=', status_filter)

See SqlSelect for the full SELECT API.

Real-World Example

A more compelling example: a simplified Cypher-to-SQL translator that parses graph query syntax and dynamically builds JOIN clauses. The translator calls add_query_joins(qb) on a base query, adding joins and conditions based on the parsed graph pattern. The same base query produces different SQL depending on the input:

expr = parse_cypher("match (:Person)")
qb = expr.add_query_joins(SQL.select().from_table('ev').column('*'))

SELECT *
FROM
    ev
    JOIN n _n0 ON _n0.event_id = ev.id
                  AND _n0.entity ->> 'label' = %s

-- args: ['Person']

A relationship pattern adds multiple joins with direction constraints:

expr = parse_cypher("match (a:Person)-[r:KNOWS]->(b:Person)")
qb = expr.add_query_joins(SQL.select().from_table('ev').column('*'))

SELECT *
FROM
    ev
    JOIN n a ON a.event_id = ev.id
                AND a.entity ->> 'label' = %s
    JOIN n b ON b.event_id = ev.id
                AND b.entity ->> 'label' = %s
    JOIN r r ON r.event_id = ev.id
                AND r.start_source_id = a.source_id
                AND r.end_source_id = b.source_id
                AND r.entity ->> 'type' = %s

-- args: ['Person', 'Person', 'KNOWS']

Boolean expressions compose naturally — an OR condition on a node property:

expr = parse_cypher("match (p:Person) where p.age < 18 or p.age > 65")
qb = expr.add_query_joins(SQL.select().from_table('ev').column('*'))

SELECT *
FROM
    ev
    JOIN n p ON p.event_id = ev.id
                AND p.entity ->> 'label' = %s
                AND ((p.entity -> 'properties' ->> 'age')::int < %s
                     OR (p.entity -> 'properties' ->> 'age')::int > %s)

-- args: ['Person', 18, 65]

Longer paths produce more joins, all correctly cross-referenced:

expr = parse_cypher("match (a:Person)-[r1:KNOWS]->(b:Person)-[r2:WORKS_AT]->(c:Company)")
qb = expr.add_query_joins(SQL.select().from_table('ev').column('*'))

SELECT *
FROM
    ev
    JOIN n a ON a.event_id = ev.id
                AND a.entity ->> 'label' = %s
    JOIN n b ON b.event_id = ev.id
                AND b.entity ->> 'label' = %s
    JOIN n c ON c.event_id = ev.id
                AND c.entity ->> 'label' = %s
    JOIN r r1 ON r1.event_id = ev.id
                 AND r1.start_source_id = a.source_id
                 AND r1.end_source_id = b.source_id
                 AND r1.entity ->> 'type' = %s
    JOIN r r2 ON r2.event_id = ev.id
                 AND r2.start_source_id = b.source_id
                 AND r2.end_source_id = c.source_id
                 AND r2.entity ->> 'type' = %s

-- args: ['Person', 'Person', 'Company', 'KNOWS', 'WORKS_AT']

The translator code never constructs SQL strings directly — it calls .join_table(), .add() on SQL.and_expr(), and SQL.value() for parameters. The formatter handles indentation, alignment, and parameterization automatically.

Design

All interaction with sqlexpr goes through a dialect class — PG, MYSQL, SQLITE, ORACLE, or MSSQL. Each dialect class provides attributes that reference the correct builder classes for that database. Calling an attribute (e.g. SQL.select()) instantiates a builder whose methods construct the query.

The API uses method chaining — most methods return self so calls can be composed fluently. Methods can be called in any order; the formatter handles emitting clauses in the correct SQL sequence regardless of the order they were added.

The SQL output follows the formatting style produced by pganalyze/format where appropriate, with some additions for clarity such as wrapping long SELECT column lists and breaking boolean expressions across lines.

Installation

Add the sqlexpr package to your project. It requires Python 3.8+.

Basic Usage

All query builders are accessed through a dialect class (PG, MYSQL, SQLITE, ORACLE, or MSSQL). We recommend aliasing your dialect as SQL so that application code is dialect-agnostic:

from sqlexpr import PG as SQL      # PostgreSQL
from sqlexpr import MYSQL as SQL   # MySQL
from sqlexpr import SQLITE as SQL  # SQLite

Each dialect class is a namespace whose attributes are the builder classes for that database — so SQL.select() instantiates the SELECT builder, SQL.insert() the INSERT builder, and so on. See Dialect Classes for the complete list of attributes on each dialect class, with links to the method API for each builder.

The examples throughout this documentation use SQL, which refers to whichever dialect class you imported above.

Building a query returns an object that you pass to SQL.format() to get the final SQL string and arguments:

qb = (SQL.select()
      .from_table('users')
      .column('name')
      .column('email')
      .where('active', '=', True))

fmt = SQL.format(qb)
sql = fmt.get_sql()    # The SQL string with %s placeholders
args = fmt.get_args()  # The parameter values

The formatter produces readable, indented SQL:

SELECT name, email
FROM users
WHERE active = %s

Comments

Any statement can have a SQL comment attached via .comment(). The comment is emitted as -- prefixed lines before the statement:

qb = (SQL.select()
      .from_table('users')
      .column('*')
      .comment('Load active users for dashboard'))

-- Load active users for dashboard
SELECT *
FROM users

This works on all statement types — SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE INDEX, and DROP TABLE.

Comments are particularly useful when composing complex queries with nested subqueries built up through conditional logic. When a query is assembled across multiple functions or branches, the generated SQL can be difficult to trace back to the code that produced it. Adding a comment to each subquery or conditional branch makes the final SQL self-documenting — you can see in the database logs or query plans exactly which code path produced each part of the statement.

Executing Queries

The get_sqlargs() method returns a tuple of (sql, args) that can be unpacked directly into a DB-API cursor’s execute() method:

cur.execute(*SQL.format(qb).get_sqlargs())

Parameter Styles

Each dialect class uses the correct placeholder style for its database. Alternative styles are available where the driver supports them. The format method you call depends on which dialect you imported as SQL:

# PostgreSQL (from sqlexpr import PG as SQL)
SQL.format(qb)              # %s placeholders, list args
SQL.format_pyformat(qb)     # %(name)s placeholders, dict args

# MySQL (from sqlexpr import MYSQL as SQL)
SQL.format(qb)              # %s placeholders, list args
SQL.format_pyformat(qb)     # %(name)s placeholders, dict args

# SQLite (from sqlexpr import SQLITE as SQL)
SQL.format(qb)              # ? placeholders, list args

# Oracle (from sqlexpr import ORACLE as SQL)
SQL.format(qb)              # :name placeholders, dict args
SQL.format_numeric(qb)      # :1, :2 placeholders, list args

# MSSQL (from sqlexpr import MSSQL as SQL)
SQL.format(qb)              # %s placeholders, list args

Named styles (named and pyformat) require named_value(name, value) instead of raw values. See Formatting for full details and examples.