mrkeyoor.com_
Thu 06 Aug 15:40 UTC
PyPIDataupdated 06 Aug 2026

pyodbc

pyodbc is a C++ extension that lets Python talk to any database that ships an ODBC driver: SQL Server, Azure SQL, Postgres, MySQL, Oracle, DB2, Snowflake, Teradata, even Access files and Excel sheets. It implements DB-API 2.0, so the shape is the one you already know (connect, cursor, execute, fetchone, fetchall, commit) with a few conveniences layered on: rows expose their columns as attributes, cursor.execute returns the cursor so you can chain a fetch onto it, and fetchval pulls a single scalar out of a one-cell result. The important thing to understand is that pyodbc is not a database driver. It is a bridge to the ODBC driver manager installed on the machine, which then loads the vendor's driver. Almost all of the install pain and most of the odd behavior you will hit comes from that layer underneath rather than from pyodbc itself.

pyodbc · v5.3.0 · 10.2M/wk · ★ 3,079 · pushed 06 Jun 2026 · mkleehammer/pyodbc · docs · reviewed by Keyoor

Verdict

If your database is SQL Server or you need one API across a pile of enterprise databases, pyodbc is still the default and the ecosystem is built around it. Just budget the setup time for unixODBC and the vendor driver, and check whether Microsoft's mssql-python fits before you inherit that install story on a new SQL Server project.

API stability5/5It is DB-API 2.0 with a thin set of documented extras, and code written against pyodbc 4.x mostly runs unchanged on 5.3.0; the only real break in the 5.0 line was dropping Python 2 and old CPython versions
Docs2/5The documentation is a GitHub wiki with a reasonably complete Cursor and Connection reference but no versioning, no proper search, Python 2.7 examples still in place, and the parts you actually need (driver install per platform, per-database quirks, encoding setup) spread across pages you have to already know exist
Maintenance3/5Alive but slow: 5.3.0 shipped 17 October 2025, the last push was 6 June 2026, and roughly one release lands a year against 41 open issues and 19 open PRs held by a small maintainer group. Wheel coverage does track new CPython releases, with 3.14 added in 5.3.0
Ecosystem5/5About 10.2M weekly downloads, it is the driver behind SQLAlchemy's mssql+pyodbc dialect and most dbt and pandas workflows that touch SQL Server, and Microsoft's connectivity documentation has pointed at it for years

Use it if

  • You are connecting Python to SQL Server or Azure SQL. Microsoft's own connectivity docs, the SQLAlchemy mssql+pyodbc dialect, and roughly two decades of Stack Overflow answers all assume this package, so you are on the path with the most working examples behind it
  • You need one connection API across several vendors your company already has drivers for (DB2, Oracle, Informix, Teradata, Snowflake, Sybase) instead of a separate Python package and a separate connection idiom per database
  • You are on Windows, where the ODBC driver manager is part of the OS and system DSNs may already be configured, so pyodbc.connect('DSN=prod') works with nothing else installed
  • You want plain DB-API 2.0 so that pandas, SQLAlchemy, dbt adapters, and internal tooling can sit on top of the same connection object
  • You need schema introspection without writing vendor-specific catalog SQL: cursor.tables(), cursor.columns(), cursor.primaryKeys(), and cursor.foreignKeys() wrap the ODBC catalog functions and return normal fetchable rows
Skip it if

Setup reality

pip install pyodbc gets you binary wheels for CPython 3.9 through 3.14 on manylinux and musllinux (x86_64 and aarch64), macOS x86_64 and arm64, and Windows win32, win_amd64, and win_arm64. There are no PyPy wheels, so anything off that matrix compiles the C++ extension from source and needs a compiler plus the unixODBC development headers. The wheel is only half the job. On Linux and macOS you must also install a driver manager (apt install unixodbc or brew install unixodbc) and then the vendor driver itself, which for SQL Server means adding Microsoft's package repository and installing msodbcsql18 with ACCEPT_EULA set. On Windows the driver manager is built in but the driver still is not. The first three failures are predictable. One, the DRIVER= name in your connection string must match a string from pyodbc.drivers() exactly, braces and version number included, or you get the confusing Data source name not found error. Two, ODBC Driver 18 for SQL Server flipped the default to Encrypt=yes, so local development against a self-signed certificate fails until you add TrustServerCertificate=yes. Three, on Alpine the musllinux wheel installs fine but Microsoft's driver needs its own musl build, so the import succeeds and the connect does not.

Patterns

Open a connection and read rowsconnect-and-query

import pyodbc

conn_str = (
    'DRIVER={ODBC Driver 18 for SQL Server};'
    'SERVER=localhost,1433;'
    'DATABASE=sales;'
    'UID=app;PWD=secret;'
    'Encrypt=yes;TrustServerCertificate=yes'
)

cnxn = pyodbc.connect(conn_str, timeout=5)
cursor = cnxn.cursor()

cursor.execute('SELECT id, email FROM users WHERE active = 1')
for row in cursor:
    print(row.id, row.email)

cnxn.close()

The DRIVER value has to match an installed driver name character for character, braces included. TrustServerCertificate=yes is only for local development: Driver 18 defaults to Encrypt=yes and will refuse a self-signed certificate without it.

Find out which ODBC drivers the machine actually haslist-installed-drivers

import pyodbc

print(pyodbc.drivers())
# ['ODBC Driver 18 for SQL Server', 'PostgreSQL Unicode']

print(pyodbc.dataSources())
# {'prod': 'ODBC Driver 18 for SQL Server'}

Run this first whenever a connection fails. An empty list means the driver manager sees nothing, which is the real cause of most Data source name not found and no default driver specified errors. On Windows the list is specific to the bitness of the Python you are running.

Pass parameters with qmark placeholdersparameterized-query

cursor.execute(
    'SELECT id FROM users WHERE email = ? AND active = ?',
    email, 1,
)
row = cursor.fetchone()

# a sequence works too
cursor.execute('SELECT id FROM users WHERE email = ? AND active = ?', (email, 1))

# IN lists need placeholders built by hand
ids = [3, 7, 11]
marks = ','.join('?' * len(ids))
cursor.execute(f'SELECT * FROM users WHERE id IN ({marks})', *ids)

Only the qmark style is supported; %s and named parameters raise. Placeholders cannot stand in for table or column names, and an IN list needs one marker per value, which is the one place you legitimately build SQL with an f-string.

Read columns by name instead of positionrow-access-by-name

cursor.execute('SELECT id, email AS user_email, count(*) AS n FROM users GROUP BY id, email')
row = cursor.fetchone()

row.user_email      # by attribute
row[1]              # by position
tuple(row)          # plain tuple
[c[0] for c in cursor.description]   # column names

row.user_email = row.user_email.lower()   # values can be replaced

Row is tuple-like, not dict-like: row['user_email'] raises TypeError because subscripting only accepts integers and slices. Names come from the result set, so computed columns need an AS alias or they arrive nameless.

Turn result rows into dictionariesrows-as-dicts

cursor.execute('SELECT id, email FROM users')
columns = [c[0] for c in cursor.description]
rows = [dict(zip(columns, r)) for r in cursor.fetchall()]

# scalar shortcut, no dict needed
total = cursor.execute('SELECT count(*) FROM users').fetchval()

There is no dict cursor. A patch adding dictionary rows was merged to master in June 2026 but has not appeared in a release yet (5.3.0 is from October 2025), so zipping cursor.description is still the answer. Duplicate column names from a join collapse when you build the dict.

Commit, roll back, and know when autocommit bitestransactions-and-commit

cnxn = pyodbc.connect(conn_str)   # autocommit=False by default
try:
    cnxn.execute('UPDATE accounts SET bal = bal - ? WHERE id = ?', 100, 1)
    cnxn.execute('UPDATE accounts SET bal = bal + ? WHERE id = ?', 100, 2)
    cnxn.commit()
except pyodbc.DatabaseError:
    cnxn.rollback()
    raise

# the with block commits on clean exit, rolls back on exception
with cnxn:
    cnxn.execute('DELETE FROM sessions WHERE expires < ?', cutoff)

pyodbc never commits for you: closing the connection, or letting it be garbage collected, throws away everything uncommitted. The with block commits or rolls back but does not close the connection, which trips up people expecting file-handle semantics.

Insert many rows without a round trip per rowbulk-insert-fast-executemany

rows = [(1, 'a@example.com'), (2, 'b@example.com')]

cursor = cnxn.cursor()
cursor.fast_executemany = True
cursor.executemany('INSERT INTO users (id, email) VALUES (?, ?)', rows)
cnxn.commit()

fast_executemany is off by default, and without it executemany is literally a loop of execute calls with no speed win. The wiki recommends it only for Microsoft's SQL Server driver, all parameters are buffered in memory so chunk very large loads, and rowcount is not populated after executemany either way.

Call a stored procedure and read output parameterscall-stored-procedure

sql = '''
SET NOCOUNT ON;
DECLARE @out nvarchar(max);
EXEC dbo.rename_user @param_in = ?, @param_out = @out OUTPUT;
SELECT @out AS the_output;
'''

cursor.execute(sql, ('Dinsdale',))
while True:
    print(cursor.fetchall())
    if not cursor.nextset():
        break

pyodbc does not implement callproc, so output parameters need this anonymous block workaround. Leave out SET NOCOUNT ON and the row counts emitted inside the procedure come back as extra result sets, which is where the No results. Previous SQL was not a query error comes from.

Catch errors and read the SQLSTATEhandle-database-errors

try:
    cursor.execute('INSERT INTO users (email) VALUES (?)', email)
    cnxn.commit()
except pyodbc.IntegrityError as exc:
    sqlstate, message = exc.args[0], exc.args[1]
    cnxn.rollback()
    raise Conflict(f'{email} already exists ({sqlstate})') from exc
except pyodbc.OperationalError as exc:
    if exc.args[0] in ('HYT00', 'HYT01'):
        raise Timeout('query timed out') from exc
    raise
except pyodbc.Error as exc:
    log.exception('odbc failure: %s', exc.args[0])

args[0] is the five-character SQLSTATE and args[1] is whatever text the driver produced, native error number included. The SQLSTATE is portable, the message is not, so never match on message text if you support more than one database.

Set the two different timeoutsquery-and-login-timeouts

cnxn = pyodbc.connect(conn_str, timeout=5)   # login timeout, seconds
cnxn.timeout = 30                            # query timeout for every cursor

cnxn_ro = pyodbc.connect(conn_str, readonly=True, autocommit=True)

These are two separate settings that share a name: connect(timeout=) limits establishing the connection, cnxn.timeout limits each query, and both default to 0 meaning no limit. A query timeout arrives as OperationalError with SQLSTATE HYT00 or HYT01, and not every driver honors either value.

Decode a type ODBC does not map, such as DATETIMEOFFSEToutput-converter-for-vendor-types

import struct
from datetime import datetime, timedelta, timezone

def handle_datetimeoffset(value):
    tup = struct.unpack('<6hI2h', value)
    return datetime(
        tup[0], tup[1], tup[2], tup[3], tup[4], tup[5], tup[6] // 1000,
        timezone(timedelta(hours=tup[7], minutes=tup[8])),
    )

cnxn.add_output_converter(-155, handle_datetimeoffset)
value = cursor.execute('SELECT dto_col FROM events WHERE id = 1').fetchval()

Without the converter a SQL Server DATETIMEOFFSET column raises ODBC SQL type -155 is not yet supported. Converters are registered per connection, receive raw bytes (or None for NULL), and have to be re-registered on every new connection, so put this in a connection factory.

Hand the ODBC connection string to SQLAlchemy for pandasuse-with-sqlalchemy-and-pandas

import urllib.parse
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(
    'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(conn_str),
    fast_executemany=True,
)

df = pd.read_sql('SELECT * FROM users', engine)
df.to_sql('users_copy', engine, if_exists='append', index=False)

pandas only supports SQLAlchemy connectables and sqlite3; passing a raw pyodbc connection still works but emits a UserWarning and is unsupported. Wrapping the whole string in odbc_connect avoids escaping the braces and semicolons that break the normal URL form.

Alternatives

PackageRegistryPick it when
mssql-pythonPyPIYou are on SQL Server or Azure SQL and want Microsoft's newer first-party driver, which bundles its own connectivity layer so there is no separate ODBC driver manager to install
sqlalchemyPyPIYou want connection pooling, an engine abstraction, and optionally an ORM on top; its mssql+pyodbc dialect still runs pyodbc underneath
pymssqlPyPISQL Server only, and skipping the ODBC install entirely is worth giving up features and a much smaller community
aioodbcPyPIYour app is asyncio and you accept that the ODBC calls are simply being moved onto a thread pool executor