The most common use of a cursor is probably to execute an SQL select command. The example below (which can be attached to the onLoad event of a form), displays the number of products in the Products table.
def eventFunc (form) : connect = RekallPYDBI.connect (form, "Orders") cursor = connect.cursor () cursor.execute ("select count(*) from Products", []) RekallMain.messageBox("You have " + cursor.fetchone()[0] + " products") |
The cursor.execute(....) line executes the SQL count query. The second argument is a list of values which will be substituted into the query (in this case, there aren't any); see the next example below.
For a select query, you can retrieve successive records using the fetchone() method:
def eventFunc (form) : connect = RekallPYDBI.connect (form, "Orders") cursor = connect.cursor () cursor.execute ("select Name, Price from Products") record = cursor.fetchone() while record is not None : RekallMain.messageBox("You sell %s for %s " % (record[0], record[1])) record = cursor.fetchone() |
Alternatively, the fetchall() method returns all the records at one go (or all remaining records if you have already called fetchone()):
def eventFunc (form) : connect = RekallPYDBI.connect (form, "Orders") cursor = connect.cursor () cursor.execute ("select Name, Price from Products") for record in cursor.fetchall() : RekallMain.messageBox("You sell %s for %s " % (record[0], record[1])) |
The extended example below is taken from the RekallDemo database. The orders form has some scripting which records changes to the Orders table in another table called Audit. Basically, the preInsert, preUpdate and preDelete block events record infomation about that is about to happen in some python global variables. The postSync event then invokes the code shown below, which inserts a record into Audit.
The main thing to note is the use of ? as a placeholder in the SQL query; when the query is executed, the values in the list argument cursor.execute(...) are substituted. Note that this is different to the standard Python DB interface, which uses %s as a placeholder. This example also shows the use of python exceptions.
def onBlockOrdersPostSync (block, qRow, action, key) : global auditActionText global auditClientID global auditProductID if auditActionText == None : return entered = time.strftime("%Y-%m-%d %T", time.localtime (time.time())) try : connect = RekallPYDBI.connect (block, "Orders") cursor = connect.cursor () cursor.execute \ ( "insert into Audit (OrderID, ClientID, ProductID, " + \ " Action, Entered) values (?, ?, ?, ?, ?)", [ key, auditClientID, auditProductID, auditActionText, entered ] ) except RekallPYDBI.DatabaseError, message : RekallMain.messageBox (message.args[0]) auditActionText = None |