Python-TDS

Warnings

• breaking When connecting to SQL Server 2022 or newer with TDS 8.0 and `Encrypt=strict`, the `TrustServerCertificate` option cannot be set to `true`. Instead, `HostNameInCertificate` must be used for certificate validation.
  Fix: Ensure your `python-tds` client is up-to-date. Do not set `TrustServerCertificate` to `true`. Use `HostNameInCertificate` parameter in `pytds.connect` to specify the trusted certificate server name and ensure proper certificate validation.
• gotcha Inserting binary data (e.g., into `VARBINARY` or `IMAGE` columns) requires wrapping the Python bytes object with `pytds.Binary()` to ensure correct type inference by the driver.
  Fix: For binary values, use `pytds.Binary(your_bytes_object)`. Example: `cursor.execute('INSERT INTO MyTable (BinaryColumn) VALUES (%s)', (pytds.Binary(data),))`
• deprecated The NTLM authentication mechanism provided directly through `pytds.login` is deprecated due to its underlying dependency on the `ntlm-auth` package, which is also deprecated.
  Fix: It is recommended to use `SpnegoAuth` for NTLM/Kerberos authentication instead of the deprecated `pytds.login` approach.
• gotcha A common 'Permission denied' error occurs during `pip install python-tds` if the user lacks write privileges to system Python directories.
  Fix: Use `pip install --user python-tds` to install into the user's home directory, or if installing system-wide, use `sudo pip install python-tds` (with caution).
• gotcha When connecting, ensure you correctly use the `server` and `dsn` parameters in `pytds.connect()`. `server` specifies the host, while `dsn` can include both host and instance name (e.g., `hostname\instance_name`).
  Fix: Use `server='your_host'` for default instances or when `dsn` is not required. Use `dsn='your_host\your_instance'` if connecting to a named instance, or `server='your_host', port=your_port` for specific ports.

Install

• pip install python-tds Basic Installation
• pip install python-tds pyOpenSSL bitarray kerberos With Optional Dependencies (TLS, Performance, Kerberos)

Imports

• pytds

  import pytds

  The PyPI package is 'python-tds', but the import name is 'pytds'.

Quickstart

This quickstart demonstrates how to connect to a Microsoft SQL Server database using `python-tds`, execute a simple query, and fetch results. It uses environment variables for connection details for security and flexibility. The connection and cursor objects are managed using Python's `with` statement for automatic resource cleanup.

import os
import pytds

# --- Environment Variables (Replace with your actual values or secure fetching) ---
SERVER = os.environ.get('SQL_SERVER', 'your_server.database.windows.net')
DATABASE = os.environ.get('SQL_DATABASE', 'your_database')
USER = os.environ.get('SQL_USER', 'your_username')
PASSWORD = os.environ.get('SQL_PASSWORD', 'your_password')

try:
    # Establish a connection using a context manager
    with pytds.connect(server=SERVER, database=DATABASE, user=USER, password=PASSWORD) as conn:
        print("Successfully connected to the database!")

        # Create a cursor object using a context manager
        with conn.cursor() as cursor:
            # Execute a simple query
            cursor.execute("SELECT 1 AS ConnectionTest")

            # Fetch the result
            result = cursor.fetchone()
            print(f"Query result: {result}")

            # Example: Fetch all results
            cursor.execute("SELECT 'Hello from Python-TDS' AS Message")
            all_results = cursor.fetchall()
            print(f"All results: {all_results}")

except pytds.Error as e:
    print(f"Database error occurred: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")