Install & Compatibility
Where this runs
tested against v1.0.2 · pip install
no network on importno background threads
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslpy 3.10–3.95 runs
installs and imports cleanly · install 0.0s · import 0.672s · 43.9MB
glibcpy 3.10–3.95 runs
installs and imports cleanly · install 3.4s · import 0.628s · 42MB
42MB installed
● package 42MB
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
create_engine
✓ from sqlalchemy import create_engine
sqlalchemy_pytds
✓ import sqlalchemy_pytds
✗ from sqlalchemy_tds import dialect
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).
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}")
Debug
Known issues
breakingSQLAlchemy 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.fixUpgrade SQLAlchemy to version 2.0 or newer and adapt code to the new API conventions.
affects: <1.0.0
gotchaDevelopment 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.fixExercise caution in production environments and thoroughly test your specific use cases. Monitor the project for updates and stability improvements.
affects: All versions
gotchaEncoding 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.fixEnsure 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`).
affects: All versions
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`.fixEnsure 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`.
affects: SQLAlchemy 2.0.0 - 2.0.9
gotchaBundling/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.fixAdd `import sqlalchemy_pytds` and `import pytds` in your main script, or configure your bundler (e.g., PyInstaller spec file) to include these modules explicitly.
affects: All versions
Errors
Common errors & fixes
OperationalError: (pytds.tds.Error) Login failed for user 'your_username'.
The provided username or password in the SQLAlchemy connection string is incorrect, or the specified user lacks the necessary permissions to connect to the SQL Server database.
fixVerify the username and password in your connection string and ensure the SQL Server user has correct database access permissions. If using SQL Server Authentication, confirm it's enabled on the server.
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mssql.pytds
The `sqlalchemy-pytds` library, which provides the `pytds` dialect for MS SQL Server, is not installed in your current Python environment, or SQLAlchemy cannot find its entry point.
fixInstall the `sqlalchemy-pytds` package using pip: `pip install sqlalchemy-pytds`.
OperationalError: (pytds.tds.Error) [WinError 10061] No connection could be made because the target machine actively refused it
The SQL Server host is unreachable, the specified port is incorrect, the server is not running, or a firewall is blocking the connection.
fixEnsure the SQL Server is running, its firewall allows incoming connections on the specified port (default 1433), and the hostname/IP address and port in your SQLAlchemy connection string are correct and reachable from your client.
Upgrade
Version history
1.0.2latest on PyPI · released Sep 14, 2024
Audit
Dependencies
python-tdsrequiredThis is the underlying DBAPI driver that sqlalchemy-pytds wraps for SQL Server communication.
SQLAlchemyrequiredsqlalchemy-pytds is a dialect for SQLAlchemy and requires SQLAlchemy >= 2.0.