{"id":5069,"library":"sqlalchemy-pytds","title":"SQLAlchemy pytds","description":"SQLAlchemy pytds is a pure Python TDS (Tabular Data Stream) connector for SQLAlchemy, enabling communication with Microsoft SQL Server databases. It provides a DBAPI 2.0 compliant interface implemented entirely in Python, removing external dependencies like FreeTDS or ODBC drivers. The current version is 1.0.2, released on September 14, 2024, indicating active development and maintenance.","status":"active","version":"1.0.2","language":"en","source_language":"en","source_url":"https://github.com/m32/sqlalchemy-tds","tags":["SQLAlchemy","SQL Server","TDS","database connector","DBAPI","MSSQL"],"install":[{"cmd":"pip install sqlalchemy-pytds","lang":"bash","label":"Install with pip"}],"dependencies":[{"reason":"This is the underlying DBAPI driver that sqlalchemy-pytds wraps for SQL Server communication.","package":"python-tds","optional":false},{"reason":"sqlalchemy-pytds is a dialect for SQLAlchemy and requires SQLAlchemy >= 2.0.","package":"SQLAlchemy","optional":false}],"imports":[{"symbol":"create_engine","correct":"from sqlalchemy import create_engine"},{"note":"While the project repository name is `sqlalchemy-tds`, the installable package and import path for the dialect itself is `sqlalchemy_pytds`. Explicitly importing the top-level `sqlalchemy_pytds` module may also be necessary in some bundling scenarios (e.g., PyInstaller).","wrong":"from sqlalchemy_tds import dialect","symbol":"sqlalchemy_pytds","correct":"import sqlalchemy_pytds"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\n# Environment variables for connection details\nDB_SERVER = os.environ.get('PYTDS_SQLSERVER_HOST', 'localhost')\nDB_PORT = os.environ.get('PYTDS_SQLSERVER_PORT', '1433')\nDB_USER = os.environ.get('PYTDS_SQLSERVER_USER', 'sa')\nDB_PASSWORD = os.environ.get('PYTDS_SQLSERVER_PASSWORD', 'yourStrongPassword123')\nDB_NAME = os.environ.get('PYTDS_SQLSERVER_DB', 'master')\n\n# Construct the connection string using the 'mssql+pytds' dialect\nconnection_string = (\n    f\"mssql+pytds://{DB_USER}:{DB_PASSWORD}@{DB_SERVER}:{DB_PORT}/{DB_NAME}\"\n)\n\n# Create the engine\ntry:\n    engine = create_engine(connection_string, echo=False) # Set echo=True for SQL logging\n\n    # Establish a connection and execute a simple query\n    with engine.connect() as connection:\n        # Example: Query the SQL Server version\n        result = connection.execute(text(\"SELECT @@VERSION AS sql_server_version\"))\n        for row in result:\n            print(f\"Connected to SQL Server Version: {row.sql_server_version}\")\n\n    print(\"Successfully connected and queried the database.\")\n\nexcept Exception as e:\n    print(f\"Error connecting to the database: {e}\")","lang":"python","description":"This quickstart demonstrates how to establish a connection to a Microsoft SQL Server database using `sqlalchemy-pytds` and `SQLAlchemy`'s `create_engine` function. It retrieves connection parameters from environment variables or uses defaults, then executes a simple query to verify the connection."},"warnings":[{"fix":"Upgrade SQLAlchemy to version 2.0 or newer and adapt code to the new API conventions.","message":"SQLAlchemy 2.0 Requirement: `sqlalchemy-pytds` explicitly requires `SQLAlchemy >= 2.0`. Users migrating from applications built with SQLAlchemy 1.x will encounter significant API and behavioral changes, including a new ORM statement paradigm and result object structure. Refer to SQLAlchemy's migration guides for details.","severity":"breaking","affected_versions":"<1.0.0"},{"fix":"Exercise caution in production environments and thoroughly test your specific use cases. Monitor the project for updates and stability improvements.","message":"Development Status (Beta): The library is classified as 'Development Status :: 4 - Beta' on PyPI. While functional, this indicates it might not be considered fully stable or production-ready for all mission-critical use cases, and APIs could potentially evolve.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure consistent encoding settings (e.g., UTF-8) across your database, Python environment, and application code. Explicitly specify encoding where possible (e.g., in connection string parameters if supported by the underlying `python-tds`).","message":"Encoding Issues (`UnicodeDecodeError`): Users have reported `UnicodeDecodeError` when processing data, especially when integrating with libraries like Pandas or in certain cloud environments. This often stems from character encoding mismatches between the SQL Server, the `python-tds` driver, and Python's default encoding.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure you are using SQLAlchemy 2.0.10 or a later version if you intend to use the `RETURNING` clause with SQL Server via `sqlalchemy-pytds`.","message":"`RETURNING` Clause Behavior on SQL Server (SQLAlchemy 2.0.9): For a brief period in SQLAlchemy 2.0.9, the `RETURNING` clause for SQL Server was temporarily disabled due to issues with row ordering. It was re-enabled in SQLAlchemy 2.0.10 with special handling. Users on SQLAlchemy 2.0.9 or earlier patch versions might encounter unexpected behavior or errors with `RETURNING`.","severity":"gotcha","affected_versions":"SQLAlchemy 2.0.0 - 2.0.9"},{"fix":"Add `import sqlalchemy_pytds` and `import pytds` in your main script, or configure your bundler (e.g., PyInstaller spec file) to include these modules explicitly.","message":"Bundling/Packaging Issues (e.g., PyInstaller): When creating standalone executables with tools like PyInstaller, the dynamic loading nature of SQLAlchemy dialects can sometimes lead to missing module errors. Explicitly importing `sqlalchemy_pytds` and its underlying driver `python-tds` at a high level in your application might be necessary to ensure they are properly included in the bundle.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}