eXtremeSQL Cursors in Python

As defined by the DB API specification, to perform queries an empty cursor is created and then the execute() method is called on the cursor to perform the SQL query. For example:

 
    >>> cursor = conn.cursor()
    >>> cursor.execute("SELECT ikey, high, volume FROM Quote WHERE ikey = ?", (5,))
     

Parameters are sent to the SQL engine using question mark syntax (as in the example above). Actual parameter values are sent as a tuple. If any changes are performed, it is necessary to execute the commit() or rollback() operations on the connection; for example:

 
    >>> conn.commit()
     

The Python wrapper automatically creates a READ_WRITE transaction with default parameters when performing execute() without an opened transaction. If this behavior is not desired, manually open a transaction using the connection.startTransaction() method.

When a SQL statement is executed, the following methods are available for retrieving results:

cursor.fetchone() - return a single row from the result as a tuple.

cursor.fetchall() - return the whole result set as a list of tuples. (NB: If the result set is too large, it can affect performance)

cursor.fetchmany() - return N rows from a result set as a set of tuples. The number of rows to return is set by the cursor member arraysize. (Default value is 100).

The eXtremeDB sequence data type is also supported in the query result. To use sequences there are two options:

1. Use the FLATTENED keyword. This allows transposing the sequence in different result rows. For instance, consider a query like the following:

 
    >>> cursor.execute("SELECT flattened stamp, low, high 
                FROM Quote WHERE symbol = ?", ('AAA',))
     

The returned result set will be several rows, like:

 
    (1,2,3)
    (2,3,4)
    etc.
     

2. Use a sequence column in the query result. This will return a SequenceIterator object instance. This iterator is compatible with most iterator functions, except those that require a materialized sequence, i.e. a sequence that is directly bound to a database field.

(Please refer to the Sequences page for further details on the use of the sequence data type in Python.