Parameters and macros

A parameter is written in SQL as a colon followed by a name. Colon minimum is a parameter. You give its value in one of two ways. The first is a keyword argument of query. The second is the sqlparams mapping. Both queries on the slide compile to the same SQL and the same parameters. The value never enters the SQL text. On PostgreSQL the placeholder is written in the driver format, percent, parenthesis, name. Use sqlparams when a parameter name is the same as a query option, or when the names are built at run time. Giving the same name in both places raises ValueError.

In this example the keyword is misspelled: minimun instead of minimum. The query still runs. At compilation asqueel emits a warning of class UnusedQueryParameterWarning. The warning names the unused keyword and the parameters that the query uses. The warning points at your line of code. The check applies to keyword arguments only. An unused key in the sqlparams mapping produces no warning. Colon minimum has no value, so it is bound as NULL. The comparison with NULL is never true, and the result is an empty list. The query does not raise an error for a missing parameter.

To compare a column with several values, write IN colon name and pass a list, a tuple or a set. Preparation writes one placeholder for each member, numbered from zero. NOT IN works the same way. With an empty collection on PostgreSQL, the IN condition becomes FALSE and the NOT IN condition becomes TRUE. On PostgreSQL you can also write equals ANY, with the parameter in parentheses. Then the list is bound as one array parameter. The two forms do not mix. Do not use the same parameter in IN position and in ANY position in one query; PostgreSQL then reports a missing parameter. A string after IN is not expanded, and PostgreSQL reports a syntax error.

The where clause has one more rule. When a parameter value is a string that starts with a dollar sign and names a column of the table, the compiler writes that column into the SQL instead of binding the value. The same happens for a value that starts with an at sign and names an existing relation. The first query on the slide compares gross_total with total. This applies with keywords and with sqlparams alike. A dollar value that names no column stays an ordinary bound value. If your value comes from outside, such as user input, and it can start with a dollar or an at sign, prefix it with a backslash. The backslash is removed before execution, and the text is bound as a plain string.

A parameter whose name starts with env underscore reads its value from the environment. Colon env_customer_id reads the environment key customer_id. The value is read at every execution, not at compilation. If you pass env_customer_id as a parameter, your value is used and the environment is not read. If neither exists, the parameter is NULL. Lesson seventeen covers the environment.

A macro starts with a hash sign. The compiler replaces it with SQL before execution. hash IN_RANGE takes a value and two bounds. Both bounds are inclusive. A NULL bound leaves that side open. In the first query the high bound is None, so the condition means total greater than or equal to one hundred. With two NULL bounds the condition is always true. hash IN_RANGE is expanded in the where clause and in the sql_formula of a formulaColumn. In order_by, group_by, having or the columns, it raises UnsupportedFeatureError.

hash PERIOD takes a date field and a parameter that holds a period in words, such as this month or last month. The colon before the parameter name is optional. The compiler parses the text and binds two dates, period_from and period_to. Parsing uses the workdate and the locale of the environment. The words must be in the language of the locale; with locale en, this month is valid and an Italian phrase raises an error. Set the locale explicitly. The default locale comes from the process, and a value that has no language file raises an error. A single day gives an equality. A closed period gives BETWEEN. An empty text gives true. The dates are computed once, when the query compiles. hash PERIOD is expanded only in the where clause.

The last two macros work on X columns, in the query columns only. In the SQL, hash BAG leaves the plain column. After the fetch, asqueel turns the XML text into a Bag object of the genro_bag library. A NULL value gives an empty Bag. hash BAGCOLS spreads the document instead. Each leaf becomes a column named after the field and the path of the leaf, with dots replaced by underscores. The field itself becomes None. Both macros take a column of the queried table; a relation path raises ValueError. In a where, order_by or formula they raise ValueError. A macro that asqueel does not know, such as hash PREF, raises UnsupportedFeatureError.

To recap. Parameters come from keywords or from sqlparams, and a misspelled keyword produces a warning. IN expands a list, ANY binds an array, and a dollar or at value compares columns. IN_RANGE and PERIOD work in the where clause; BAG and BAGCOLS work in the columns. The next lesson covers the fetch terminals.