Class SQLarray

Build the SQL table from a numpy record array.

Overrides: object.__init__

Merge another recarray with the same columns into this table.

:Arguments:
   recarray    
      numpy record array that describes the layout and initializes the
      table

:Returns:
   n           number of inserted rows
   
:Raises:
   Raises an exception if duplicate and incompatible data exist
   in the main table and the new one.

Merge an existing table in the database with the __self__ table.

Executes as ``'INSERT INTO __self__ SELECT * FROM <name>'``.
However, this method is probably used less often than the simpler :meth:`merge`.

:Arguments:
   name         name of the table in the database (must be compatible with __self__)

:Returns:
   n            number of inserted rows

Execute a simple SQL ``SELECT`` statement and returns values as new numpy rec array.

The arguments *fields* and the additional optional arguments
are simply concatenated with additional SQL statements
according to the template::

   SELECT <fields> FROM __self__ [args]

The simplest fields argument is ``"*"``.

Example:
   Create a recarray in which students with average grade less than
   3 are listed::

     result = T.SELECT("surname, subject, year, avg(grade) AS avg_grade",
                    "WHERE avg_grade < 3", "GROUP BY surname,subject",
                    "ORDER BY avg_grade,surname")

   The resulting SQL would be::

     SELECT surname, subject, year, avg(grade) AS avg_grade FROM __self__
          WHERE avg_grade < 3
          GROUP BY surname,subject
          ORDER BY avg_grade,surname

   Note how one can use aggregate functions such avg().

   The string *'__self__'* is automatically replaced with the table
   name (``T.name``); this can be used for cartesian products such as ::

      LEFT JOIN __self__ WHERE ...

.. Note:: See the documentation for :meth:`~SQLarray.sql` for more details on
          the available keyword arguments and the use of ``?`` parameter
          interpolation.

Execute a simple SQL ``SELECT`` statement and returns values as new numpy rec array.

The arguments *fields* and the additional optional arguments
are simply concatenated with additional SQL statements
according to the template::

   SELECT <fields> FROM __self__ [args]

The simplest fields argument is ``"*"``.

Example:
   Create a recarray in which students with average grade less than
   3 are listed::

     result = T.SELECT("surname, subject, year, avg(grade) AS avg_grade",
                    "WHERE avg_grade < 3", "GROUP BY surname,subject",
                    "ORDER BY avg_grade,surname")

   The resulting SQL would be::

     SELECT surname, subject, year, avg(grade) AS avg_grade FROM __self__
          WHERE avg_grade < 3
          GROUP BY surname,subject
          ORDER BY avg_grade,surname

   Note how one can use aggregate functions such avg().

   The string *'__self__'* is automatically replaced with the table
   name (``T.name``); this can be used for cartesian products such as ::

      LEFT JOIN __self__ WHERE ...

.. Note:: See the documentation for :meth:`~SQLarray.sql` for more details on
          the available keyword arguments and the use of ``?`` parameter
          interpolation.

Execute sql statement. 

:Arguments:
   SQL : string
      Full SQL command; can contain the ``?`` place holder so that values
      supplied with the ``parameters`` keyword can be interpolated using
      the ``pysqlite`` interface.
   parameters : tuple
      Parameters for ``?`` interpolation.
   asrecarray : boolean
      ``True``: return a ``numpy.recarray`` if possible;
      ``False``: return records as a list of tuples. [``True``]
   cache : boolean
      Should the results be cached? Set to ``False`` for large queries to
      avoid memory issues. Queries with ``?`` place holders are never cached.
      [``True``]
      
.. warning::
   There are **no sanity checks** applied to the SQL. 

If  possible, the  returned list  of tuples  is turned  into a
numpy record  array, otherwise the original list  of tuples is
returned.

.. warning::
   Potential BUG: if there are memory issues then it can
   happen that we just silently fall back to a tuple even
   though calling code expects a recarray; because we
   swallowed ANY exception the caller will never know

The last cachesize queries are cached (for cache=True) and are
returned directly unless the table has been modified.

.. Note:: '__self__' is substituted with the table name. See the doc
          string of the :meth:`SELECT` method for more details.

Return a new SQLarray from a SELECT selection.

This is a very useful method because it allows one to build complicated selections and essentially new tables from existing data.

Examples:

       s = selection('a > 3')
       s = selection('a > ?', (3,))
       s = selection('SELECT * FROM __self__ WHERE a > ? AND b < ?', (3, 10))