{"id":6398,"library":"mssql-python","title":"MSSQL Python Driver","description":"mssql-python is the official Microsoft Python library for interacting with Microsoft SQL Server, Azure SQL, and SQL databases in Fabric. It provides high-performance data access, bulk copy operations, and Apache Arrow integration. Currently at version 1.5.0, the library maintains an active release cadence with regular enhancements and bug fixes.","status":"active","version":"1.5.0","language":"en","source_language":"en","source_url":"https://github.com/microsoft/mssql-python","tags":["sql","mssql","database","odbc","microsoft","azure","sql-server"],"install":[{"cmd":"pip install mssql-python","lang":"bash","label":"Install mssql-python"}],"dependencies":[{"reason":"Internal dependency for core functionalities, including bulk copy and performance optimizations. Automatically installed with mssql-python.","package":"mssql-py-core","optional":false}],"imports":[{"note":"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.","wrong":"from mssql_python import connect\nconn = connect(...)","symbol":"connect","correct":"import mssql_python\nconn = mssql_python.connect(...)"}],"quickstart":{"code":"import os\nimport mssql_python\n\n# Ensure the ODBC Driver 18 for SQL Server is installed and configured.\n# On Debian/Ubuntu: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -\n#                     curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list\n#                     sudo apt update\n#                     sudo ACCEPT_EULA=Y apt install msodbcsql18\n#                     sudo ACCEPT_EULA=Y apt install mssql-tools18\n# On macOS: brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release\n#            brew update\n#            ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18\n\n# Connection details from environment variables (recommended for production)\nSERVER = os.environ.get('MSSQL_SERVER', 'localhost')\nDATABASE = os.environ.get('MSSQL_DATABASE', 'master')\nUID = os.environ.get('MSSQL_USERNAME', '')\nPWD = os.environ.get('MSSQL_PASSWORD', '')\n\n# Construct the connection string. Make sure the DRIVER matches your installed ODBC driver.\n# For Windows, it might be 'ODBC Driver 17 for SQL Server' or 'SQL Server'.\nconnection_string = (\n    f\"DRIVER={{ODBC Driver 18 for SQL Server}}মনের;\"\n    f\"SERVER={SERVER};\"\n    f\"DATABASE={DATABASE};\"\n) # Omit UID/PWD if using Windows Authentication or EntraID\n\nif UID and PWD:\n    connection_string += f\"UID={UID};PWD={PWD};\"\n\ntry:\n    with mssql_python.connect(connection_string) as cnxn:\n        with cnxn.cursor() as cursor:\n            # Execute a simple query\n            cursor.execute(\"SELECT @@SERVERNAME, @@VERSION\")\n            row = cursor.fetchone()\n            if row:\n                print(f\"Connected to: {row[0]}\")\n                print(f\"SQL Server Version: {row[1]}\")\n\n            # Example: Insert data\n            cursor.execute(\"CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY, name NVARCHAR(50))\")\n            cnxn.commit()\n            print(\"Table 'test_table' ensured.\")\n\n            cursor.execute(\"INSERT INTO test_table (id, name) VALUES (?, ?)\", 1, 'Hello')\n            cnxn.commit()\n            print(\"Inserted row (1, 'Hello').\")\n\n            # Example: Fetch all data\n            cursor.execute(\"SELECT id, name FROM test_table\")\n            for row in cursor.fetchall():\n                print(f\"Fetched: {row}\")\n\nexcept mssql_python.Error as ex:\n    sqlstate = ex.args[0]\n    print(f\"MSSQL Error: {sqlstate} - {ex}\")\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")\n","lang":"python","description":"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."},"warnings":[{"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`).","message":"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`.","severity":"breaking","affected_versions":">=1.0.0"},{"fix":"If manually setting encoding for wide character types, ensure you specify `utf-16le` or `utf-16be`. Avoid using generic `utf-16`.","message":"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.","severity":"gotcha","affected_versions":">=1.1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":"all"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.4.0"}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z"}