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")

You can also 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

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

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
Return type

None

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 ?.

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.

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 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)
class csql.Overrides(paramRenderer: 'Optional[Type[csql.render.param.ParameterRenderer]]' = None, queryRenderer: 'Optional[Type[csql.render.query.QueryRenderer]]' = None)[source]
Parameters
Return type

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.

abstract _renderScalarSql(index, key)[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 (Optional[str]) –

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

Return type

csql.render.param.SQL

class csql.render.param.SQL(x)

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

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