pyodbc: Python ODBC Bridge

5.3.0 · active · verified Sat Mar 28

pyodbc is an open-source Python module that provides a simple and consistent interface for connecting to various databases via ODBC (Open Database Connectivity), implementing the DB API 2.0 specification. It allows for executing SQL queries, retrieving results, and managing database operations efficiently. As of version 5.3.0, it supports Python 3.9 through 3.14 and is actively maintained with frequent releases.

Warnings

breaking pyodbc 5.x dropped support for Python 2.x. If you are on an older Python 2 environment, you must use pyodbc 4.x or migrate to Python 3.
Fix: Upgrade to Python 3.x and pyodbc 5.x, or stay on pyodbc 4.x for Python 2.7.
breaking pyodbc 5.3.0 dropped support for Python 3.8, requiring Python 3.9 or newer.
Fix: Upgrade your Python environment to 3.9 or later, or use pyodbc < 5.3.0 for Python 3.8.
gotcha pyodbc is a bridge to ODBC drivers. You *must* install an appropriate ODBC driver manager (e.g., unixODBC on Linux/macOS) and specific database ODBC drivers (e.g., SQL Server ODBC Driver) on your system for pyodbc to function. This is an external, operating-system-level dependency, not a Python package dependency.
Fix: Refer to `pyodbc` documentation or your database vendor's documentation for installing the necessary ODBC drivers and driver manager for your operating system.
gotcha Transactions are not automatically committed by default. You must explicitly call `connection.commit()` after `INSERT`, `UPDATE`, or `DELETE` operations, or pass `autocommit=True` to `pyodbc.connect()` if you want each statement to commit immediately.
Fix: Call `cnxn.commit()` after data modification statements or initialize connection with `pyodbc.connect(..., autocommit=True)`.
gotcha Version 5.0.0 introduced a bug preventing `bytes` objects from being passed in the `attrs_before` parameter of `pyodbc.connect()`, commonly used for Azure authentication tokens. This was fixed in 5.0.1.
Fix: Upgrade to pyodbc 5.0.1 or later.
gotcha macOS ARM (Apple Silicon M1/M2) users experienced issues with precompiled binaries in early 5.x releases. Dedicated Mac ARM wheels were introduced and stabilized in 5.1.0.
Fix: Upgrade to pyodbc 5.1.0 or later for stable macOS ARM support.

Install

pip install pyodbc Install latest version

Imports

pyodbc
```
import pyodbc
```

connect

cnxn = pyodbc.connect('DSN=your_dsn;UID=user;PWD=pass')

Quickstart

This quickstart demonstrates how to establish a connection to an ODBC database using `pyodbc`, execute SQL commands, insert data, and retrieve results. It uses environment variables for connection details and requires an appropriate ODBC driver and DSN (or DSN-less connection string) to be pre-configured on your system.

import pyodbc
import os

# NOTE: This example requires an ODBC driver and DSN to be configured on your system.
# For Windows, common drivers like 'SQL Server' might be available.
# For Linux/macOS, you might need to install unixODBC and specific database drivers.
# Example DSN-less connection string (replace with your actual details):
DB_DRIVER = os.environ.get('PYODBC_DRIVER', '{ODBC Driver 17 for SQL Server}')
DB_SERVER = os.environ.get('PYODBC_SERVER', 'your_server.database.windows.net')
DB_DATABASE = os.environ.get('PYODBC_DATABASE', 'your_database_name')
DB_UID = os.environ.get('PYODBC_UID', 'your_username')
DB_PWD = os.environ.get('PYODBC_PWD', 'your_password')

connection_string = (
    f"DRIVER={DB_DRIVER};"
    f"SERVER={DB_SERVER};"
    f"DATABASE={DB_DATABASE};"
    f"UID={DB_UID};"
    f"PWD={DB_PWD};"
)

try:
    cnxn = pyodbc.connect(connection_string)
    cursor = cnxn.cursor()

    # Example: Create a table (if it doesn't exist)
    try:
        cursor.execute("CREATE TABLE #TestTable (id INT, name VARCHAR(50))")
        print("Table #TestTable created.")
    except pyodbc.ProgrammingError as e:
        if 'There is already an object named' in str(e): # For SQL Server temp table
            print("Table #TestTable already exists, skipping creation.")
        else:
            raise

    # Example: Insert data
    cursor.execute("INSERT INTO #TestTable (id, name) VALUES (?, ?)", 1, 'Alice')
    cursor.execute("INSERT INTO #TestTable (id, name) VALUES (?, ?)", 2, 'Bob')
    cnxn.commit() # Commit changes if autocommit is not enabled
    print("Data inserted.")

    # Example: Select data
    cursor.execute("SELECT id, name FROM #TestTable")
    rows = cursor.fetchall()
    print("\nFetched Data:")
    for row in rows:
        print(f"ID: {row.id}, Name: {row.name}")

except pyodbc.Error as ex:
    sqlstate = ex.args[0]
    print(f"Database Error (SQLSTATE: {sqlstate}): {ex}")
finally:
    if 'cnxn' in locals() and cnxn:
        cnxn.close()
        print("\nConnection closed.")

view raw JSON →