Working with data in Python
As the Lianja App Builder has an embedded database built into it, you do not need to install any other database software to be able to build multi-user high performance database apps for the desktop, web and mobile devices.
The Lianja embedded database engine is highly compatible with Visual FoxPro 9.0. It includes a feature-complete cross platform implementation of the Visual FoxPro scripting language and database engine.
To facilitate the development of custom UI sections and gadgets, Lianja comes with a cross-platform, UI independent application framework called the Lianja UI Framework.
Note that the Lianja database has a high degree of Visual FoxPro compatibility but has many extensions above and beyond Visual FoxPro to facilitate the deployment of high-availability systems with a high degree of fault tolerance.
Data access with Python
Accessing data in Lianja databases from Python is simple, but nevertheless powerful, exposing much of the power of the Lianja database to Python developers.
Note that if you are building a UI with the Lianja UI Framework, you can bind your UI controls to data sources in the Lianja database just by setting the controlsource property of the UI control to tablename.columnname, you do not need to write any special Python code to accomplish this.
Opening a database
To open a Lianja database from Python you use the global Lianja function openDatabase().
import Lianja db = Lianja.openDatabase("southwind")
Create a Recordset
You can then access a table in the database using the openRecordSet() method of the Database class using SQL or noSQL. For example we can access the customers table using SQL like this:
rs = db.openRecordSet("select * from customers")
Or alternatively just open the customers table with noSQL like this:
rs = db.openRecordSet("customers")
After we have opened a recordset we can navigate through the data using any of the recordset data navigation methods of the Recordset class.
For example to position on the first record in a recordset use the moveFirst() method.
Extract data from the Recordset
When you are positioned on a particular record in a recordset you can extract data using the fields() method.
for i in range(rs.fields.count): name = rs.fields(i).name value = rs.fields(i).value
The fields() method can also take the field name as an argument as well as the column ordinal position.
value = rs.fields("amount").value
Filtering selected data
When you open a recordset with an SQL select statement, the data selected is only that which matches the where condition of the select statement. If you open a table with noSQL e.g.
rs = db.openRecordSet("customers")
You can filter the records that are returned using the data restriction methods.
noSQL keyed data access
When opening a table in noSQL mode, you can lookup records by keys.
rs = db.openRecordSet("customers") rs.index = "id" rs.seek("12345") if rs.nomatch(): # key was not found.
Adding new records
You can add new blank records to a recordset using the addNew() method.
Note that after executing addNew() the record is not written until the update() method is called. This allows you to update the fields of the blank record prior to it being committed into the table.
You can update records in a recordset using the update() method.
rs = db.openRecordSet("customers") rs.index = "id" rs.seek("12345") if !rs.nomatch(): rs.fields("amount").value = rs.fields("amount").value + 1000 rs.update()
You can delete records in a recordset using the delete() method.
rs = db.openRecordSet("customers") rs.index = "id" rs.seek("12345") if !rs.noMatch(): rs.delete()
Closing a Recordset
You close a recordset using the close() method.