11.2.3. dbSelect¶
Last page update: November 23, 2011
11.2.3.1. Definition and Description¶
- GnrWebAppHandler.dbSelect(dbtable=None, columns=None, auxColumns=None, hiddenColumns=None, rowcaption=None, _id=None, _querystring='', querystring=None, ignoreCase=True, exclude=None, excludeDraft=True, condition=None, limit=None, alternatePkey=None, order_by=None, selectmethod=None, notnull=None, weakCondition=False, **kwargs)¶
dbSelect is a FilteringSelect that takes the values through a query on the database: user can choose between all the values contained into the linked table (the table is specified through the dbtable attribute). While user write in the dbSelect, partially matched values will be shown in a pop-up menu below the input text box. You can show more columns in the pop-up menu through the auxColumns attribute
- dbSelect() method is decorated with the public_method() decorator
Parameters:
- dbtable – the database table related to the dbSelect. For more information, check the dbtable section (simple example)
- columns – list of columns. User can select the record respect all the columns specified through this attribute. For more information, check the columns section (columns example)
- auxColumns – list of columns separated by a comma. Every columns must have a prefix ($). Show the columns you specify here as auxiliary columns in a pop-up menu (auxColumns example)
- hiddenColumns – data that is retrieved but they are not shown
- rowcaption – what user see into the field. Check the rowcaption section
- querystring – TODO
- ignoreCase – boolean. Set it True for a case insensitive query from characters typed from user. Set to False for a case sensitive query
- exclude – TODO
- excludeDraft – boolean. TODO
- condition – additional conditions into the query - the basic condition is set by user typing characters in the field (condition example)
- limit – string. Number of result’s rows (default is 10, set limit to ‘0’ to visualize all data). Corresponding to the sql “LIMIT” operator. For more information, check the limit section
- alternatePkey – TODO
- order_by – corresponding to the sql “ORDER BY” operator. For more information check the order_by section
- selectmethod – custom rpc_method you can use to make the query on the server.
- notnull – TODO
- weakCondition – boolean. It will apply the condition if there is a result, but if there is no result for the condition then the condition will not be used. The selectmethod attribute can be used to override this attribute
- kwargs –
- selected_COLUMNNAME: allow to save in the datastore some columns of the record chosen by user (“COLUMNNAME” is the name of the column). For every column to be taken there must be a “selected_COLUMNNAME” (selected example)
Please check the simple example below for a deeper description of the dbSelect main attributes
11.2.3.2. Examples¶
11.2.3.2.1. simple example¶
Description: a basic example of a dbSelect.
- The dbtable specify the database table from which user chooses the record. To define a default dbtable value for all the elements of your page that supports it you can use the webpage variable called maintable. Clearly, if you define a dbtable attribute in a object, it prevails on the maintable
Note
example elements’ list:
- classes: GnrCustomWebPage
- components: TestHandlerFull
- webpage variables: py_requires
- widgets: formbuilder
Code:
# -*- coding: UTF-8 -*- """dbSelect""" class GnrCustomWebPage(object): py_requires = "gnrcomponents/testhandler:TestHandlerFull" def test_1_db(self, pane): """Basic dbSelect""" fb = pane.formbuilder(cols=3) fb.div("""In this test you can see the basic funcionalities of the dbSelect attribute: the "dbtable" attribute allows to search from a database table, saving the ID of the chosen record.""", colspan=3) fb.div('saved in \"test/test_1_db/id\"') fb.dbSelect(dbtable='showcase.person', value='^.id', limit=10) fb.div("""dbSelect default attributes: limit=10, hasDownArrow=False, ignoreCase=True""") fb.div('saved in \"test/test_1_db/id2\"') fb.dbSelect(dbtable='showcase.person', value='^.id2', hasDownArrow=True) fb.div("""The hasDownArrow=True override the limit=10, and let the user see all the entries""")
11.2.3.2.2. auxColumns example¶
Note
example elements’ list:
- classes: GnrCustomWebPage
- components: TestHandlerFull
- webpage variables: py_requires
- widgets: formbuilder
Code:
# -*- coding: UTF-8 -*- """dbSelect""" class GnrCustomWebPage(object): py_requires = "gnrcomponents/testhandler:TestHandlerFull" def test_2_auxcolumns(self, pane): """\"auxColumns\" attribute""" fb = pane.formbuilder(cols=2) fb.div('With \"auxColumns\" attribute you let user see more columns during selection') fb.dbSelect(dbtable='showcase.person', value='^.person_id', hasDownArrow=True, auxColumns='$nationality,$b_year')
11.2.3.2.3. selected example¶
Description: the “selected” attribute allow to save in the datastore more columns respect to the standard column taken, that is the id column
The syntax is:
selected_COLUMNNAME='STORE_ADDRESS'Where:
- COLUMNNAME is the name of a single column
- STORE_ADDRESS is the path in datastore for the column
Note
example elements’ list:
- classes: GnrCustomWebPage
- components: TestHandlerFull
- webpage variables: py_requires
- widgets: formbuilder
Code:
# -*- coding: UTF-8 -*- """dbSelect""" class GnrCustomWebPage(object): py_requires = "gnrcomponents/testhandler:TestHandlerFull" def test_3_selected(self, pane): """\"selected\" attribute""" fb = pane.formbuilder() fb.div("""If you want to keep in the datastore some attributes of the chosen record (in addition to the ID), you have to use the "selected" attribute""",colspan=3) fb.div("""In this example you get the column \"name\" and the column \"b_year\" and set their value in a custom path. In particular we put the content of the \"nationality\" column in \"test/test_3_selected/nationality\" and the content of the \"b_year\" column in \"test/test_3_selected/year\". You can see them in datastore (ctrl+shift+D), but you can see them even in the two \"readOnly\" fields""",colspan=3) fb.dbSelect(lbl='musician', dbtable='showcase.person', value='^.id', selected_nationality='.nationality', selected_b_year='.year') fb.textbox(lbl='nationality', value='^.nationality', readOnly=True) fb.textbox(lbl='birth year', value='^.year', readOnly=True)
11.2.3.2.4. condition example¶
Note
example elements’ list:
- classes: GnrCustomWebPage
- components: TestHandlerFull
- webpage variables: py_requires
- widgets: formbuilder
Code:
# -*- coding: UTF-8 -*- """dbSelect""" class GnrCustomWebPage(object): py_requires = "gnrcomponents/testhandler:TestHandlerFull" def test_4_condition(self, pane): """\"condition\" attribute""" fb = pane.formbuilder() fb.div("""If you have two or more database tables in relation, you can allow the user to choose a record with a first "dbSelect"... """) fb.dbSelect(dbtable='showcase.person', value='^.person_id', lbl='Musician', selected_name='.name', selected_b_year='.b_year') fb.div("""... and then you can make the user choose an attribute relative to the first record selected through a second dbSelect:""") fb.dbSelect(dbtable='showcase.person_music', value='^.music_id', lbl='Music', condition='$person_id=:pid', condition_pid='=.person_id', alternatePkey='music_id')
11.2.3.2.5. columns example¶
Note
example elements’ list:
- classes: GnrCustomWebPage
- components: TestHandlerFull
- webpage variables: py_requires
- widgets: formbuilder
Code:
# -*- coding: UTF-8 -*- """dbSelect""" class GnrCustomWebPage(object): py_requires = "gnrcomponents/testhandler:TestHandlerFull" def test_5_columns(self, pane): """\"columns\" attribute""" fb = pane.formbuilder() fb.div("""The \"columns\" attribute allows user to search respect to all the fields you specify in it. In this example we specify both \"name\" and \"nationality\", so try to look for an actor respect its name or its nationality (you can try \"Czech\", \"German\" or \"Austrian\", for example)""") fb.dbSelect(dbtable='showcase.person', value='^.value', columns='$name,$nationality', auxColumns='$name,$nationality,$b_year,$d_year')
