MSSQL Python Driver

Warnings

• breaking The `mssql-python` library (v1.0.0+) requires the Microsoft ODBC Driver for SQL Server (version 17 or 18) to be installed on the system, which is a native, non-Python dependency. This driver must be installed manually and is not handled by `pip`.
  Fix: Before installing `mssql-python`, install the appropriate Microsoft ODBC Driver for SQL Server for your operating system. Refer to Microsoft's official documentation for installation instructions (e.g., for Ubuntu, macOS, Windows). Ensure the `DRIVER={...}` string in your connection matches the installed driver (e.g., `ODBC Driver 18 for SQL Server`).
• gotcha When handling `SQL_WCHAR` types, the library strictly validates encoding settings, allowing only `utf-16le` and `utf-16be`. Generic `utf-16` with Byte Order Mark (BOM) is explicitly rejected due to byte order ambiguity.
  Fix: If manually setting encoding for wide character types, ensure you specify `utf-16le` or `utf-16be`. Avoid using generic `utf-16`.
• gotcha The `mssql-python` driver, being a wrapper around ODBC, is not thread-safe by default at the connection level for concurrent operations on the *same connection object*. While internal encoding/decoding operations are thread-safe (v1.1.0+), shared connection objects require careful management.
  Fix: For concurrent database operations, create a separate connection object for each thread or use a connection pooling library. Avoid sharing a single `mssql_python.connect` object across multiple threads without explicit locking mechanisms.
• gotcha The `bulkcopy()` method on the Cursor object (public API since v1.4.0) provides high-performance bulk data loading. While powerful, it requires careful handling of data types and column mapping, and logs extensively.
  Fix: Familiarize yourself with the `cursor.bulkcopy()` documentation. Pay close attention to data type conversions, potential logging verbosity, and error handling, especially for large datasets. Ensure data provided matches target table schema.

Install

• pip install mssql-python Install mssql-python

Imports

• connect

  import mssql_python
  conn = mssql_python.connect(...)

  While 'from mssql_python import connect' works, the official documentation and examples often use 'import mssql_python' followed by 'mssql_python.connect' for clarity and consistency with other functions/classes.

Quickstart

This quickstart demonstrates how to connect to a Microsoft SQL Server database using `mssql-python`, execute a simple query, create a table, insert data, and fetch results. It highlights the use of environment variables for secure credential management and the recommended `with` statement for connection and cursor management.

import os
import mssql_python

# Ensure the ODBC Driver 18 for SQL Server is installed and configured.
# On Debian/Ubuntu: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
#                     curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
#                     sudo apt update
#                     sudo ACCEPT_EULA=Y apt install msodbcsql18
#                     sudo ACCEPT_EULA=Y apt install mssql-tools18
# On macOS: brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
#            brew update
#            ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

# Connection details from environment variables (recommended for production)
SERVER = os.environ.get('MSSQL_SERVER', 'localhost')
DATABASE = os.environ.get('MSSQL_DATABASE', 'master')
UID = os.environ.get('MSSQL_USERNAME', '')
PWD = os.environ.get('MSSQL_PASSWORD', '')

# Construct the connection string. Make sure the DRIVER matches your installed ODBC driver.
# For Windows, it might be 'ODBC Driver 17 for SQL Server' or 'SQL Server'.
connection_string = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}}মনের;"
    f"SERVER={SERVER};"
    f"DATABASE={DATABASE};"
) # Omit UID/PWD if using Windows Authentication or EntraID

if UID and PWD:
    connection_string += f"UID={UID};PWD={PWD};"

try:
    with mssql_python.connect(connection_string) as cnxn:
        with cnxn.cursor() as cursor:
            # Execute a simple query
            cursor.execute("SELECT @@SERVERNAME, @@VERSION")
            row = cursor.fetchone()
            if row:
                print(f"Connected to: {row[0]}")
                print(f"SQL Server Version: {row[1]}")

            # Example: Insert data
            cursor.execute("CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY, name NVARCHAR(50))")
            cnxn.commit()
            print("Table 'test_table' ensured.")

            cursor.execute("INSERT INTO test_table (id, name) VALUES (?, ?)", 1, 'Hello')
            cnxn.commit()
            print("Inserted row (1, 'Hello').")

            # Example: Fetch all data
            cursor.execute("SELECT id, name FROM test_table")
            for row in cursor.fetchall():
                print(f"Fetched: {row}")

except mssql_python.Error as ex:
    sqlstate = ex.args[0]
    print(f"MSSQL Error: {sqlstate} - {ex}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")