Executing SQL Statements

cTDS implements both the k_ctds.Cursor.execute() and k_ctds.Cursor.executemany() methods for executing SQL statements. Both are implemented using the sp_executesql SQL Server stored procedure. This allows optimizations when running batches using k_ctds.Cursor.executemany().

Note

sp_executesql support requires FreeTDS 1.0 or later, which is the minimum version supported by k-cTDS.

Passing Parameters

Parameters may be passed to the k_ctds.Cursor.execute() and k_ctds.Cursor.executemany() methods using the numeric parameter style as defined in PEP 0249#paramstyle.

Note

Passing parameters using the numeric paramstyle assumes an initial index of 0.

cursor.execute(
    'SELECT * FROM MyTable WHERE Id = :0 AND OtherId = :1',
    (1234, 5678)
)

cursor.executemany(
    '''
    INSERT (Id, OtherId, Name, Birthday) INTO MyTable
    VALUES (:0, :1, :2, :3)
    ''',
    (
        (1, 2, 'John Doe', datetime.date(2001, 1, 1)),
        (2000, 22, 'Jane Doe', datetime.date(1974, 12, 11)),
    )
)

Parameter Types

Parameter SQL types are inferred from the Python object type. If desired, the SQL type can be explicitly specified using a type wrapper class. For example, this is necessary when passing None for a BINARY column.

cursor.execute(
    '''
    INSERT (Id, BinaryValue) INTO MyTable
    VALUES (:0, :1)
    ''',
    (
        (1, cursor.SqlBinary(None)),
    )
)

Note

Timezone-aware datetime.datetime objects are automatically mapped to the SQL DATETIMEOFFSET type when using TDS 7.3+. Naive datetimes continue to map to DATETIME/DATETIME2.

Limitations

Due to the implementation of k_ctds.Cursor.execute() and k_ctds.Cursor.executemany(), any SQL code which defines parameters cannot be used with execute parameters. For example, the following is not supported:

# Parameters passed from python are not supported with SQL '@'
# parameters.
cursor.execute(
    '''
    CREATE PROCEDURE Increment
        @value INT OUTPUT
    AS
        SET @value = @value + :0;
    ''',
    (1,)
)

Warning

Currently FreeTDS does not support passing empty string parameters. Empty strings are converted to NULL values internally before being transmitted to the database.