Acces to db made easy

- Prev: SqlMask
- Next: Browsing data

# SqlTable

SqlTable is a widget that presents data in a table mode. Columns that are foreign keys are presented in blue and a foreign key description & search field of the foreign key is used.

class `sqlkit.widgets.``SqlTable`(see_below)

  Main widget to represent data of a sqlalchemy selectable (SELECT of a TABLE, e JOIN, & similar).

  A SqlTable can have different views of the same data represented in different TreeVies.

  A SqlTable can be viewed alone or as part of a composite widget, that is normally a mask with Relationships. Each SqlTable (as SqlMask has a `records` attribute that holds the list of records that it represents, while the model has possibly also other objects as totals and headers.

  SqlTables can have both ListStores or TreeStores. The latter allows to represent rows in a hierarchy.

  SqlTable inherits from `sqlkit.widgets.common.sqlwidget.SqlWidget`

  `create_view`(name='main', treeview=None, field_list=None)

    Create a view, i.e.: a list of columns one for each field in self.field_list. Columns are setup according to the type of the database field, introspected from the mapper. There may be more than one view in a SqlTable. The first one is created mandatorily; possible further ones are created by hand, passing a view name, a treeview and -normally- a field_list. The filter panel output page is a view of the filtered records.

    All the views of a SqlTable share the same model.

    Parameters: | view – the name of the view treeview – the treeview field_list – a list of field_name for which we want the view

  `set_field_list`(field_list, view_name='main')

    Set field of visible columns. Accepts both a list or a string (space or comma separated)

    Parameters: | field_list – the field name list view – the view for which we set the field_list

  `set_editable`(editable=True)

    Turn this table into a normal editable table. A tables is not normally editable if it has `relationship_mode = m2m`

    Parameter: | editable (boolean) – Only meaningful for table that represent an m2m relationship: turns the table from non editable to editable, read the explanation in Relationships

  `set_opts`(icons=True, scroll='xy', width=None)

    Modify default appearance of some features:

    Parameters: | scroll (boolean) – eliminate scrollbars (values: x|y|xy|None) icons (boolean) – allows to disable icons in a m2m relationship width – set the maximum width of the widget

  `select_path`(path=None, obj=None)

    Select path `path`. If path is null and obj is not null try finding path at which is obj

    Parameters: | path – the path to be selected or None obj – if path is None, the object to be searched for and selected

  `add_record`(obj)

    Add record to the model and setup all what is needed

    Parameter: | obj – an object of type `table.mapper.class_`

  `set_fk_layout`(field_name, layout_nick)

    set a layout to be used if opening a mask to edit the foreign key

    Parameters: | field_name – the field_nme of the column layout_nick – the nick name of the layout to be used a ‘layout_nick’

  `record_in_mask`(widget=None)

    Open a SqlMask to show self.current if any and to follow selection.

    Hooks are set on the newly created mask so that validation is retained Remember to set any possible configuration of the table in an `on_init` hook so that it will be propagated.

    If there are pending modification, a save dialog is opened. This function is normally invoked by the first menu entry of the menu popped by right click on a table view this record in a mask.

    Parameter: | widget – the menu entry that invoked it, can be ignored when calling it by hand
    Returns SqlMask:
    | the SqlMask widget

  `fkey_record_in_mask`(widget=None, field_name=None)

    Open a SqlMask to show the fkey of this field

    Parameters: | widget – the menu entry that invoked it, can be ignored when calling it by hand field_name – the foreign key attribute name that should be displayed
    Returns SqlMask:
    | the SqlMask widget

  `add_row_filter`(func)

    Add a filter that selects which rows should be displayed

    Parameter: | func – the function that will be called with the obj as argument and must return a bool

## Attributes

`totals`
  class for Totals: see the specific documentation for further info

## Column headers

Each column is clickable. Clicking on the column pops up a menu that enables:

> - to add a filter on this field (see Filters). This same action can be done by pressing `f` on the column w/o opening the column menu. Only persisted fields can be filtered on.

> - to sort on this field (database sorting). Note that sorting on a ForeignKey will trigger a join with the referenced table and an attempt to sort on the foreign key description & search field (In general you don’t want to sort on the `id`).

>   A “local sort”, i.e. a sort done without hitting the database is performed when you sort a related table (e.g.: as explained in Relationships) or when you press `s` on a column after clicking the table (to set the focus on the table)

> - to add a total on this column (if the column is numeric). This same action can be done by pressing `t` on the column w/o opening the column menu.

> - to toggle a brake on this column (see docs for Totals). This same action can be done by pressing `b` on the column w/o opening the column menu.

> - to hide a column. This same action can be done by pressing `h` on the column w/o opening the column menu.

## Cell renderers

### Boolean

Booleans are represented with CellRendererToggle, if the field is nullable, the value loops over 3 states.

### Text

I don’t have a satisfactory CellRender for long text. Any hint is appreciated.

## Export

A very minimal export function allows to export visible data in a .csv (comma separated value) format. Follow File -> Export. That function can be reach also right-clicking on the table, thus allowing to export that particular view.

## Shortcut

### in the treeeview

Control-x: | eliminates a line
Control-k: | eliminates a line
Control-s: | saves the record
Control-q: | quits the table
Control-n: | opens a new record
Control-z: | zoom the treeview
Shift-Z: | zoom the treeview in a related table (nested in a mask)
s: | sorts the table locally (toggle between ascending and descending)
f: | open a filter for this column
t: | create a total if the column is numeric
v: | toggle visibility of some rows, needs that you set a visibility function via `sqlkit.widgets.SqlTable.add_row_filter()`
b: | toggles a break for subtotals on this column. In addition to this operation it also sort on the column as it often happens you want a subtotal after having ordered. Adding a break from the column menu does not order.

### in the editable cell

Control-Enter: | pops a completion (regexp mode)
Shift-Enter: | pops a completion (start mode)
Esc: | aborts editing

## Signals

button-press-event:
  | this event is emitted when clicking in the treeview if there are no pending validation errors (in which case you are forced to solve them before). It is mainly used to handle the default menu. Look in the example to see how you can manipulate the menu. `button_press_cb`(table, event, obj, field_name, menu, treeview)
  Parameters: | table – the SqlTable that emitted the signal event – the gtk.gdk.Event associated obj – the object currently selected or None field_name – the field_name of the selected column menu – the default menu that il popped up by this button-press-event if event.button == 3 or None treeview – the TreeView widget that was clicked. You may get the view the treeview belongs to with `treeview.get_data('view')` New in version 8.7.

context-changed:
  | Records have been displayed or selection was changed. This is used to track any change in the records both selected or displayed and was added to be used by RecordInMask below `records_displayed_cb(sqlwidget, current):`
  Parameters: | sqlwidget – the widget the emitted the signal current – the obj that was current in the row or the first record in the liststore.

## RecordInMask

Clicking on any row of the treeview gives the possibility to show the record in a Mask View. If a layout was passed to the `SqlTable`, it will be passed to SqlMask. In this case all hooks, fields possibly configured and completions will be copied in the new Mask. Same for the hooks.

If you need to configure this Mask in a particular fashion you can put all configuration in a hook named on_init. This code will be executed both for Table and Mask generation that allows to use the same Hook for both widgets.

Signals and callbacks are arranged so that it will follow the selection of the table. The newly created mask will inherit the mode of the table, i.e. if the table was read-only the mask will be opened in read-only mode. On the other hand the mask will be have browsing inhibited.

If a layout was registered for the database table it will be used.

You can programmatically open this SqlMask using `fkey_record_in_mask`:

    t = SqlTable(...)
    m = t.record_in_mask()

## RecordInMask for ForeignKeys

Similarly, if the column in which you click is a ForeignKey, the popped menu will show an entry to edit the referenced record in a mask.

To be sure it will have the customization you want you can register Layout, Hooks and Class.

In the case you have several possible layout for the same fk, and you want to use a layout that is not the default one, you can use `set_fk_layout`

You can programmatically open the foreign key mask using `record_in_mask`:

    t = SqlTable(model.Movie, ...)
    m = t.fkey_record_in_mask('director_id')

## Views

Views (added in rel 0.8.8) are a way to view the same data in two different TreeViews. This way you can split a very large table into different chunks (vertically), leaving some columns to a view and others to a different view. The same column may be repeated in different views.

An example of a table with views: numbers and dates are really fields of the same table in the database.

### Adding a column

You can add columns to a view if there’s a corresponding `field` (sqlkit.fields.Field) in `table.gui_field`. Examples #31 and #32 show how to do that that essentially boils down to:

1. Create a Field class that has a method that uses as input the object and produces the output, you’ll need a clean_value() method s well if you want to be able to create a total on that column (see example #32)
2. Add an instance of that Field to the fields used in the gui: table.gui_fields
3. Create a Column and add it to the View

    class ObjField(fields.VarcharField):
        """
        A field that presents the obj
        """
        def clean_value(self, value):
            return value and "%(year)s %(title)s" % DictLike(value)

    my_field = ObjField('new_column', {'editable' : False, 'type' : str, 'length' : 30})
    t.gui_fields['new_column'] = my_field
    ## create a column
    col = columns.VarcharColumn(t, 'new_column', 'My New Column', field=my_field)
    ## add it to the view
    t.views['main'].add_column(col, 0)

Note

Adding a column that is not mapped to any db-field leads to a column that cannot be filtered on. On the other hand you can sort (locally, read below) on that column and also get totals.

## Sorting columns

Sorting columns can be done in 2 different ways:

1. using `sqlkit.widgets.common.SqlWidget.order_by` attribute of sqlwidget, that triggers an `ORDER BY` clause on the database

2. using `order_by` clause of `modelproxy`, that triggers a function locally. This option is faster since you don’t need to reload data

   This is the only method you can use to order related table, apart from playing with relation’s order_by attribute in the model

   Currently it can be done:

   programmatically:
   > | by `modelproxy.order_by` with argument a string with field_names possibly prefixed by + or - to define the order:
   >     t = SqlTable(...)
   >     t.modelproxy.order_by('status -description')
   > it can also be used to order a related table:
   >     lay = """
   >          director
   >          o2m=movies
   >     """
   >     m = SqlMask(..., layout=layout)
   >     m.related.movies.modelproxy.order_by('-year title')
   when sorting a field that represent a foreign key, the value of the lookup (i.e.: the value shown) is used rather than the value of the key
   interactively: | currently limited to just one column at a time pressing `s` when focus in in the treeview (i.e. you ave already clicked the treeview). Sorting will toggle ascending and descending.