Fetch terminals

A query offers four properties to inspect it without executing it. sqltext is the SQL. sqlparams is the mapping of parameter values. test returns both, as a tuple. compiled is the CompiledQuery object. It carries the SQL, the parameters and the result columns. None of these opens a connection. The first access compiles the query, and the result is kept. sqlparams leaves out the env underscore parameters, because their values are read from the environment at every execution.

fetch executes the query and returns a list of rows. Each row is a NamedList from the genro_toolbox library. You read a value by name or by position. Negative positions work as in a list. keys, items, values, get and update work as in a dictionary. The in operator tests a column name. Iteration yields the values, not the names, because a NamedList is a list. dict of row gives a plain dictionary; use it for JSON or for comparisons. A row is mutable. Changing a value changes only the Python object, never the database. Rows of one fetch share their column index: a new name added to one row is visible to the others with the value None.

A query keeps its compiled SQL, but not its rows. Every terminal executes the SQL again on the current connection. In this example the second fetch sees the invoice inserted after the first fetch, because it runs in the same transaction. No terminal commits. You end the transaction yourself, with commit or rollback.

fetchRecord returns exactly one row, in the same format as fetch. If no row matches, it raises RecordNotFoundError. With ignoreMissing equal True it returns None instead. If several rows match, it raises RecordMultipleRowsError. There is one exception. When the query has an explicit order_by, fetchRecord compiles a second version of the query with LIMIT one and returns the first row. The row policies of lesson eighteen apply as in every terminal, so a draft or deleted row is not found unless you change the query options.

count executes a counting version of the query. For an ordinary query it selects count star and returns the number. For a query with group_by it selects the group keys and returns the number of groups. For a query with distinct it keeps the projection and returns the number of distinct rows. ORDER BY is removed. LIMIT and OFFSET are kept, and they apply to the counting query, not to the rows counted. A limit of two on an ordinary query still returns four, because count star produces one row.

The examples from here on use a query without a filter, which returns the four invoices. When the result is large, read it from a cursor. execute and cursor both run the query and return the open cursor. The cursor has description, rowcount, fetchone, fetchmany, fetchall and iteration, and its rows are NamedList. You close it. servercursor opens a named server-side cursor on PostgreSQL; on SQLite it returns a normal cursor. iterfetch yields lists of at most arraysize rows, read from a server-side cursor. The default arraysize is thirty. The last list is empty. serverfetch returns the cursor and the same generator. The macros hash BAG and hash BAGCOLS are applied by iterfetch and serverfetch, but not by execute and cursor: a cursor returns the XML text.

Several terminals reshape the rows of fetch. fetchPkeys returns the primary-key values in query order. fetchAsDict returns a dictionary keyed by a column; by default the key is the primary key. With ordered equal True it returns an OrderedDict. With pkeyOnly equal True each value is the primary key instead of the row. fetchGrouped returns lists of rows grouped by a column, in the order of the fetch. Each of these terminals reads the key from the rows. The key column must be in the projection. fetchPkeys on a query without the id column raises KeyError.

fetchAsJson returns the rows as a JSON array of objects. Datetimes are written in ISO format. Every other value that JSON does not support goes through str, so a decimal total becomes a string. The method accepts a key argument, but it does not use it. fetchAsBag returns a Bag with one node per row. The node label is the key column, and the row is stored in the node attributes. The nodes are sorted by the key. fetchGrouped with asBag equal True returns the groups in a Bag.

To recap. sqltext, sqlparams, test and compiled inspect a query without running it. fetch returns mutable NamedList rows, and every terminal executes the SQL again. fetchRecord returns one row, count returns a number, and cursors read large results in pieces. The next lesson covers the environment.