4. sql attributes

Last page update: November 23, 2011

• columns
• condition
• deferred
• distinct
• group_by
• having
• limit
• order_by
• where

4.1. columns

The columns attribute represents the columns to be returned by the “SELECT” clause in the traditional sql query.

It is a string of column names and relations separated by comma (you can use a list or a tuple, too).

It is a standard sql column clause and may contain sql functions and the “AS” operator. In addition to sql expressions, a column can be called through the following syntaxes:

• $colname: a column of the main table or a key of the relationDict
• @relname.colname: a related column
• sqlfunction($colname, @relname.colname): $ and @ syntax can be used inside sql functions too
• *: all the columns of the main table (with or without the bagFields)
• *filter: all columns of the main table filtered (check the expandMultipleColumns() method)
• *@relname.filter: all columns of a related table filtered (check the expandMultipleColumns() method)

To select all the columns use the char *

The columns parameter also accepts special statements such as “COUNT”, “DISTINCT” and “SUM”

4.2. condition

Note

the condition parameter is supported by:

• dbSelect
• field (when it acts as a dbSelect)

The condition attribute gathers additional queries respect to the where in your field/component.

In the dbSelect the where attribute must not to be specified because it coincides with the user typing. In those case, you can use the condition attribute if you need to specify more conditions in the query

Syntax:

TODO condition ... Every kwargs params that starts with condition_ are the variables involved in the ‘where’ clause

Example:

If you have the following dbSelect:

# ... other line codes
pane = root.contentPane()
fb = pane.formbuilder(cols=2, border_spacing='6px')
fb.dbSelect()

The query here is specified by what user digits; if he types “john”, then the dbSelect will show all the records that contains those letters.

If we need more conditions on the query, we can use the condition attribute; so, the dbSelect will be replaced with:

fb.dbSelect()

For more information, check the dbSelect page

4.3. deferred

The sql “DEFERRED” clause.

Boolean, True to get... TODO

Example:

TODO

4.4. distinct

The sql “DISTINCT” clause.

Boolean, True for getting a “SELECT DISTINCT”.

Example:

TODO

4.5. group_by

The sql “GROUP BY” clause. Database columns can use one of the following syntaxes:

• $colname

  where colname is the name a table column

• @relname.colname

  where relname is a relation, colname is the name of the column.

Use group_by='*' when all columns are aggregate (TODO) functions in order to avoid the automatic insertion of the pkey field in the columns.

Example:

TODO

4.6. having

The sql “HAVING” clause. Database columns can use one of the following syntaxes:

• $colname

  where colname is the name a table column

• @relname.colname

  where relname is a relation, colname is the name of the column.

4.7. limit

TODO

4.8. order_by

The sql “ORDER BY” clause. A clause that returns the result set in a sorted order based on specified columns.

Database columns can use one of the following syntaxes:

• $colname

  where colname is the name a table column

• @relname.colname

  where relname is a relation, colname is the name of the column.

Example:

TODO

4.9. where

The where attribute represents the table columns to be returned by the “SELECT” clause in the traditional sql query.

Database columns can use one of the following syntaxes:

• $colname

  where colname is the name a table column

• @relname.colname

  where relname is a relation, colname is the name of the column.

Query parameters have to start with colon (:), like:

``@relname.colname=:param1``.

where param1 is the query parameter.

Note

we suggest not to use hardcoded values into the where clause, but refer to variables passed to the selection method as kwargs.

Examples:

where="$date BETWEEN :mybirthday AND :christmas", mybirthday=mbd, christmas=xmas