SQLAlchemy's SQL Server pyodbc dialect with focused workarounds for two SQL Server/ODBC limitations:
- Repeated positional parameters in a
SELECTexpression and itsGROUP BYexpression are treated as different values by SQL Server. - SQL Server limits a statement to 2,100 parameters.
The dialect leaves ordinary statements parameterized. It uses SQLAlchemy's type-aware post-compile literal rendering for grouped expressions and expanding parameters. A conservative fallback handles statements containing more than 2,099 individual parameters.
Install the package and use mssql+pyodbc_mssql in place of
mssql+pyodbc:
pip install sqlalchemy_pyodbc_mssqlmssql+pyodbc_mssql://user:password@server/database?driver=ODBC+Driver+18+for+SQL+Server
See SQLAlchemy's Microsoft SQL Server documentation for pyodbc connection options.
Project structure and tooling mostly derives from Coppy. See its documentation for context and additional instructions.
This project can be updated from the upstream repo, see Updating a Project.
From zero to hero (passing tests that is):
-
Ensure host dependencies are installed
-
Start docker service dependencies (if applicable):
docker compose up -d -
Sync project virtualenv w/ lock file:
uv sync -
Configure prek:
prek install -
Run tests:
nox
The fast tests run without SQL Server. Live contract tests run when
MSSQL_TEST_URL is set. For ODBC Driver 18 and the supplied Compose service:
export MSSQL_TEST_URL='mssql+pyodbc_mssql://sa:SqlServer12!@127.0.0.1:1433/tempdb?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes&TrustServerCertificate=yes'
nox -s pytestCI uses Microsoft's official SQL Server 2022 image and probes readiness with
sqlcmd before starting the Python 3.12, 3.13, and 3.14 test sessions.
Versions are date based. A version task shows and bumps versions:
# Show current version
mise run version show
# Bump version based on date, tag, and push:
mise run version bump
# See other options
mise run version -- --help