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(*args, **kwargs)

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

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

A SqlTable can be viewed alone or as part of a composite widget, that is normally a mask with Relationships: be sure to understand this part as it’s probably one of the more powerfull features of Sqlkit.

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

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

views

A container for the possible Views. It always have a default view named main

totals

class for Totals: see the specific documentation for further info

edited_path

The path of the edited row (a tuple). It’s not the output of get_selected_path() since when you change the selected row the validation is triggered on the previuos selected_row that is pointed to by edited_path.

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

hide_fields(field_name_list='', view_name='main')

Hide a column and add the entry in the menu to make it show up again

Parameters:

• field_name_list – may be a list or a string (in which case it is split with, and spaces)
• view – the view for which we set the list of hidden fields

get_selected_path()

Return the path of the selected row or None

NOTE: treeselection ‘changed’ signal arrives when the selection change has

already occurred. Use edited_path to get the path that is being currently edited

get_obj_at_path(path, all=False)

Return the object at path. Return None if no selection. If the object at path is a total object (see Totals) it will be discarder unless you use param all=True

Parameters:

• path – the modelproxy.modelstore path
• all – boolean: only return object of the main class (no totals)

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, addto=None, naked=False)

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

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 that 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.

Duplicating a row

The row menu (right click) allows to duplicate a row, it requires some understanding of the related issues:

1. Only visible fields are copied
2. Primary keys are not copied
3. If a foreig key is copied and that corresponds to a relation that has cascade='delete-orphan' that relation requires an instance to be attached. The field is able to retrieve such instance and attach it only if you configure the model’s class properly. You can read more info on the field’s method add_related_object

As for Save as mask function, there’s a hook named on_save_as that can be used to configure proper action to be taken before saving the duplicated row