Dabo cursor objects hold their data in DataSet objects. Internally, a DataSet is a tuple of dictionaries, where each element of the tuple is 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()
# 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)