{"id":683,"library":"pymssql","title":"pymssql","description":"pymssql is a Python DB-API (PEP-249) interface to Microsoft SQL Server, built on top of FreeTDS. The 2.x branch, a complete rewrite using Cython, offers improved performance and Python 3 compatibility. It is actively maintained with regular releases, currently at version 2.3.13, and supports Python 3.9 and newer.","status":"active","version":"2.3.13","language":"python","source_language":"en","source_url":"https://github.com/pymssql/pymssql","tags":["database","mssql","sql server","db-api","freetds","cython"],"install":[{"cmd":"pip install pymssql","lang":"bash","label":"Install latest stable version"}],"dependencies":[{"reason":"pymssql builds on FreeTDS. Official wheels bundle a static copy, but for source builds, specific FreeTDS features (like SSL/Kerberos), or certain environments (e.g., Azure), a system-installed FreeTDS (v1.2.18+ recommended, though v0.95+ was previously sufficient for some features) might be required or preferred.","package":"FreeTDS","optional":true},{"reason":"Required for building pymssql from source (e.g., when wheels are not available for your platform/Python version). Specifically requires Cython > 3.0.10 for recent pymssql versions.","package":"Cython","optional":true}],"imports":[{"note":"This is the primary DB-API compliant module for general use.","symbol":"pymssql","correct":"import pymssql"},{"note":"A lower-level module offering potentially better performance and more direct control, but less DB-API compliant.","symbol":"_mssql","correct":"from pymssql import _mssql"}],"quickstart":{"code":"import pymssql\nimport os\n\n# Environment variables are recommended for sensitive credentials\nSERVER = os.environ.get('PYMSSQL_SERVER', 'your_server.database.windows.net')\nUSER = os.environ.get('PYMSSQL_USER', 'your_username')\nPASSWORD = os.environ.get('PYMSSQL_PASSWORD', 'your_password')\nDATABASE = os.environ.get('PYMSSQL_DATABASE', 'your_database')\n\ntry:\n    # Establish connection using a context manager\n    with pymssql.connect(server=SERVER, user=USER, password=PASSWORD, database=DATABASE) as conn:\n        print(\"Successfully connected to SQL Server!\")\n        # Create a cursor, with results returned as dictionaries\n        with conn.cursor(as_dict=True) as cursor:\n            # Execute a query\n            cursor.execute('SELECT @@VERSION as server_version, GETDATE() as current_time')\n            \n            # Fetch one row and print it\n            row = cursor.fetchone()\n            if row:\n                print(f\"Server Version: {row['server_version']}\")\n                print(f\"Current Server Time: {row['current_time']}\")\n            \n            # Example: Insert data (if autocommit is False, call conn.commit())\n            # cursor.execute(\"INSERT INTO YourTable (Col1, Col2) VALUES (%s, %s)\", ('value1', 123))\n            # conn.commit() \n\nexcept pymssql.OperationalError as e:\n    print(f\"Connection failed: {e}\")\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")","lang":"python","description":"This quickstart demonstrates how to establish a connection to a SQL Server database, execute a simple query, and fetch results using the `pymssql` module. It uses environment variables for credentials, which is a recommended practice for security. Results are fetched as dictionaries for easier access."},"warnings":[{"fix":"Review the official 'Migrating from 1.x to 2.x' documentation. Update connection parameters: `host` becomes `server`. Audit code for removed or changed functions.","message":"Migration from pymssql 1.x to 2.x involves significant API changes. Version 2.0.0 was a complete rewrite in Cython, dropping Python 2.6 support and introducing incompatible changes such as the removal of the `dsn` parameter and the renaming of `host` to `server` in `pymssql.connect()`.","severity":"breaking","affected_versions":"All 2.x versions when migrating from 1.x"},{"fix":"Explicitly set `tds_version` in `pymssql.connect()` (e.g., `tds_version='7.3'` or `'8.0'`) or configure FreeTDS globally if experiencing connection issues or missing functionality.","message":"Default TDS protocol version changed. Starting with pymssql 2.1.4, the default TDS protocol version is no longer '7.1' (it became `None` in 2.2.0), requiring explicit specification via the `tds_version` parameter in `pymssql.connect()` or `_mssql.connect()`, a `TDSVER` environment variable, or `freetds.conf` for certain SQL Server versions or features. Using an unsupported TDS version for your FreeTDS library can lead to unexpected behavior.","severity":"gotcha","affected_versions":"2.1.4 and newer"},{"fix":"For SSL/Azure or Kerberos authentication, you may need to install FreeTDS with SSL/Kerberos support on your system and then build `pymssql` from source against it, or use a Docker image that is pre-configured with the necessary FreeTDS libraries. Alternatively, consider using `pyodbc` for Azure SQL Database connections.","message":"The statically-linked FreeTDS bundled with official `pymssql` wheels for Linux and Windows might lack SSL and Kerberos support. This can prevent connections to Azure SQL Database or domain logins to SQL Server instances that require these features.","severity":"gotcha","affected_versions":"All 2.x versions using official wheels"},{"fix":"Ensure you have the correct version of Microsoft C++ Build Tools installed (often part of Visual Studio Community Edition or standalone build tools). Upgrade pip if it's too old to handle `manylinux` wheels.","message":"On Windows, if pre-built wheels are not available or fail, installing `pymssql` from source requires Microsoft C++ Build Tools (e.g., Visual C++ 14.0 or newer), which can be a common installation hurdle.","severity":"gotcha","affected_versions":"All versions, when installing from source on Windows"},{"fix":"Verify SQL Server configuration (SQL Server Configuration Manager, Surface Area Configuration). Check network firewalls. Use `tsql -H` from FreeTDS to diagnose connectivity issues independently of Python. Ensure `server` and `port` are correctly specified.","message":"Connection failures are often due to SQL Server configuration (e.g., remote connections disabled, specific protocols not enabled, or firewall blocking the connection) rather than `pymssql` itself. Issues with `freetds.conf` (if used) can also cause problems.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Always provide explicit aliases for aggregated columns or expressions in your SQL queries (e.g., `SELECT MAX(column_name) AS max_value FROM ...`).","message":"When fetching rows as dictionaries using `cursor(as_dict=True)`, aggregated columns or expressions without explicit aliases (e.g., `SELECT MAX(column_name) FROM ...`) may result in the column being omitted from the dictionary, as `pymssql` cannot determine a suitable dictionary key.","severity":"gotcha","affected_versions":"All 2.x versions using `as_dict=True`"},{"fix":"Upgrade to Python 3.9 or newer to use current `pymssql` versions. If stuck on Python 2, you must use `pymssql` 2.1.4 or older (which is no longer maintained).","message":"Python 2 support was officially dropped with `pymssql` version 2.1.5. Newer versions are exclusively for Python 3.","severity":"deprecated","affected_versions":"2.1.5 and newer"}],"env_vars":null,"last_verified":"2026-05-12T17:48:05.609Z","next_check":"2026-06-26T00:00:00.000Z","problems":[{"fix":"Install the package using pip: `pip install pymssql`. If using a virtual environment, ensure it is activated. If still failing, check the Python interpreter path in your IDE.","cause":"The pymssql package is not installed in the Python environment being used, or the Python interpreter cannot locate it due to PATH issues or virtual environment misconfiguration.","error":"ModuleNotFoundError: No module named 'pymssql'"},{"fix":"Verify the server hostname/IP and port (default 1433), ensure the SQL Server instance is running and configured for remote connections, check firewall rules on both client and server, and ensure the SQL Server Browser service is running if connecting to a named instance. Use `tsql -H <host> -p <port> -U <user>` to diagnose FreeTDS connectivity separately.","cause":"The SQL Server is unreachable, not running, or network connectivity issues (e.g., firewall, incorrect hostname/IP, incorrect port, or SQL Server Browser service not running for named instances) are preventing a connection.","error":"pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\\nUnable to connect: Adaptive Server is unavailable or does not exist (hostname)\\n')"},{"fix":"Double-check the username and password in your connection string. Ensure the SQL Server login exists and has permissions for the database. Also, be aware of password length limitations (e.g., historically 30 characters for some pymssql/FreeTDS versions).","cause":"The provided username or password for connecting to the SQL Server is incorrect, or the user does not have the necessary permissions to log in to the specified database.","error":"pymssql.OperationalError: (18456, \"Login failed for user 'xxx'.DB-Lib...\")"},{"fix":"Ensure the correct Microsoft Visual C++ Redistributable (e.g., 2015-2022) is installed for your system architecture. If installing from source, ensure you have the necessary build tools. Sometimes, using a pre-compiled wheel (`.whl` file) downloaded from a reliable source can bypass build issues.","cause":"This error typically occurs on Windows when `pymssql` or its underlying FreeTDS dependencies cannot find required DLLs, often due to missing Microsoft Visual C++ Redistributable packages or issues with the FreeTDS library installation.","error":"ImportError: DLL load failed: The specified module could not be found."},{"fix":"Install the FreeTDS development package for your operating system (e.g., `sudo apt-get install freetds-dev` on Debian/Ubuntu, `brew install freetds` on macOS). After installing FreeTDS, try reinstalling `pymssql` again: `pip install pymssql`.","cause":"This compilation error, common on Linux/macOS, indicates that the FreeTDS development headers (specifically `sqlfront.h`) are missing or not found in the standard include paths when `pymssql` is being installed via pip.","error":"_mssql.c:266:10: fatal error: 'sqlfront.h' file not found"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":80,"quickstart_tag":"verified","pypi_latest":"2.3.13","install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.5,"disk_size":"26.8M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.5,"disk_size":"26.8M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":1.7,"import_time_s":0.03,"mem_mb":1.5,"disk_size":"26M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.02,"mem_mb":1.5,"disk_size":"26M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.2,"disk_size":"28.5M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.2,"disk_size":"28.5M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":1.8,"import_time_s":0.03,"mem_mb":1.2,"disk_size":"28M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":1.2,"disk_size":"28M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":0.8,"disk_size":"20.6M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":0.8,"disk_size":"20.6M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":1.6,"import_time_s":0.02,"mem_mb":0.8,"disk_size":"20M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.02,"mem_mb":0.8,"disk_size":"20M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":0.9,"disk_size":"20.3M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.02,"mem_mb":0.9,"disk_size":"20.2M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":1.8,"import_time_s":0.02,"mem_mb":0.7,"disk_size":"20M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.02,"mem_mb":0.7,"disk_size":"20M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":1.5,"disk_size":"26.3M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.5,"disk_size":"26.3M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":" $EXIT -eq 0 ","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":2.1,"import_time_s":0.04,"mem_mb":1.5,"disk_size":"26M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.03,"mem_mb":1.5,"disk_size":"26M"}]},"quickstart_checks":{"last_tested":"2026-04-24","tag":"verified","tag_description":"quickstart runs on critical runtimes, recently tested","results":[{"runtime":"python:3.10-alpine","exit_code":0},{"runtime":"python:3.10-slim","exit_code":0},{"runtime":"python:3.11-alpine","exit_code":0},{"runtime":"python:3.11-slim","exit_code":0},{"runtime":"python:3.12-alpine","exit_code":0},{"runtime":"python:3.12-slim","exit_code":0},{"runtime":"python:3.13-alpine","exit_code":0},{"runtime":"python:3.13-slim","exit_code":0},{"runtime":"python:3.9-alpine","exit_code":0},{"runtime":"python:3.9-slim","exit_code":0}]}}