DBFrames tutorial

Creating your first application

Before you try using DBFrames on your WinCE device, it's recommended to install
Python, ppygui-win32 and DBFrames on your desktop or laptop. If your application
will work fine on desktop, you can copy it on the handheld.

Create file firstapp.py and put the following code into it.

importdbframesasDclassProducts(D.DBFrame):""" This class creates tb_products table with fields f_code_prod, f_name, f_price."""tablename="tb_products"title="Products"columns=({"name":"f_code_prod","type":D.INTEGER,"title":"Code"},{"name":"f_name","type":D.TEXT,"title":"Name"},{"name":"f_price","type":D.REAL,"title":"Price","default":lambdaself:0,},)# Only one class to initialize and put into the application menuD.G.initorder=(Products,)D.G.menuorder=(Products,)D.start()

Start this by python firstapp.py and you will see the application window.

Each DBFrame object has two view modes - List and Form. But only Form
view allows data editing.
To add new product entry press + button, enter product code, name, price and
pess OK.
Also don't forget to press OK to write changes to database after any data
editing.

More complicated application

Let's assume you need a mobile application to gather sale orders from your
retail outlets. So you have to create four DBFrame classes:
Outlets, Products, Orders and OrderDetails.

First of all, create the separate configuration file where you will define
your custom classes. Put the following code into the file config.py:

importdatetimeimportdbframesasD# Database file nameD.G.dbfilename="orders.db"# Define the application titleD.G.lstr["Application Title"]="Sale Orders"classOutlets(D.DBFrame):tablename="tb_outlets"title="Outlets"columns=({"name":"f_code_id","type":D.INTEGER,"title":"Code","default":lambdaself:0,},{"name":"f_name","type":D.TEXT,"title":"Name","default":lambdaself:'',},)classProducts(D.DBFrame):tablename="tb_products"title="Products"columns=({"name":"f_code_prod","type":D.INTEGER,"title":"Code","default":lambdaself:0,},{"name":"f_name","type":D.TEXT,"title":"Name","default":lambdaself:'',},{"name":"f_price","type":D.REAL,"title":"Price","default":lambdaself:0,},)classOrderDetails(D.DBFrame):tablename="tb_order_details"title=u"OrderDetails"columns=({"name":"f_code_order","type":D.INTEGER,"title":u"Code","show":False},{"name":"f_code_prod","type":D.INTEGER,"title":u"Product","default":lambdaself:0,"foreign":{"dbframe":Products,"key_fld":"f_code_prod","str_fld":"f_name",}},{"name":"f_num","type":D.INTEGER,"title":u"Quantity","default":lambdaself:0},{"name":"f_sum","type":D.REAL,"title":u"Amount","default":lambdaself:0,"readonly":lambdaself:True},{"name":"f_price","type":D.REAL,"title":u"Price","default":lambdaself:0,"readonly":lambdaself:True},)total="f_sum"defon_ok(self):# Save price and amountself.set_edit_value("f_price",D.G.objects[Products].get_by_value("f_code_prod",self.get_edit_value("f_code_prod"),"f_price"))self.set_edit_value("f_sum",self.get_edit_value("f_price")*self.get_edit_value("f_num"))classOrders(D.DBFrame):tablename="tb_orders"title="Orders"columns=({"name":"f_code_id","type":D.INTEGER,"title":"No","constr":lambdaself:"PRIMARY KEY AUTOINCREMENT","readonly":lambdaself:True},{"name":"f_date","type":D.DATE,"title":"Date","default":lambdaself:datetime.date.today(),"filter":lambdaself:("=",datetime.date.today())},{"name":"f_code_outlet","type":D.INTEGER,"title":"Outlet","default":lambdaself:0,"foreign":{"dbframe":Outlets,"key_fld":"f_code_id","str_fld":"f_name",},"filter":lambdaself:None},{"name":"f_sum","type":D.REAL,"title":u"Amount","default":lambdaself:0,"readonly":lambdaself:True,"filter":lambdaself:None},)total="f_sum"orderby=lambdaself:[("f_code_id","asc"),]detail=(OrderDetails,"f_code_order")defon_ok(self):# Calculate total amountself.get_edit("f_sum").text=self.details.sum_col("f_sum",False,True)# Check outlet selectionifself.get_edit_value("f_code_outlet")==0:ret=D.Message.ok(u"Warning",u"Outlet not selected","warning",self)# Application objects in initialization order.# 'Products' and 'Outlets' are joined as 'foreign' to other objects, so they# must be initialized first than objects they joined to.D.G.initorder=(Products,Outlets,Orders,OrderDetails)# Application objects in menu and notebook position orderD.G.menuorder=(Orders,OrderDetails,Products,Outlets)

Also create the file start.py to use config.py file:

importdbframesfromconfigimport*dbframes.start()

Run python start.py and add entris to Products and Outlets as described
in first application.

To make sale order with multiple detailed entries, add
an entry to Order, press OK button, select OrderDetails tab and add one
or more order detail entries.

When you press Order tab again you will see
that Amount field is placed with sum of the detail amounts.

The number between navigation buttons and filter button on Order tab means
total sum of sale orders on chosen date as defined in class Order in line
"filter": lambda self: ("=", datetime.date.today())
You can press F button and choose other date for filter.

Adding actions

Paste the following code to the file actions.py.

importppygui.apiasguiimportdbframesasDclassTotals(D.ActionDialog):def__init__(self,parent,title):D.ActionDialog.__init__(self,parent,title)self.dctrl=gui.Date(self)self.dctrl.value=datetime.date.today()self.bclean=gui.Button(self,"Calculate totals on date",action=self.on_click_bclean)sizer=gui.VBox((2,2,2,2),spacing=2)sizer.add(self.dctrl)sizer.add(self.bclean)self.sizer=sizerdefon_click_bclean(self,event):totals=({"text":"Number of orders: %s\n","sql":"select count(*) from tb_orders where f_date=? and f_sum > 0","result":0,},{"text":"Total amount of orders: %s\n","sql":"select sum(f_sum) from tb_orders where f_date=?","result":0,},)c=self.dbconn.cursor()messtxtl=[]fortintotals:c.execute(t["sql"],(self.dctrl.value,))r=c.fetchone()ifr!=None:ifr[0]!=None:t["result"]=r[0]messtxtl.append(t["text"]%t["result"])ret=gui.Message.ok("Message","".join(messtxtl),"info",self)classRemoveOldOrders(D.ActionDialog):def__init__(self,parent,title):D.ActionDialog.__init__(self,parent,title)self.dctrl=gui.Date(self)self.dctrl.value=datetime.date.today()self.bclean=gui.Button(self,"Delete all orders before the date",action=self.on_click_bclean)sizer=gui.VBox((2,2,2,2),spacing=2)sizer.add(self.dctrl)sizer.add(self.bclean)self.sizer=sizerdefon_click_bclean(self,event):fromconfigimportOrders,OrderDetailsifself.objects[Orders].crow_editorself.objects[OrderDetails].crow_edit:ret=gui.Message.ok("Message","There is an unsaved order. Save it before orders deleting","warning",self)returnret=gui.Message.yesno("Warning","ALL orders before %s will be deleted. Continue?"%self.dctrl.value,"warning",self)ifret=="yes":c=self.dbconn.cursor()sql="select f_code_id from tb_orders where f_date < ?"c.execute(sql,(self.dctrl.value,))ordrs=c.fetchall()sql="delete from tb_order_details where f_code_order=?"c.executemany(sql,ordrs)sql="delete from tb_orders where f_code_id=?"c.executemany(sql,ordrs)self.dbconn.commit()rc=c.rowcountifrc<0:rc=0ret=gui.Message.ok("Message","Deleting completed.\nOrders deleted: %s.\n"%rc,"info",self)self.objects[Orders].listform.fill_with_data()self.objects[OrderDetails].listform.fill_with_data()

Start the application and press Action button. You will see pop-up menu with
two items: Totals and Delete old orders.
Totals calculates number of orders per chosen date and total amount of them.
Delete old orders allows batch deleting orders with date earlier than chosen.