Working with data in Python

From Lianjapedia
Jump to: navigation, search

Overview

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.

Python, PHP and JavaScript are seamlessly integrated on top of this database engine, allowing you to build custom UI sections and custom UI gadgets in Visual FoxPro, JavaScript, PHP or Python and make full use of the power of this high performance database engine with local cursors and complete SQL and noSQL database support.

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")

Recordset data navigation

After we have opened a recordset we can navigate through the data using any of the recordset data navigation methods of the Recordset class.

  • moveFirst()
  • moveNext()
  • movePrevious()
  • moveLast()

For example to position on the first record in a recordset use the moveFirst() method.

rs.moveFirst()

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.

  • findFirst(condition)
  • findNext(condition)
  • findPrevious(condition)
  • findLast(condition)

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.

rs.addNew()

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.

Updating records

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.edit()
   rs.fields("amount").value = rs.fields("amount").value + 1000
   rs.Update()

Deleting records

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.

rs.close()