Comment on page
🏗
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 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.Alternatively, you may create an instance of class
SnowDDLQueryBuilder
directly.append(sql, params=None)
Appends a fragment to the current line. Optionally formats it using placeholders and dictionary with params.append_nl(sql, params=None)
Starts a new line and append a fragment to it. Optionally formats it using placeholders and dictionary with params.fragment_count()
Returns a number of fragments across all lines.add_short_hash(comment)
Part of short hash feature. Takes the comment for object and adds a short hash at the end. Returns updated comment.compare_short_hash(comment)
Part of short hash feature. Extracts short hash from existing comment and compares it with expected short hash. ReturnsTrue
if hashes are the same, returnsFalse
otherwise.__str__()
Query builder objects can be used as normal strings.
SnowDDL formatter uses its own custom syntax for placeholders and supports a wide range of placeholder types.
Only named placeholders are supported. Positional placeholders are not allowed on purpose. It is important to reduce the amount of accidental mistakes.
The placeholder syntax is:
{name:type}
Type is optional. Default type is
:s
(string value).Query formatting is performed 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
, there are no "defaults". It helps to prevent typos and accidental damage associated with it.If you pass list of values for placeholder instead of single value, it will be represented as concatenated comma-separated string of individually formatted values, which is useful for
IN (val1, val2, val3)
syntax.:s
- common value, enclosed in single quotes and escaped NULL value is returned asNULL
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 asTRUE
orFALSE
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
You may extend or replace standard formatter by overloading
SnowDDLFormatter
class and by explicitly setting it into overloaded property .formatter
of SnowDDLEngine
.Last modified 1mo ago