csql.contrib

csql.contrib contains some useful extensions, like alternative renderers and cache implementations.

These may be directly useful, and additionally are a good starting point if you want to write your own customizations.

Warning

Things in contrib are not subject to the same promises of API stability as the rest of csql. I won’t go out of my way to make your life difficult, but at the same time I reserve the right to make more aggressive changes in here.

csql.contrib.persist

See also: Persistance / Caching

contrib.persist contains some csql.persist.Cacher implementations.

class csql.contrib.persist.TempTableCacher(connection)

The TempTableCacher persists a query in a create temporary table if not exists statement. It needs to be given a DBAPI-compliant connector to work.

>>> from csql.contrib.persist import TempTableCacher
>>> con = my_connection()
>>> cache = TempTableCacher(con)
>>> q = Q('select * from slow_view').persist(cache)
>>> q2 = Q(f'select count(*) from {q}') # does nothing
>>> print(q2.build().sql) 
with
_subQuery0 as (...)
select count(*) from _subQuery0

Parameters

connection (Any) –

csql.contrib.persist.snowflake

contrib.persist.snowflake contains cache implementations specifically for the Snowflake database. For this file to import properly, you’ll need to make sure snowflake-connector-python is installed.

class csql.contrib.persist.snowflake.SnowflakeResultSetCacher(connection)

Caches queries using the RESULT_SCAN functionality of snowflake. This is nice because it means you can kill your snowflake connection without losing temp tables, and the results are still cleaned up properly by Snowflake in 7 days.

Parameters

connection (snowflake.connector.Connection) –

csql.contrib.render

csql.contrib.render contains some pre-built alternative renderers.

csql.contrib.render.param

csql.contrib.render.param contains alterantive csql.render.param.ParameterRenderer implementations.

class csql.contrib.render.param.UDFParameterRenderer

UDFParameterRenderer renders parameters as just their key, for use in a UDF body.

Example:

>>> p = Parameters(start=date(2021,5,5))
>>> q = Q(f'''select * from purchases where purchase_date > {p['start']}''')
>>> q.build().sql
'select * from purchases where purchase_date > :1'
>>> from csql.contrib.render.param import UDFParameterRenderer
>>> udf_overrides = csql.Overrides(paramRenderer=UDFParameterRenderer)
>>> udf_body = q.build(overrides=udf_overrides).sql
>>> print(udf_body)
select * from purchases where purchase_date > start
>>> con = some_connection()
>>> con.cursor().execute(f'''
...   create function my_func(date) as
...   $$
...   {udf_body}
...   $$
... ''')
>>> #