DataSet

From Dabo Wiki
Jump to: navigation, search

Dabo cursor objects hold their data in DataSet objects. Internally, the class dabo.db.dDataSet is a tuple of dictionaries, where each element of the tuple represents a row in the data; each row is represented by a dictionary with each key-value pair being a column name and its value in that row. You can get a DataSet by calling a bizobj's getDataSet() method. For those of you coming from a VisualFoxPro background, you can think of them as you would a Fox cursor.

The cool thing about DataSets is that they understand SQL commands. You can run a SQL Select statement against the DataSet object, and a new DataSet containing the result of executing that statement is returned. The data in the original DataSet is unchanged.

Since you aren't querying an actual table, what do you use for the FROM clause? The convention we've adopted is to simply use dataset in the FROM clause as the name of the source table. Like all SQL, this is not case-sensitive.

You aren't limited to a single DataSet, by the way. You can do multi-DataSet joins quite easily. The only catch is that you have to decide what alias names to use for each of the additional DataSets. Then pass a dictionary containing your alias:DataSet as the key:value pairs.

custDS = customerBizobj.getDataSet()
ordersDS = ordersBizobj.getDataSet()
ordItemsDS = orditemsBizobj.getDataSet()


# If you are in an onHit() method of a GUI control:
# custDS = self.Form.getBizobj(dataSource='name_of_table').getDataSet()


# Get all customers whose company name begins with 'ST'
ds = custDS.execute("select * from dataset where company like 'ST%' ")


# Get several columns from the Orders table 
ds = ordersDS.execute("""select order_net, shipped_on as shipDate 
        from dataset
        where order_net >= 1000
        order by shipDate desc""")


# Join all three DataSets together
aliasDict = {"ord" : ordersDS, "orditem" : ordItemsDS}
ds = custDS.execute("""select dataset.company, dataset.contact, 
        ord.shipped_on, ord.order_net, orditem.unit_price, orditem.quantity, 
        (orditem.unit_price * orditem.quantity) as itemTotal
        from dataset join ord
                on dataset.pkid = ord.cust_fk
        join orditem 
                on ord.pkid = orditem.order_fk
        where dataset.company = 'Franchi S.p.A.'
        order by ord.order_date""", aliasDict)

Replacing

You can replace the value of a field in a DataSet with a given value or expression by calling the replace() method of the DataSet, passing in the field name, the value/expression to replace that field with, and optionally a scope expression that will limit the affected records. The scope should evaluate to a boolean value for each row in the DataSet, and only those for which scope=True will be modified. The DataSet is modified in place; there is no return value from the replace() method.

Sorting

You can obtain a sorted copy of a DataSet by calling its sort() method and passing in the field name to sort on. By default, the sort is done ascending and case-insensitive; you can modify those defaults by passing in either of two additional parameters: asdesc (ASC or DESC) and caseSensitive (bool).

Filtering

DataSets have a filter() method that allows you to easily control the rows in the data without having to create and execute queries. Additionally, filters can be removed using the removeFilter() method; this will "unfilter" in the reverse order that the filters were applied to return to your original data. Example:

 ds = bizobj.getDataSet()
 print len(ds)
 ## prints 100
 ds = ds.filter("lastname", "S", "startswith")
 print len(ds)
 ## prints 24
 ds = ds.filter("income", 80000, ">")
 print len(ds)
 ## prints 6
 ds = ds.removeFilter()
 print len(ds)
 ## prints 24
 ds = ds.removeFilter()
 print len(ds)
 ## prints 100

There is also a removeFilters() (note the plural) that will remove all filters on a data set at once.

To apply a filter, pass in the field name to filter on, a matching expression, and (optionally) an operator. If no operator is supplied, the default of = will be used. You may also pass one of the following strings in, and the corresponding operator will be used:

Filter String Operator
eq =
equals =
gt >
gte >=
lt <
lte <=
startswith LIKE%
beginswith LIKE%
endswith  %LIKE
contains  %LIKE%