# Using Sequences in Python

As explained in the User's Guide page, a sequence is an unbounded array of eXtremeDB-supported scalar data elements. The Python SequenceIterator class is used to manage database fields of type sequence. As sequences are effectively vectors of values, they are accessed through iterators. Where the C type `mco_seq_iterator_h` is used in C applications, the Python class SequenceIterator serves the equivalent role in Python applications. The SequenceIterator class also provides a powerful set of Analytics Methods for performing mathematical and statistical operations on sequences.

## Inserting and Updating Sequences in Python

Normally sequence data is inserted using the SequenceIterator `append()` method. For example consider the following schema definition:

        #define uint4 unsigned<4>

        class Quote {
            char<16>            symbol;
            sequence<uint4 asc> day;
            sequence<float>     price;

            tree<symbol> by_symbol;
        };

With this class definition, the Quote sequence fields `day` and `price` can be populated with code like the following:

        con.startTransaction()
        tick = generate_random_quote()
        quote = con.new("Quote")
        quote.symbol = tick.symbol
        quote.day.append(tick.day)
        quote.price.append(tick.price)
        con.commit()

It may sometimes be necessary to insert values into an ordered time series. Values can be inserted into an existing sequence using the `insert()` method. For example, the following code snippet searches for the Quote object with symbol "`IBM`", then inserts `day` and `price` values:

        con.startTransaction()
        for quote in cursor:
            dayit = quote.day.search(20130101, exdb.SeqIteratorBoundary.MCO_SEQ_BOUNDARY_INCLUSIVE,
                            20130101, exdb.SeqIteratorBoundary.MCO_SEQ_BOUNDARY_INCLUSIVE)
            dayit = append(20130102)
            quote.day.next()
            quote.day.insert(dayit)

        con.commit()

## Object method seq_append(args)

Since eXtremeDB version 8.4.1806, the `Object` type supports the `seq_append()` method

This method appends records to the object’s sequence fields.

The args parameter may be one of the following:

- A tuple.

  The tuple is treated as a single record and appended to the object’s sequences.

- Multiple arguments.

  The arguments are treated as a tuple and appended as a single record to the object’s sequences.

- An object.

  The python object’s fields are used to append values to the database object’s sequences.

- A list.

  The list is treated as a collection of records and a vectored append is performed for all elements.

  Each list element may be:

  - A tuple.
  - a dictionary
  - An object.

> ### Rules for fields matching.

> Consider an object with the following structure:

>         class Obj {
>             int key;
>             sequence<int> s1;
>             sequence<float> s2;
>             sequence<int asc> s3;
>         };

> Although it is technically possible for s1, s2, and s3 to be unrelated and of different lengths, in practice they usually correspond to one another. That is, they typically have the same number of elements, and elements at the same index position are logically related.

> In this case, the elements at position i in s1, s2, and s3 can be viewed as forming a logical tuple:

>         (i, s1i, s2i, s3i)

> This tuple is associated with the parent Obj record identified by key.

> If this object is translated into a relational model, it would conceptually correspond to two tables:

>         create table Obj(key int);
>         create table Obj_rec(int i, int s1, float s2, int s3, key_Obj int);

> In real life there is no class Obj_rec, it is shown only to clarify the concept. The field i is not stored in reality as well, its shown only to demonstrate an order of records in Obj_rec.

> The method exdb.Object.seq_append(args) uses args to populate the sequence fields of Obj as if each input were an Obj_rec record. Tuple arguments (as well as class instances) are treated as logical Obj_rec records.

> ### Tuple-Based Field Mapping

> A simple positional mapping algorithm is used:

> 1. The sequence fields of the target class (Obj) are enumerated in declaration order (three fields in this example). Of course database dictionary must not be sorted to this algorythm to work correctly.
> 2. Tuple elements are appended sequentially to the corresponding sequence fields.

> For example, inserting the tuple (1, 2, 3) results in:

> - 1 appended to s1
> - 2 appended to s2
> - 3 appended to s3

> If a tuple contains more elements than there are sequence fields, the extra elements are ignored.

> If a tuple contains fewer elements than required, an error is raised.

> ### Object (dict) appending.

> Objects and dictionaries are Python types whose fields can be accessed by name. In this case, field names (strings) are matched against the sequence field names of the target object.

> For example, to append to an object of class Obj:

>         obj.seq_append({“s1”: 1, “s2”: 2.0, “s3”: 3})

> Or, using a class instance:

>         class A:
>             def __init__(self):
>                 self.s1 = 1
>                 self.s2 = 2.0
>                 self.s3 = 33

>         obj.seq_append(A())

> Because object and dictionary appends rely on string-based field matching, they are generally slower than pure tuple-based appends.

> ### None beaviour.

> If an input tuple contains None, it is treated as NULL, provided that the target sequence field is nullable.

## Sequence Interface to NumPy

If Python has the numPy module installed, it is possible to load and save data from sequences directly to a numPy array. (Note that due to the nature of sequence data in eXtremeDB, only 1-dimensional numPy arrays are supported.)

### Appending a numPy Array to a Sequence.

The SequenceIterator `append()` method can accept a `numpy.ndarray` as an argument. For example:

        >>> ac = numpy.arange(10,  dtype='uint32')
        >>> ac
        array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9], dtype=uint32)
        >>> quote.volume.append(ac)

Both numPy and eXtremeDB sequences use C-Style binary array representation for efficiency. So the data types for sequence and `nparray` must match. This is why `dtype=uint32` is the argument specified.

The append() method recognizes the following argument types:

- A Python `object`:

>         o.price.append(1)

> This will simply append a value to the end of the sequence. (This is the slowest way to update a sequence.)

- A Python `list`:

>         o.price.append([1,2,3,4,5])

> Here the 5 values in the list are appended to the sequence.

- A Python `tuple`:

>         o.price.append( (1,2,3,4,5) )

> Again the 5 values in the list are appended to the sequence.

- An `array.array`:

> The Python standard library supports a homogenous typed `array` with elements of the same size. In fact, this is the wrapper over C-style array, and it can be used to fill a sequence with data as follows:

>         import array
>         a = array.array('f', [1,2,3,4])
>         o.price.append(a)

> From the database point of view, this is the fastest way of inserting data, but keep in mind the following considerations:

> > 1. The data type for `array.array` must exactly match the data type for the database sequence. Type mappings between eXtremeDB database types, Python and C types are shown in the following table:

> > Sequence type | array.array typecode | C type
> > --- | --- | ---
> > signed<1> | 'b' | signed char
> > unsigned<1> | 'B' | unsigned char
> > signed<2> | 'h' | signed short
> > unsigned<2> | 'H' | unsigned short
> > signed<4> | 'i' | signed int
> > unsigned<4> | 'I' | unsigned int
> > signed<8> | 'l' | signed long long
> > unsigned<8> | 'L' | unsigned long long
> > float | 'f' | float
> > double | 'd' | double
> > char<n> | - | -

> > (Note, that the char<n> type is not supported in `array.array`.)

> > 2. Speed. Although this is the fastest way to insert data into a database, the creation of `array.array` incurs its own overhead. Tests show that the longer the array being used to insert, the greater the improvement in performance. (It's feasible to use `array.array` with data chunks of much more that a 100 items.) Also, the Python interpreter is quite slow filling `array.array` with values. A real advantage can be achieved using other methods for filling that array, like: `array.fromfile(f, n)`or `array.fromstring(s)`. (Please consult the Python documentation for these methods.)

### Retrieving Sequence Data to an nparray

If a sequence is of moderate size, it is possible to represent the entire sequence as a `numPy array` using the SequenceIterator method `tonparray()`. Its signature is:

        SequenceIterator.tonparray([size_hint])

For a materialized `sequence`, the eXtremeDB runtime knows exactly what the sequence size is, so the required memory for the resulting array is allocated immediately. For a non-materialized sequence, it is not possible to know in advance the size of the resulting array. In this case, memory is allocated by chunks as the temporary result is built. This is not very efficient, so if the application knows the expected array size, this can be passed to `tonparray()` as the `size_hint` argument.

## Sequences in Python Embedded SQL Applications

The eXtremeDB Python wrapper extends the Python DB API Specification by allowing operations with sequences. A sequence may appear as a result of a `select` statement, or, as a parameter in `insert` or `update` statements. For example, the following statement could be sent to the SQL engine:

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

The result of this query can be as follows:

        row = cursor.fetchone()
        print "Row is:", row
        Row is: (<exdb.SequenceIterator object at 0x1008f6610>, <exdb.SequenceIterator object at 0x1008f7610>,
            <exdb.SequenceIterator object at 0x1008f8610>)

Note that the resulting row contains SequenceIterator objects as row values. Regular iterator methods can be applied to this iterator. Note that, except in the case of a query result, the returned iterator will be “not materialized”.

To insert data into a sequence, pass data into the SQL statement as a parameter using an `array.array` . For example:

        cursor.execute('INSERT INTO Quote(symbol, stamp, open, close, high, low, volume) VALUES (?,?,?,?,?,?,?)',
            ('AAA', array.array('I', timestamps), array.array('f', opens), array.array('f', closes),
            array.array('f', highs), array.array('f', lows), array.array('I', volumes)))

Note that `array.array` is strictly typed, so its type must match the sequence column type. If it does not match, the resulting behavior is unpredictable. Passing a Python `list` or `tuple` as a parameter value is not supported. Please consult the mappings table above for the correspondences between sequence types and `array.array` types.