Executing SQL Statements

cTDS implements both the ctds.Cursor.execute() and 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 ctds.Cursor.executemany().

Note

Versions of FreeTDS prior to 0.92.405 can’t properly support the use of sp_executesql. cTDS still implements executemany(), however performance benefits are lost.

Passing Parameters

Parameters may be passed to the ctds.Cursor.execute() and 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)),
    )
)

Note

In ctds version 1.6.0 and later, the named paramstyle may be used instead by specifying it in ctds.connect().

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)),
    )
)

Limitations

Due to the implementation of ctds.Cursor.execute() and 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.