Connection

class k_ctds.Connection

A connection to the database server.

PEP 0249#connection-objects

__enter__()

Enter the connection’s runtime context. On exit, the connection is closed automatically.

Returns:

The connection object.

Return type:

k_ctds.Connection

__exit__(exc_type, exc_val, exc_tb)

Exit the connection’s runtime context, closing the connection. If no error occurred, any pending transaction will be committed prior to closing the connection. If an error occurred, the transaction will be implicitly rolled back when the connection is closed.

Parameters:

  • exc_type (type) – The exception type, if an exception is raised in the context, otherwise None.

  • exc_val (Exception) – The exception value, if an exception is raised in the context, otherwise None.

  • exc_tb (object) – The exception traceback, if an exception is raised in the context, otherwise None.

Returns:

None

__repr__()

Return repr(self).

autocommit

Auto-commit transactions after k_ctds.Cursor.execute(), k_ctds.Cursor.executemany(), and k_ctds.Cursor.callproc(). If False, operations must be committed explicitly using commit().

Return type:

bool

bulk_insert(table, rows, batch_size=None, tablock=False, auto_encode=False)

Bulk insert rows into a given table. This method utilizes the BULK INSERT functionality of SQL Server to efficiently insert large amounts of data into a table. By default, rows are not validated until all rows have been processed.

An optional batch size may be specified to validate the inserted rows after batch_size rows have been copied to server.

Parameters:

  • table (str) – The table in which to insert the rows.

  • rows (typeiter) – An iterable of data rows. Data rows are Python sequence objects. Each item in the data row is inserted into the table in sequential order. Version 1.9 supports passing rows as dict. Keys must map to column names and must exist for all non-NULL columns.

  • batch_size (int) – An optional batch size.

  • tablock (bool) – Should the TABLOCK hint be passed?

  • auto_encode – Controls automatic encoding of Python str values based on the target column’s collation. When True, column metadata is queried from INFORMATION_SCHEMA.COLUMNS before the insert begins. Alternatively, pass a pre-computed codecs object from column_codecs() to skip the metadata query on each call. This is useful when calling bulk_insert repeatedly for the same table. str values destined for NVARCHAR/NCHAR/NTEXT columns are encoded to UTF-16LE. str values destined for VARCHAR/CHAR/TEXT columns are encoded to the column’s collation code page. .. versionadded:: 2.0.0

Returns:

The number of rows saved to the table.

Return type:

int

close()

Close the connection now. Pending transactions will be rolled back. Subsequent calls to this object or any k_ctds.Cursor objects it created will raise k_ctds.InterfaceError.

PEP 0249#Connection.close

column_codecs(table)

Query column encoding information for a table.

Returns a pre-computed codecs object that can be passed to bulk_insert() via the auto_encode parameter to avoid repeated INFORMATION_SCHEMA queries when inserting multiple batches into the same table.

codecs = connection.column_codecs('dbo.MyTable')
for chunk in chunks:
    connection.bulk_insert('dbo.MyTable', chunk, auto_encode=codecs)
Parameters:

table (str) – The table name (may be schema- or catalog-qualified).

Returns:

A codecs object for use with bulk_insert().

Raises:

ValueError – If the table does not exist or is inaccessible.

Added in version 2.0.0.

commit()

Commit any pending transaction to the database.

PEP 0249#commit

cursor()

Return a new k_ctds.Cursor object using the connection.

Note

k_ctds.Cursor.close() should be called when the returned cursor is no longer required.

Warning

Only one k_ctds.Cursor object should be used per connection. The last command executed on any cursor associated with a connection will overwrite any previous results from all other cursors.

PEP 0249#cursor

Returns:

A new Cursor object.

Return type:

k_ctds.Cursor

database

The current database or None if the connection is closed.

Return type:

str

messages

A list of any informational messages received from the last k_ctds.Cursor.execute(), k_ctds.Cursor.executemany(), or k_ctds.Cursor.callproc() call. For example, this will include messages produced by the T-SQL PRINT and RAISERROR statements. Messages are preserved until the next call to any of the above methods. None is returned if the connection is closed.

PEP 0249#connection-messages

Added in version 1.4.

Return type:

list(dict)

rollback()

Rollback any pending transaction to the database.

PEP 0249#rollback

spid

The SQL Server Session Process ID (SPID) for the connection or None if the connection is closed.

Return type:

int

tds_version

The TDS version in use for the connection or None if the connection is closed.

Return type:

str

timeout

The connection timeout, in seconds, or None if the connection is closed.

Note

Setting the timeout requires FreeTDS version 1.00 or later.

Raises:

k_ctds.NotSupportedError – cTDS was compiled against a version of FreeTDS which does not support setting the timeout on a connection.

Return type:

int

use(database)

Set the current database.

Parameters:

database (str) – The database.

Returns:

None

String Representation

repr(connection) returns a human-readable summary useful for debugging:

>>> conn = k_ctds.connect('localhost', user='sa', password='secret')
>>> repr(conn)
"<k_ctds.Connection database='master' spid=54>"
>>> conn.close()
>>> repr(conn)
'<k_ctds.Connection (closed)>'

Added in version 2.1.0.