==================
Crate Client Usage
==================

Connect to a Database
=====================

Before we can start we have to import the crate client::

    >>> from crate import client

The client provides a ``connect()`` function which is used to establish a connection, the first
argument is threat as the server to connect to::

    >>> connection = client.connect(crate_host)

Servers can also be passed by named argument::

    >>> connection = client.connect(servers=crate_host)

It's also possible to make a connection to multiple hosts. If a server does not respond,
the request is automatically routed to the next server::

    >>> invalid_host = 'not_responding_host:9200'
    >>> connection = client.connect([crate_host, invalid_host])

If no ``servers`` are given, the default one ``127.0.0.1:9200`` is used::

    >>> connection = client.connect()
    >>> connection.client._active_servers
    ['127.0.0.1:9200']

It's possible to define a default timeout value in seconds for all servers using the optional
parameter ``timeout``::

    >>> connection = client.connect([crate_host, invalid_host], timeout=5)

It's possible to define an own connection client class and pass its instance to the
``connect`` method::

    >>> class MyConnectionClient:
    ...     def __init__(self):
    ...         pass
    ...     def sql(self, stmt=None):
    ...         pass
    >>> connection = client.connect([crate_host, invalid_host], client=MyConnectionClient())

Create connection with default client again::

    >>> connection = client.connect(crate_host)

Selecting Some Data
===================

To execute a command we use the cursor provided by the connection::

    >>> cursor = connection.cursor()
    >>> cursor.execute("SELECT name FROM locations")

To retrieve a row we can use one of the cursor's fetch functions (described below).

fetchone()
----------

``fetchone()`` with each call returns the next row from the results::

    >>> result = cursor.fetchone()
    >>> pprint(result)
    [u'Algol']

If no more data is available, an empty result is returned::

    >>> while cursor.fetchone():
    ...     pass
    >>> cursor.fetchone()

fetchmany()
-----------

``fetch_many()`` returns a list of all remaining rows, containing no more than the specified
size of rows::

    >>> cursor.execute("SELECT name FROM locations")
    >>> result = cursor.fetchmany(2)
    >>> pprint(result)
    [[u'Algol'], [u'Folfanga']]

If a size is not given, the cursor's arraysize, which defaults to '1', determines the number
of rows to be fetched::

    >>> cursor.fetchmany()
    [[u'Aldebaran']]

It's also possible to change the cursors arraysize to an other value::

    >>> cursor.arraysize = 3
    >>> cursor.fetchmany()
    [[u'Argabuthon'], [u'Bartledan'], [u'Galactic Sector QQ7 Active J Gamma']]

fetchall()
----------

``fetchall()`` returns a list of all remaining rows:: 
    
    >>> cursor.execute("SELECT name FROM locations")
    >>> result = cursor.fetchall()
    >>> pprint(result)
    [[u'Algol'],
     [u'Folfanga'],
     [u'Aldebaran'],
     [u'Argabuthon'],
     [u'Bartledan'],
     [u'Galactic Sector QQ7 Active J Gamma'],
     [u'Allosimanius Syneca'],
     [u'Arkintoofle Minor'],
     [u'Outer Eastern Rim'],
     [u'Altair']]

Cursor Description
==================

The ``description`` property of the cursor returns a sequence of 7-item sequences containing the
column name as first parameter. Just the name field is supported, all other fields are 'None'::

    >>> cursor.execute("SELECT * FROM locations")
    >>> result = cursor.fetchone()
    >>> pprint(result)
    [u'2013-07-16',
     u'Algol is the home of the Algolian Suntiger, ...',
     u'Star System',
     u'Algol',
     2]

    >>> result = cursor.description
    >>> pprint(result)
    ((u'date', None, None, None, None, None, None),
     (u'description', None, None, None, None, None, None),
     (u'kind', None, None, None, None, None, None),
     (u'name', None, None, None, None, None, None),
     (u'position', None, None, None, None, None, None))

Closing the Cursor
==================

The following command closes the cursor::

    >>> cursor.close()

If a cursor is closed, it will be unusable from this point forward.
If any operation is attempted to a closed cursor an ``ProgrammingError`` will be raised.

    >>> cursor.execute("SELECT * FROM locations")
    Traceback (most recent call last):
    ...
    ProgrammingError: Cursor closed

Closing the Connection
======================

The following command closes the connection::

    >>> connection.close()

If a connection is closed, it will be unusable from this point forward.
If any operation using the connection is attempted to a closed connection an ``ProgrammingError``
will be raised::

    >>> cursor.execute("SELECT * FROM locations")
    Traceback (most recent call last):
    ...
    ProgrammingError: Connection closed

    >>> cursor = connection.cursor() 
    Traceback (most recent call last):
    ...
    ProgrammingError: Connection closed




