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
paramstyle (csql.dialect.ParamStyle) –
limit (csql.dialect.Limit) –
- 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
.
- class csql.dialect.InferOrDefault[source]
A wrapper to flag that this query should use a previous query’s dialect if not otherwise specified.
- 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: 'Optional[Type[csql.render.param.ParameterRenderer]]' = None, queryRenderer: 'Optional[Type[csql.render.query.QueryRenderer]]' = None)[source]
- Parameters
paramRenderer (Optional[Type[csql.render.param.ParameterRenderer]]) –
queryRenderer (Optional[Type[csql.render.query.QueryRenderer]]) –
- Return type
None
- class csql.overrides.InferOrDefault(overrides: 'Optional[csql.overrides.Overrides]')[source]
- Parameters
overrides (Optional[csql.overrides.Overrides]) –
- Return type
None
Parameter Rendering
The builtin parameter renderers are found in csql.render.param
.
csql.render.param
- 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 acsql.Query
is built, and_renderScalarSql()
is called once for each parameter that needs to be placed into fullcsql.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 builtincsql.render.param.QMark
renderer just definesdef _renderScalarSql(self, index, key): return SQL('?')
- Parameters
index (int) –
the index of the current parameter in the rendered query. Numbered from 0.
key (Optional[Union[AutoKey, str]]) –
the (possibly missing) name of the current parameter.
- Return type
- class csql.render.param.SQL(x)
A
NewType
alias for astr
representing a chunk of SQL.
- 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) –
- Return type
None
- 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
ParamRenderer (Type[csql.render.param.ParameterRenderer]) –
dialect (SQLDialect) –
- 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
ParamRenderer (Type[csql.render.param.ParameterRenderer]) –
dialect (SQLDialect) –