The Cursor object

This documentation section outlines different attributes, methods, and behaviors of the crate.client.cursor.Cursor object.

The example code uses ClientMocked and set_next_response for demonstration purposes, so they don’t need a real database connection.

Table of Contents

Introduction

This section sets up a cursor object, inspects some of its attributes, and sets up the response for subsequent cursor operations.

>>> from crate.client import connect
>>> from crate.client.converter import DefaultTypeConverter
>>> from crate.client.cursor import Cursor
>>> from crate.testing.util import ClientMocked
>>> connection = connect(client=ClientMocked())
>>> cursor = connection.cursor()

The rowcount and duration attributes are -1, in case no execute() has been performed on the cursor yet.

>>> cursor.rowcount
-1
>>> cursor.duration
-1

Define the response of the mocked connection client. It will be returned on request without needing to execute an SQL statement.

>>> connection.client.set_next_response({
...     "rows":[ [ "North West Ripple", 1 ], [ "Arkintoofle Minor", 3 ], [ "Alpha Centauri", 3 ] ],
...     "cols":[ "name", "position" ],
...     "rowcount":3,
...     "duration":123
... })

fetchone()

Calling fetchone() on the cursor object the first time after an execute returns the first row:

>>> cursor.execute('')
>>> cursor.fetchone()
['North West Ripple', 1]

Each call to fetchone() increments the cursor and returns the next row:

>>> cursor.fetchone()
['Arkintoofle Minor', 3]

One more iteration:

>>> cursor.next()
['Alpha Centauri', 3]

The iteration is stopped after the last row is returned. A further call to fetchone() returns an empty result:

>>> cursor.fetchone()

Using fetchone() on a cursor before issuing a database statement results in an error:

>>> new_cursor = connection.cursor()
>>> new_cursor.fetchone()
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: No result available. execute() or executemany() must be called first.

fetchmany()

fetchmany() takes an argument which specifies the number of rows we want to fetch:

>>> cursor.execute('')
>>> cursor.fetchmany(2)
[['North West Ripple', 1], ['Arkintoofle Minor', 3]]

If the specified number of rows not being available, fewer rows may returned:

>>> cursor.fetchmany(2)
[['Alpha Centauri', 3]]
>>> cursor.execute('')

If no number of rows are specified it defaults to the current cursor.arraysize:

>>> cursor.arraysize
1
>>> cursor.fetchmany()
[['North West Ripple', 1]]
>>> cursor.execute('')
>>> cursor.arraysize = 2
>>> cursor.fetchmany()
[['North West Ripple', 1], ['Arkintoofle Minor', 3]]

If zero number of rows are specified, all rows left are returned:

>>> cursor.fetchmany(0)
[['Alpha Centauri', 3]]

fetchall()

fetchall() fetches all (remaining) rows of a query result:

>>> cursor.execute('')
>>> cursor.fetchall()
[['North West Ripple', 1], ['Arkintoofle Minor', 3], ['Alpha Centauri', 3]]

Since all data was fetched ‘None’ is returned by fetchone():

>>> cursor.fetchone()

And each other call returns an empty sequence:

>>> cursor.fetchmany(2)
[]
>>> cursor.fetchmany()
[]
>>> cursor.fetchall()
[]

iteration

The cursor supports the iterator interface and can be iterated upon:

>>> cursor.execute('')
>>> [row for row in cursor]
[['North West Ripple', 1], ['Arkintoofle Minor', 3], ['Alpha Centauri', 3]]

When no other call to execute has been done, it will raise StopIteration on subsequent iterations:

>>> next(cursor)
Traceback (most recent call last):
...
StopIteration
>>> cursor.execute('')
>>> for row in cursor:
...     row
['North West Ripple', 1]
['Arkintoofle Minor', 3]
['Alpha Centauri', 3]

Iterating over a new cursor without results will immediately raise a ProgrammingError:

>>> new_cursor = connection.cursor()
>>> next(new_cursor)
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: No result available. execute() or executemany() must be called first.

description

>>> cursor.description
(('name', None, None, None, None, None, None), ('position', None, None, None, None, None, None))

rowcount

The rowcount property specifies the number of rows that the last execute() produced:

>>> cursor.execute('')
>>> cursor.rowcount
3

The attribute is -1, in case the cursor has been closed:

>>> cursor.close()
>>> cursor.rowcount
-1

If the last response does not contain the rowcount attribute, -1 is returned:

>>> cursor = connection.cursor()
>>> connection.client.set_next_response({
...     "rows":[],
...     "cols":[],
...     "duration":123
... })
>>> cursor.execute('')
>>> cursor.rowcount
-1
>>> connection.client.set_next_response({
...     "rows":[ [ "North West Ripple", 1 ], [ "Arkintoofle Minor", 3 ], [ "Alpha Centauri", 3 ] ],
...     "cols":[ "name", "position" ],
...     "rowcount":3,
...     "duration":123
... })

duration

The duration property specifies the server-side duration in milliseconds of the last query issued by execute():

>>> cursor = connection.cursor()
>>> cursor.execute('')
>>> cursor.duration
123

The attribute is -1, in case the cursor has been closed:

>>> cursor.close()
>>> cursor.duration
-1
>>> connection.client.set_next_response({
...     "results": [
...         {
...             "rowcount": 3
...         },
...         {
...             "rowcount": 2
...         }
...     ],
...     "duration":123,
...     "cols":[ "name", "position" ],
... })

executemany()

executemany() allows to execute a single sql statement against a sequence of parameters:

>>> cursor = connection.cursor()
>>> cursor.executemany('', (1,2,3))
[{'rowcount': 3}, {'rowcount': 2}]
>>> cursor.rowcount
5
>>> cursor.duration
123

executemany() is not intended to be used with statements returning result sets. The result will always be empty:

>>> cursor.fetchall()
[]

For completeness’ sake the cursor description is updated nonetheless:

>>> [ desc[0] for desc in cursor.description ]
['name', 'position']
>>> connection.client.set_next_response({
...     "rows":[ [ "North West Ripple", 1 ], [ "Arkintoofle Minor", 3 ], [ "Alpha Centauri", 3 ] ],
...     "cols":[ "name", "position" ],
...     "rowcount":3,
...     "duration":123
... })

close()

After closing a cursor the connection will be unusable. If any operation is attempted with the closed connection an ProgrammingError exception will be raised:

>>> cursor = connection.cursor()
>>> cursor.execute('')
>>> cursor.fetchone()
['North West Ripple', 1]
>>> cursor.close()
>>> cursor.fetchone()
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: Cursor closed
>>> cursor.fetchmany()
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: Cursor closed
>>> cursor.fetchall()
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: Cursor closed
>>> cursor.next()
Traceback (most recent call last):
...
crate.client.exceptions.ProgrammingError: Cursor closed

Python data type conversion

The cursor object can optionally convert database types to native Python data types. Currently, this is implemented for the CrateDB data types IP and TIMESTAMP on behalf of the DefaultTypeConverter.

>>> cursor = connection.cursor(converter=DefaultTypeConverter())
>>> connection.client.set_next_response({
...     "col_types": [4, 5, 11],
...     "rows":[ [ "foo", "10.10.10.1", 1658167836758 ] ],
...     "cols":[ "name", "address", "timestamp" ],
...     "rowcount":1,
...     "duration":123
... })
>>> cursor.execute('')
>>> cursor.fetchone()
['foo', IPv4Address('10.10.10.1'), datetime.datetime(2022, 7, 18, 18, 10, 36, 758000, tzinfo=datetime.timezone.utc)]

Custom data type conversion

By providing a custom converter instance, you can define your own data type conversions. For investigating the list of available data types, please either inspect the DataType enum, or the documentation about the list of available CrateDB data type identifiers for the HTTP interface.

To create a simple converter for converging CrateDB’s BIT type to Python’s int type.

>>> from crate.client.converter import Converter, DataType
>>> converter = Converter({DataType.BIT: lambda value: int(value[2:-1], 2)})
>>> cursor = connection.cursor(converter=converter)

Proof that the converter works correctly, B\'0110\' should be converted to 6. CrateDB’s BIT data type has the numeric identifier 25.

>>> connection.client.set_next_response({
...     "col_types": [25],
...     "rows":[ [ "B'0110'" ] ],
...     "cols":[ "value" ],
...     "rowcount":1,
...     "duration":123
... })
>>> cursor.execute('')
>>> cursor.fetchone()
[6]

TIMESTAMP conversion with time zone

Based on the data type converter functionality, the driver offers a convenient interface to make it return datetime objects using the desired time zone.

For your reference, in the following examples, epoch 1658167836758 is Mon, 18 Jul 2022 18:10:36 GMT.

>>> import datetime
>>> tz_mst = datetime.timezone(datetime.timedelta(hours=7), name="MST")
>>> cursor = connection.cursor(time_zone=tz_mst)
>>> connection.client.set_next_response({
...     "col_types": [4, 11],
...     "rows":[ [ "foo", 1658167836758 ] ],
...     "cols":[ "name", "timestamp" ],
...     "rowcount":1,
...     "duration":123
... })
>>> cursor.execute('')
>>> cursor.fetchone()
['foo', datetime.datetime(2022, 7, 19, 1, 10, 36, 758000, tzinfo=datetime.timezone(datetime.timedelta(seconds=25200), 'MST'))]

For the time_zone keyword argument, different data types are supported. The available options are:

  • datetime.timezone.utc

  • datetime.timezone(datetime.timedelta(hours=7), name="MST")

  • pytz.timezone("Australia/Sydney")

  • zoneinfo.ZoneInfo("Australia/Sydney")

  • +0530 (UTC offset in string format)

Let’s exercise all of them:

>>> cursor.time_zone = datetime.timezone.utc
>>> cursor.execute('')
>>> cursor.fetchone()
['foo', datetime.datetime(2022, 7, 18, 18, 10, 36, 758000, tzinfo=datetime.timezone.utc)]
>>> import pytz
>>> cursor.time_zone = pytz.timezone("Australia/Sydney")
>>> cursor.execute('')
>>> cursor.fetchone()
['foo', datetime.datetime(2022, 7, 19, 4, 10, 36, 758000, tzinfo=<DstTzInfo 'Australia/Sydney' AEST+10:00:00 STD>)]
>>> try:
...     import zoneinfo
... except ImportError:
...     from backports import zoneinfo
>>> cursor.time_zone = zoneinfo.ZoneInfo("Australia/Sydney")
>>> cursor.execute('')
>>> record = cursor.fetchone()
>>> record
['foo', datetime.datetime(2022, 7, 19, 4, 10, 36, 758000, ...zoneinfo.ZoneInfo(key='Australia/Sydney'))]
>>> record[1].tzname()
'AEST'
>>> cursor.time_zone = "+0530"
>>> cursor.execute('')
>>> cursor.fetchone()
['foo', datetime.datetime(2022, 7, 18, 23, 40, 36, 758000, tzinfo=datetime.timezone(datetime.timedelta(seconds=19800), '+0530'))]