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 the url of the server to connect to:

>>> connection = client.connect(crate_host)

Crate is a clustered database providing high availability through replication. In order for clients to make use of this property it is recommended to specify all hosts of the cluster. This way if a server does not respond, the request is automatically routed to the next server:

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

If no servers are given, the default one http://127.0.0.1:4200 is used:

>>> connection = client.connect()
>>> connection.client._active_servers
['http://127.0.0.1:4200']

If the option error_trace is set to True, the client will print a whole traceback if a server error occurs:

>>> connection = client.connect([crate_host], error_trace=True)

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)

Inserting Data¶

Before executing any statement a cursor has to be opened to perform database operations:

>>> cursor = connection.cursor()
>>> cursor.execute("""INSERT INTO locations
... (name, date, kind, position) VALUES (?, ?, ?, ?)""",
...                ('Einstein Cross', '2007-03-11', 'Quasar', 7))

To bulk insert data you can use the executemany function:

>>> cursor.executemany("""INSERT INTO locations
... (name, date, kind, position) VALUES (?, ?, ?, ?)""",
...                [('Cloverleaf', '2007-03-11', 'Quasar', 7),
...                 ('Old Faithful', '2007-03-11', 'Quasar', 7)])
[{u'rowcount': 1}, {u'rowcount': 1}]

executemany returns a list of results for every parameter. Each result contains a rowcount. If an error occures the rowcount is -2 and the result may contain an error_message depending on the error.

Note

If you are using a crate server version older than 0.42.0 the client will execute a single sql statement for every parameter in the parameter sequence when you are using executemany. In this case, executemany doesn’t return any value. To avoid that overhead you can use execute and make use of multiple rows in the INSERT statement and provide a list of arguments with the length of number of inserted records * number of columns:

>>> cursor.execute("""INSERT INTO locations
... (name, date, kind, position) VALUES (?, ?, ?, ?), (?, ?, ?, ?)""",
...                ('Creameries', '2007-03-11', 'Quasar', 7,
...                 'Double Quasar', '2007-03-11', 'Quasar', 7))

Selecting Data¶

To perform the select operation simply execute the statement on the open cursor:

>>> cursor.execute("SELECT name FROM locations where name = ?", ('Algol',))

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 order by name")
>>> result = cursor.fetchmany(2)
>>> pprint(result)
[[u'Aldebaran'], [u'Algol']]

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'Allosimanius Syneca']]

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

>>> cursor.arraysize = 3
>>> cursor.fetchmany()
[[u'Alpha Centauri'], [u'Altair'], [u'Argabuthon']]

fetchall()¶

fetchall() returns a list of all remaining rows:

>>> cursor.execute("SELECT name FROM locations order by name")
>>> result = cursor.fetchall()
>>> pprint(result)
[['Aldebaran'],
 ['Algol'],
 ['Allosimanius Syneca'],
 ['Alpha Centauri'],
 ['Altair'],
 ['Argabuthon'],
 ['Arkintoofle Minor'],
 ['Bartledan'],
 ['Cloverleaf'],
 ['Creameries'],
 ['Double Quasar'],
 ['Einstein Cross'],
 ['Folfanga'],
 ['Galactic Sector QQ7 Active J Gamma'],
 ['Galaxy'],
 ['North West Ripple'],
 ['Old Faithful'],
 ['Outer Eastern Rim']]

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 order by name")
>>> result = cursor.fetchone()
>>> pprint(result)
[u'2013-07-16',
 None,
 u'Max Quordlepleen claims that the only thing left ...',
 None,
 u'Star System',
 u'Aldebaran',
 1]

>>> result = cursor.description
>>> pprint(result)
((u'date', None, None, None, None, None, None),
 (u'datetime', None, None, None, None, None, None),
 (u'description', None, None, None, None, None, None),
 (u'details', 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