Controlling SQL Output

SQL Dialects

Different dialects can be specified at render time, or as the default dialect of your Queries. Currently the only things dialects control are parameter rendering and limits, but I expect to see some scope creep around here… Dialects are instances of csql.dialect.SQLDialect and can be found in csql.dialect. The default dialect is csql.dialect.DefaultDialect, which uses a numeric parameter renderer. You can specify your own prefered dialect per-query:

q = csql.Q(f"select 1 from thinger", dialect=csql.dialect.DuckDB)

If you want to set a default, use functools.partial like so:

import functools

Q = functools.partial(csql.Q, dialect=csql.dialect.DuckDB)
q = Q(f"select 1 from thinger")

Inferred Dialects

If a query q2 references a previous query q1, and q1 has a dialect specified, then q2 will use q1’s dialect by default.

q1 = csql.Q('select 1 from thinger', dialect=csql.dialect.Snowflake)
q2 = csql.Q('select count(*) from {q1})
assert q2.default_dialect == csql.dialect.Snowflake

If you reference multiple queries with conflicting dialects, you’ll get an error. Normally this is because you’ve actually forgotten to specify something somewhere. If you’re doing this on purpose, override by setting dialect= to Q manually.

DIY Dialects

You can construct your own dialects:

import csql.dialect

MyDialect = csql.dialect.SQLDialect(paramstyle=csql.dialect.ParamStyle.qmark)

There are presets for some common databases (see below), and I’m very happy to accept PRs for any others.

csql.dialect

class csql.dialect.SQLDialect(paramstyle=ParamStyle.numeric, limit=Limit.limit)[source]

Represents settings of a SQL Dialect.

>>> import functools
>>> from csql.dialect import SQLDialect, ParamStyle
>>> my_dialect=SQLDialect(paramstyle=ParamStyle.qmark)
>>> p = Parameters(value=123)

To use as a once-off, pass to csql.Query.build():

>>> q = Q(f"select {p['value']}")
>>> q.build() # builds normally
RenderedQuery('select :1', (123,))
>>> q.build(dialect=my_dialect) # builds with `my_dialect`
RenderedQuery('select ?', (123,))

To set as a default, use functools.partial:

>>> Q = functools.partial(csql.Q, dialect=my_dialect)
>>> q = Q('select ...') # builds with `my_dialect`
Parameters:
class csql.dialect.ParamStyle[source]

Enum to define how to render query parameter placeholders.

numeric

Use placeholders like :1.

numeric_dollar

Use placeholders like $1.

qmark

Use placeholders like ?.

clickhouse = 4

Use placeholders like {MyParam:String}

class csql.dialect.Limit[source]

Enum to defines how to limit preview queries.

limit

Use a limit clause, e.g. select * from (query) limit 10.

top_n

Use a top n clause, e.g. select top(10) * from (query).’

ansi

Use ANSI SQL fetch clause, e.g. select * from (query) fetch first 10 rows only.

class csql.dialect.InferOrDefault[source]

A wrapper to flag that this query should use a previous query’s dialect if not otherwise specified.

Parameters:

dialect (csql.dialect.SQLDialect)

dialect: csql.dialect.SQLDialect
csql.dialect.DefaultDialect = SQLDialect(paramstyle=ParamStyle.numeric, limit=Limit.limit)

The default dialect for csql.

csql.dialect.Snowflake = SQLDialect(paramstyle=ParamStyle.numeric, limit=Limit.limit)

A dialect for Snowflake

csql.dialect.DuckDB = SQLDialect(paramstyle=ParamStyle.numeric_dollar, limit=Limit.limit)

A dialect for DuckDB

csql.dialect.MSSQL = SQLDialect(paramstyle=ParamStyle.numeric, limit=Limit.top_n)

A dialect for MS SQL Server

Advanced Customization

If the SQL Dialects system isn’t enough, you have the ability to reach in and provide alternative rendering implementations. You can do anything here, from rendering parameters differently, all the way through to pre-processing and assembling Queries in an arbitrary way.

To customize rendering with your own implementations, pass an csql.overrides.Overrides to csql.Query.build() or csql.Q() overrides. For example:

from csql.render.param import ParameterRenderer

class MyParamRenderer(ParameterRenderer):
   ... # left as an exercize for the reader

overrides = Overrides(paramRenderer=MyParamRenderer)

p = Parameters(val=123)
q = Q('select * from thingers where id = {p['val']})
q.build(overrides=overrides)

Like dialects, overrides will by default propagate if they’ve been set on referenced queries:

>>> from csql.overrides import Overrides
>>> o = Overrides()
>>> q1 = Q(f'select * from thinger', overrides=o)
>>> q2 = Q(f'select count(*) from {q1}')
>>> assert q2.default_overrides == o
class csql.overrides.Overrides(paramRenderer: 'type[csql.render.param.ParameterRenderer] | None' = None, queryRenderer: 'type[csql.render.query.QueryRenderer] | None' = None)[source]
Parameters:
class csql.overrides.InferOrDefault(overrides: 'csql.overrides.Overrides | None')[source]
Parameters:

overrides (csql.overrides.Overrides | None)

Parameter Rendering

The builtin parameter renderers are found in csql.render.param.

csql.render.param

class csql.render.param.QMark[source]

A ParameterRenderer that renders param placeholders as ‘?’.

class csql.render.param.ColonNumeric[source]

A ParameterRenderer that renders param placeholders like ‘:1’.

class csql.render.param.DollarNumeric[source]

A ParameterRenderer that renders param placeholders like ‘$1’.

To customize parameter rendering, subclass csql.render.param.ParameterRenderer.

class csql.render.param.ParameterRenderer[source]

This is a base class to define how SQL parameters are rendered.

A new ParameterRenderer is created each time a csql.Query is built, and _renderScalarSql() is called once for each parameter that needs to be placed into full csql.RenderedQuery. These are called in the order the parameters appear in the rendered query.

abstractmethod _renderScalarSql(index, key, fmt)[source]

This is called once for each parameter that needs to be rendered into a csql.RenderedQuery. Implementations might be simple: for example, the builtin csql.render.param.QMark renderer just defines

def _renderScalarSql(self, index, key):
        return SQL('?')
Parameters:

  • index (int) –

    • the index of the current parameter in the rendered query. Numbered from 0.

  • key (AutoKey | str | None) –

    • the (possibly missing) name of the current parameter.

  • fmt (str) –

    • the format spec passed at f-string time, e.g. f”select {p[“param”]:spec}”.

Return type:

csql.render.param.SQL

class csql.render.param.SQL

NewType creates simple unique types with almost zero runtime overhead.

NewType(name, tp) is considered a subtype of tp by static type checkers. At runtime, NewType(name, tp) returns a dummy callable that simply returns its argument.

Usage:

UserId = NewType('UserId', int)

def name_by_id(user_id: UserId) -> str:
    ...

UserId('user')          # Fails type check

name_by_id(42)          # Fails type check
name_by_id(UserId(42))  # OK

num = UserId(5) + 1     # type: int

A NewType alias for a str representing a chunk of SQL. alias of str

class csql.render.param.AutoKey(k)[source]

A wrapper for a parameter key, indicating it was generated automatically by csql.Parameters.add().

Parameters:

k (str)

k: str

Query Renderering

The big one… this lets you override how queries are constructed. Currently the only implementation is csql.render.query.BoringSQLRenderer, but there may be others added in future (e.g. I can imagine some poor soul might need to render as a big mess of nested subqueries instead of a CTE)

csql.render.query

class csql.render.query.BoringSQLRenderer(ParamRenderer, dialect)[source]

Render a Query. Referenced other Queries are all assembled with this one into a CTE/with expression.

Parameters:
class csql.render.query.QueryRenderer(ParamRenderer, dialect)[source]

You should subclass this guy if you want to handle rendering yourself. I’m reserving the right to change this API, though - be warned.

Parameters: