{"id":2735,"library":"python-tds","title":"Python-TDS","description":"Python-TDS is a pure Python DBAPI driver for Microsoft SQL Server, implementing the Tabular Data Stream (TDS) protocol. This cross-platform library eliminates dependencies on ADO or FreeTDS, offering features like MARS, bulk insert, table-valued parameters, and TLS/Kerberos support. The current version is 1.17.1, with active development and consistent releases.","status":"active","version":"1.17.1","language":"en","source_language":"en","source_url":"https://github.com/denisenkom/pytds","tags":["database","mssql","sql server","tds","dbapi","microsoft sql","pure python"],"install":[{"cmd":"pip install python-tds","lang":"bash","label":"Basic Installation"},{"cmd":"pip install python-tds pyOpenSSL bitarray kerberos","lang":"bash","label":"With Optional Dependencies (TLS, Performance, Kerberos)"}],"dependencies":[{"reason":"Required for TLS connection encryption.","package":"pyOpenSSL","optional":true},{"reason":"Provides better performance for certain operations.","package":"bitarray","optional":true},{"reason":"Required for Kerberos authentication on non-Windows platforms (experimental).","package":"kerberos","optional":true}],"imports":[{"note":"The PyPI package is 'python-tds', but the import name is 'pytds'.","symbol":"pytds","correct":"import pytds"}],"quickstart":{"code":"import os\nimport pytds\n\n# --- Environment Variables (Replace with your actual values or secure fetching) ---\nSERVER = os.environ.get('SQL_SERVER', 'your_server.database.windows.net')\nDATABASE = os.environ.get('SQL_DATABASE', 'your_database')\nUSER = os.environ.get('SQL_USER', 'your_username')\nPASSWORD = os.environ.get('SQL_PASSWORD', 'your_password')\n\ntry:\n    # Establish a connection using a context manager\n    with pytds.connect(server=SERVER, database=DATABASE, user=USER, password=PASSWORD) as conn:\n        print(\"Successfully connected to the database!\")\n\n        # Create a cursor object using a context manager\n        with conn.cursor() as cursor:\n            # Execute a simple query\n            cursor.execute(\"SELECT 1 AS ConnectionTest\")\n\n            # Fetch the result\n            result = cursor.fetchone()\n            print(f\"Query result: {result}\")\n\n            # Example: Fetch all results\n            cursor.execute(\"SELECT 'Hello from Python-TDS' AS Message\")\n            all_results = cursor.fetchall()\n            print(f\"All results: {all_results}\")\n\nexcept pytds.Error as e:\n    print(f\"Database error occurred: {e}\")\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":"All versions when connecting to SQL Server 2022+ with strict encryption."},{"fix":"For binary values, use `pytds.Binary(your_bytes_object)`. Example: `cursor.execute('INSERT INTO MyTable (BinaryColumn) VALUES (%s)', (pytds.Binary(data),))`","message":"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.","severity":"gotcha","affected_versions":"All versions."},{"fix":"It is recommended to use `SpnegoAuth` for NTLM/Kerberos authentication instead of the deprecated `pytds.login` approach.","message":"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.","severity":"deprecated","affected_versions":"Versions 1.6+"},{"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).","message":"A common 'Permission denied' error occurs during `pip install python-tds` if the user lacks write privileges to system Python directories.","severity":"gotcha","affected_versions":"All versions on systems with strict permissions."},{"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.","message":"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`).","severity":"gotcha","affected_versions":"All versions."}],"env_vars":null,"last_verified":"2026-04-10T00:00:00.000Z","next_check":"2026-07-09T00:00:00.000Z"}