DOCSEARCHbyAlgolia
Submit Docs

mssql-python

repository·main·Indexed 19 days ago

https://github.com/microsoft/mssql-python

A DB API 2.0 compliant Python driver for Microsoft SQL Server and the Azure SQL family of databases. It utilizes Direct Database Connectivity (DDBC), a lightweight C++ layer that replaces the platform-specific Driver Manager to provide cross-platform consistency and reduced overhead. The driver supports various Microsoft Entra ID authentication methods and includes features such as connection pooling and bulk copy operations.

Tokens
3.9K
Snippets
11
Records
18
Agent score
66%

What's inside mssql-python

  1. How connection pooling works with Entra ID authentication

    main

    The driver provides built-in connection pooling enabled by default. When using Entra ID authentication, there are important considerations for multi-user processes:

    • Interactive/Device-code Auth: The driver caches one credential instance per authentication type for the lifetime of the process. This means all connections in the same process share the identity of the first user who authenticated. Do not use this for multi-user applications; instead, use a per-user token provider.
    • Bring-your-own token (token_provider / attrs_before): The driver cannot refresh tokens it did not mint. If you provide your own token, you are responsible for managing its lifetime. If a pooled connection is reused after a token expires, the reconnect will fail.
    • ActiveDirectoryDefault: This resolves to the ambient identity (e.g., Managed Identity). It is not designed to distinguish between end users in a multi-user workload and is not expiry-aware for custom tokens.

  2. Understand ODBC driver distribution via mssql-python-odbc

    main

    The ODBC driver binaries used by mssql-python are distributed through a companion package named mssql-python-odbc (import name: mssql_python_odbc).

    When you install mssql-python, it automatically depends on and loads these binaries. While the current release includes a fallback libs/ directory, this fallback is deprecated and will be removed in a future release. It is recommended to validate your environments against the standalone mssql-python-odbc package.

  3. Use the standalone mssql-python-odbc package

    main

    Starting with version 1.12.0, the bundled ODBC driver binaries are available as a separate, pure-data package named mssql-python-odbc (import name mssql_python_odbc, version 18.6.2).

    While mssql-python still bundles a libs/ tree for backward compatibility, it prefers the external mssql-python-odbc package at import time. You can install it separately if needed.

    pip install mssql-python-odbc

  4. Understand the mssql-python architecture and DDBC

    main

    Unlike traditional drivers like pyodbc that route calls through a platform-specific Driver Manager (which can cause inconsistent behavior across Windows, macOS, and Linux), mssql-python uses DDBC (Direct Database Connectivity).

    DDBC is a lightweight C++ layer that replaces the Driver Manager, providing:

    • Cross-platform consistency: A unified backend for connections, statements, and memory handling.
    • Direct interfacing: It communicates directly with native SQL Server drivers and the TDS core library.
    • Reduced overhead: Lower function call overhead by bypassing the Driver Manager.
    • Simplified deployment: On Windows, pip install mssql-python is sufficient with zero external dependencies. It is built using PyBind11 to ensure native-speed execution and memory-safe bindings.

  5. Install the Microsoft Python Driver for SQL Server

    main

    To use the Microsoft official Python driver for SQL Server, Azure SQL, and SQL databases in Fabric, install the mssql-python package.

    Note: Do not install mssql-python-odbc directly. The mssql-python package automatically handles the correct dependency on mssql-python-odbc and loads the necessary Microsoft ODBC Driver 18 for SQL Server binaries for you.

    pip install mssql-python

  6. Build DDBC Bindings on Windows

    main

    To build the DDBC Bindings on Windows (supporting x64 and ARM64), follow these steps:

    1. Install PyBind11: Use pip to install the required library.
    2. Install Visual Studio Build Tools: Download from the official Microsoft site and ensure the Desktop development with C++ workload is selected (this includes CMake).
    3. Run Build: Open the Developer Command Prompt for VS 2022, navigate to the pybind directory, and execute the build script.

    The build script cleans existing directories, detects your Python version and architecture, compiles mssql_python/pybind/ddbc_bindings.cpp via CMake, and generates a versioned .pyd file in the parent mssql_python directory.

    # 1. Install PyBind11
pip install pybind11

# 2. Navigate to pybind and run build script (inside Developer Command Prompt)
cd pybind
build.bat

  7. Prerequisites for running benchmarks

    main

    To successfully run the benchmark scripts, ensure the following requirements are met:

    • SQL Server Instance: A running SQL Server instance must be accessible.
    • Database: AdventureWorks2022 is required for perf-benchmarking.py.
    • Permissions: The database user must have permissions to create and drop tables and stored procedures (specifically for bench_mssql.py). perf-benchmarking.py requires read permissions.
    • Dependencies: Both pyodbc and mssql-python must be installed.
    • Environment Variables: For bench_mssql.py, the DB_CONNECTION_STRING environment variable must be set.

  8. How to file issues and get help

    main
    To report bugs or request new features for the mssql-python driver, use GitHub Issues. Before creating a new issue, search the existing issue tracker to ensure your topic has not already been addressed, which helps prevent duplicates.

  9. Run Real-World Query Benchmarks with `perf-benchmarking.py`

    main

    The perf-benchmarking.py script is a standalone tool that tests real-world queries against the AdventureWorks2022 database. It performs statistical analysis (average, min, max, and standard deviation) over multiple iterations (default: 5) to provide speedup comparisons between pyodbc and mssql-python.

    # Run from the project root
python benchmarks/perf-benchmarking.py

  10. Run Richbench Framework Benchmarks with `bench_mssql.py`

    main

    The bench_mssql.py script provides comprehensive benchmarks for SELECT, INSERT, UPDATE, DELETE, complex queries, stored procedures, and transaction handling using the richbench framework. It includes automated setup and cleanup, using tables prefixed with perfbenchmark_ and a stored procedure named perfbenchmark_stored_procedure.

    # 1. Set the connection string
export DB_CONNECTION_STRING="Server=your_server;Database=AdventureWorks2022;UID=your_user;PWD=your_password;"

# 2. Install the benchmarking tool
pip install richbench

# 3. Run benchmarks from the project root
richbench benchmarks

  11. Build DDBC Bindings on macOS

    main

    To build the DDBC Bindings on macOS (supporting both Apple Silicon ARM64 and Intel x86_64 via universal2), follow these steps:

    1. Install Dependencies: Use Homebrew to install CMake and pip to install PyBind11.
    2. Install Microsoft ODBC Driver (msodbcsql18): This is required for the development headers (sql.h, sqlext.h) and the dynamic library (libmsodbcsql.18.dylib). You must accept the EULA during installation.
    3. Run Build: Navigate to the pybind directory and execute ./build.sh.

    The build script cleans artifacts, detects architecture, configures CMake, and generates a .so file (e.g., ddbc_bindings.cp313-universal2.so) in the parent mssql_python directory.

    # 1. Install CMake & PyBind11
brew install cmake
pip install pybind11

# 2. Install Microsoft ODBC Driver
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
ACCEPT_EULA=Y brew install msodbcsql18

# 3. Build
cd pybind
./build.sh

  12. Install mssql-python

    main

    Install the Microsoft Python Driver for SQL Server using pip. Note that for Linux and macOS, specific system dependencies must be installed first to ensure the driver functions correctly.

    # Windows
pip install mssql-python

# MacOS (requires OpenSSL)
brew install openssl
pip install mssql-python

# Linux (Debian/Ubuntu)
apt-get install -y libltdl7 libkrb5-3 libgssapi-krb5-2
pip install mssql-python

# Linux (RHEL)
dnf install -y libtool-ltdl krb5-libs
pip install mssql-python

# Linux (SUSE)
zypper install -y libltdl7 libkrb5-3 libgssapi-krb5-2
pip install mssql-python

# Linux (Alpine)
apk add libtool krb5-libs krb5-dev
pip install mssql-python

# Linux (Azure Linux)
tdnf distro-sync && tdnf install -y libtool-ltdl krb5-libs glibc-iconv
pip install mssql-python