SnowDDL
Search…
πŸ—
Query builder & formatter
SnowDDL builds a lot of raw SQL queries. In order to simplify this task while keeping the full flexibility of SQL, SnowDDL provides query builder and query formatter via its engine.

Query builder

Query builder is used to build complex SQL queries from small fragments. Each fragment can be added to the current line or it may start a new line.
For example, consider the following query fragments:
['SELECT', 'id AS user_id', 'name AS user_name']
['FROM', '"MY_TABLE"']
['WHERE', 'country_id = 10']
Final query:
SELECT id AS user_id, name AS user_name
FROM "MY_TABLE"
WHERE country_id = 10
Example code:
query = engine.query_builder()
​
# Fill the first line
query.append("SELECT")
query.append("id AS user_id")
query.append("name AS user_name")
​
# Add a new line with identifier as parameter
query.append_nl("FROM {table_name:i}", {
"table_name": Ident('MY_TABLE')
})
​
# Add another line with filters
query.append_nl("WHERE")
query.append_nl("country_id = {country_id:d}", {
"country_id": 10
})
​
# Display query
print(query)
​
# Execute query
engine.execute_meta(query)
In order to create a new empty query builder, call function .query_builder() of engine class.

Methods

  • append(sql, params=None) Append a fragment to the current line. Optionally format it using placeholders and dictionary with params.
  • append_nl(sql, params=None) Start a new line and append a fragment to it. Optionally format it using placeholders and dictionary with params.
  • fragment_count() Return a number of fragments across all lines.
  • add_short_hash(comment) Part of short hash feature. Take the comment for object and adds a short hash at the end. Return updated comment.
  • compare_short_hash(comment) Part of short hash feature. Extract short hash from existing comment and compare it with expected short hash. Return True if hashes are the same, return False otherwise.
  • __str__() Query builder objects can be used as normal strings.

Formatter

SnowDDL formatter uses its own custom syntax for placeholders and supports a wide range of placeholder types.

Syntax

Only named placeholders are supported. No positional placeholders on purpose.
The syntax is: {name:type}
Type is optional. Default type is :s (common value).

Usage notes

Query formatting is enabled only when params are explicitly defined as dict. It allows you to use your own query formatter if it is necessary.
All placeholders defined in SQL query text must be present in params, no "defaults". It helps to prevent typos and accidental damage associated with it.
If you pass a list of values for placeholder instead of single value, it will be represented as comma-separated list of individually formatted values, which is useful for IN (val1, val2, val3) syntax.

Placeholder types

  • :s - common value, enclosed in single quotes and escaped NULL value is returned as NULL without quotes normally used for VARCHAR, TIMESTAMP, etc.
  • :d - safe decimal value, validated and formatted "as is" normally used for NUMBER, raw integer values in LIMIT, etc.
  • :f - safe float value, validated and formatted "as is" used for FLOAT values only, supports optional exponent
  • :b - safe boolean value, validated and formatted as TRUE or FALSE string used for BOOLEAN values only
  • :i - identifier, enclosed in double quotes and escaped used for identifiers of all types
  • :ia - identifier, but additionally enclosed in single quotes and escaped used for arguments of table functions which accept identifiers in this form
  • :r - raw value, formatted "as is", not validated, dangerous (!) used to add dynamic query fragments which cannot be constructed by other means
  • :lf - LIKE pattern looking for full match (LIKE '{val}')
  • :ls - LIKE pattern looking for match starting with (LIKE '{val}%')
  • :le - LIKE pattern looking for match ending with (LIKE '%{val}')
  • :lse - LIKE pattern looking for match starting and ending with (LIKE '{start}%{end}') accepts tuple with two elements
Example of SQL with placeholders:
​
SELECT {common_val} AS common_val
, {null_val} AS {col_name:i}
, u.user_id
, sum(gross_amt) AS gross_amt
FROM {table_name:i} u
WHERE u.user_rating >= {user_rating:d}
AND u.user_score > {user_score:f}
AND u.is_female IS {is_female:b}
AND u.status IN ({user_statuses})
AND u.user_rating NOT IN ({exclude_user_score:d})
GROUP BY 1,2,3
ORDER BY 4 DESC
LIMIT {limit:d}
Query formatting code:
# SQL with formatting
params = {
'common_val': 'abc',
'null_val': None,
'col_name': Ident('NULL_VAL')
'table_name': IdentWithPrefix(env_prefix='ALICE__', 'MY_DB', 'MY_SCHEMA', 'USERS'),
'user_rating': '0.5',
'user_score': 1e1,
'is_female': True,
'user_statuses': ['ACTIVE', 'PASSIVE', 'SUSPENDED'],
'exclude_user_score': [10, 20],
'limit': 10
}
​
print(engine.format(query, params))
Result:
SELECT 'abc' AS common_val
, NULL AS "NULL_VAL"
, u.user_id
, sum(gross_amt) AS gross_amt
FROM "ALICE__MY_DB"."MY_SCHEMA"."USERS" u
WHERE u.user_rating >= 0.5
AND u.user_score > 1e1
AND u.is_female IS TRUE
AND u.status IN ('ACTIVE', 'PASSIVE', 'SUSPENDED')
AND u.user_rating NOT IN (10, 20)
GROUP BY 1,2,3
ORDER BY 4 DESC
LIMIT 10

Overloading

You may extend the standard formatter by overloading SnowDDLFormatter class and by explicitly setting it into overloaded property .formatter of SnowDDLEngine.
Last modified 5mo ago
Copy link
On this page
Query builder
Methods
Formatter
Syntax
Usage notes
Placeholder types
Overloading