Reading Result Sets

cTDS supports reading multiple result sets generated by k_ctds.Cursor.execute() or k_ctds.Cursor.callproc().

Due to the design of the TDS protocol, it is recommended to read all rows of all result set(s) as soon as possible to allow the database server to reclaim resources associated with the result set(s).

Fetching Rows

Rows from the current result set can be read using any of k_ctds.Cursor.fetchone(), k_ctds.Cursor.fetchmany(), or k_ctds.Cursor.fetchall() methods. cTDS will cache all retrieved raw row data. However, to save memory, it is only converted to Python objects when first accessed from the Python client. This is done to minimize memory overhead when processing large result sets. Columns for the current resultset can be retrieved using the k_ctds.Cursor.description property.

import k_ctds
with k_ctds.connect(*args, **kwargs) as connection:
    with connection.cursor() as cursor:
        cursor.callproc('GetSomeResults', (1,))
        rows = cursor.fetchall()

        # Get column names.
        columns = [column.name for column in cursor.description]

# Process the rows after releasing the connection
print(columns)
for row in rows:
    # Do stuff with the rows.
    print(tuple(row))

The row list returned from k_ctds.Cursor.fetchmany(), or k_ctds.Cursor.fetchall() implements the Python sequence protocol and therefore supports indexing. For example,

import k_ctds
with k_ctds.connect(*args, **kwargs) as connection:
    with connection.cursor() as cursor:
        cursor.callproc('GetSomeResults', (1,))
        rows = cursor.fetchall()

if len(rows) > 5:
    # Print the first column of row 5.
    print(rows[5][0])

Note

Unless a result set contains a large number of rows, it is typically recommended to use k_ctds.Cursor.fetchall() to retrieve all the rows of a result. Only when result sets are sufficiently large as to make caching them a large memory burden is it recommended to use k_ctds.Cursor.fetchone() or k_ctds.Cursor.fetchmany().

Reading Columns

cTDS rows support referencing column values multiple ways: you can index a row by either a column number or a column name, use a column name as an attribute of the row, or build a dictionary mapping column names to values.

import k_ctds
with k_ctds.connect(*args, **kwargs) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            '''
            SELECT
                'unnamed',
                2 AS Column2,
                'Three' AS Column3
            '''
        )
        rows = cursor.fetchall()

for row in rows:
    # index
    assert row[0] == 'unnamed'

    # attribute
    assert row.Column2 == 2

    # mapping
    assert row['Column3'] == 'Three'

    # dict - note that the column number is used as the key
    # for any unnamed columns
    assert row.dict() == {
        0: 'unnamed',
        'Column2': '2',
        'Column3': 'Three',
    }

String Representation

repr(row) displays column name/value pairs, making rows easy to inspect in the REPL or in log output:

>>> repr(row)
"<k_ctds.Row(Column2=2, Column3='Three')>"

Unnamed columns show only their value:

>>> cursor.execute("SELECT 42, 'hello' AS greeting")
>>> row = cursor.fetchone()
>>> repr(row)
"<k_ctds.Row(42, greeting='hello')>"

Added in version 2.1.0.

Advancing the Result Set

The result set can be advanced using the k_ctds.Cursor.nextset() method. New operations using k_ctds.Cursor.execute() or k_ctds.Cursor.callproc() will discard any unread result sets.

Note

Previous result sets cannot be retrieved once the cursor has been advanced past them.