pymssql

Warnings

• breaking 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()`.
  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.
• gotcha 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.
  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.
• gotcha 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.
  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.
• gotcha 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.
  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.
• gotcha 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.
  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.
• gotcha 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.
  Fix: Always provide explicit aliases for aggregated columns or expressions in your SQL queries (e.g., `SELECT MAX(column_name) AS max_value FROM ...`).
• deprecated Python 2 support was officially dropped with `pymssql` version 2.1.5. Newer versions are exclusively for Python 3.
  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).

Install

• pip install pymssql Install latest stable version

Imports

• pymssql

  import pymssql

  This is the primary DB-API compliant module for general use.
• _mssql

  from pymssql import _mssql

  A lower-level module offering potentially better performance and more direct control, but less DB-API compliant.

Quickstart

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.

import pymssql
import os

# Environment variables are recommended for sensitive credentials
SERVER = os.environ.get('PYMSSQL_SERVER', 'your_server.database.windows.net')
USER = os.environ.get('PYMSSQL_USER', 'your_username')
PASSWORD = os.environ.get('PYMSSQL_PASSWORD', 'your_password')
DATABASE = os.environ.get('PYMSSQL_DATABASE', 'your_database')

try:
    # Establish connection using a context manager
    with pymssql.connect(server=SERVER, user=USER, password=PASSWORD, database=DATABASE) as conn:
        print("Successfully connected to SQL Server!")
        # Create a cursor, with results returned as dictionaries
        with conn.cursor(as_dict=True) as cursor:
            # Execute a query
            cursor.execute('SELECT @@VERSION as server_version, GETDATE() as current_time')
            
            # Fetch one row and print it
            row = cursor.fetchone()
            if row:
                print(f"Server Version: {row['server_version']}")
                print(f"Current Server Time: {row['current_time']}")
            
            # Example: Insert data (if autocommit is False, call conn.commit())
            # cursor.execute("INSERT INTO YourTable (Col1, Col2) VALUES (%s, %s)", ('value1', 123))
            # conn.commit() 

except pymssql.OperationalError as e:
    print(f"Connection failed: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")