SQLAlchemy pytds

Warnings

• breaking 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.
  Fix: Upgrade SQLAlchemy to version 2.0 or newer and adapt code to the new API conventions.
• gotcha 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.
  Fix: Exercise caution in production environments and thoroughly test your specific use cases. Monitor the project for updates and stability improvements.
• gotcha 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.
  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`).
• gotcha `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`.
  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`.
• gotcha 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.
  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.

Install

• pip install sqlalchemy-pytds Install with pip

Imports

• create_engine

  from sqlalchemy import create_engine

• sqlalchemy_pytds

  import sqlalchemy_pytds

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

Quickstart

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.

import os
from sqlalchemy import create_engine, text

# Environment variables for connection details
DB_SERVER = os.environ.get('PYTDS_SQLSERVER_HOST', 'localhost')
DB_PORT = os.environ.get('PYTDS_SQLSERVER_PORT', '1433')
DB_USER = os.environ.get('PYTDS_SQLSERVER_USER', 'sa')
DB_PASSWORD = os.environ.get('PYTDS_SQLSERVER_PASSWORD', 'yourStrongPassword123')
DB_NAME = os.environ.get('PYTDS_SQLSERVER_DB', 'master')

# Construct the connection string using the 'mssql+pytds' dialect
connection_string = (
    f"mssql+pytds://{DB_USER}:{DB_PASSWORD}@{DB_SERVER}:{DB_PORT}/{DB_NAME}"
)

# Create the engine
try:
    engine = create_engine(connection_string, echo=False) # Set echo=True for SQL logging

    # Establish a connection and execute a simple query
    with engine.connect() as connection:
        # Example: Query the SQL Server version
        result = connection.execute(text("SELECT @@VERSION AS sql_server_version"))
        for row in result:
            print(f"Connected to SQL Server Version: {row.sql_server_version}")

    print("Successfully connected and queried the database.")

except Exception as e:
    print(f"Error connecting to the database: {e}")