mssql-python Breaks Python-SQL Parameter Debate: Supports Both Positional and Named Styles

Breaking: mssql-python Now Supports Both Positional and Named Parameters

The Python-SQL Server connector mssql-python has introduced dual parameter style support, ending the long-standing debate between positional (?) and named (%(name)s) placeholders. Starting immediately, developers can use either qmark or pyformat styles in their SQL queries, a move that simplifies code migration and boosts query clarity.

mssql-python Breaks Python-SQL Parameter Debate: Supports Both Positional and Named Styles — Source: devblogs.microsoft.com

“This is a game-changer for teams maintaining legacy code or building complex dynamic queries,” said Sumit Sarabhai, lead reviewer of the feature. “Developers no longer have to force one style—they can use what fits their context.” The update is available now via pip install mssql-python.

Background: The Parameter Style Divide

The Python DB-API 2.0 specification (PEP 249) defines multiple parameter styles, but two dominate: qmark (positional ?) and pyformat (named %(name)s). Positional placeholders offer conciseness, while named placeholders make queries self-documenting.

Previously, mssql-python only supported qmark. As queries grew in complexity, developers faced error-prone parameter ordering. A six-parameter insert, for example, required tracking which ? mapped to which value—a recipe for subtle bugs.

What This Means for Developers

Self-Documenting Queries with Named Parameters

Named parameters eliminate guesswork. Instead of an opaque list of values, each placeholder carries a label:

Positional style (old): cursor.execute("INSERT INTO employees (...) VALUES (?, ?, ?, ?, ?, ?)", ("Jane", ...)) — six values, unclear mapping.
Named style (new): cursor.execute("... VALUES (%(first_name)s, ...)", {"first_name": "Jane", ...}) — every value is labeled.

“Named parameters turn the query into documentation,” said Sarabhai. “You instantly know which value goes where, reducing debugging time.”

Parameter Reuse Simplifies Complex Queries

Developers can now reuse the same parameter multiple times without repeating it in the value list. For instance, an audit log that records both modified_by and approved_by as the same user becomes cleaner:

cursor.execute("""
UPDATE orders 
SET status = %(new_status)s, 
    modified_by = %(user)s, 
    approved_by = %(user)s, 
    modified_at = %(now)s, 
    approved_at = %(now)s 
WHERE order_id = %(order_id)s
""", {"new_status": "shipped", "user": "john_doe", "now": "2025-03-15", "order_id": 123})

This reduces duplication and ensures consistency across multiple columns. Previously, developers had to pass the same value multiple times in a positional tuple, risking mismatches.

Easier Migration from Other DBAPI Drivers

Many Python database drivers (e.g., psycopg2, mysql-connector-python) support named parameters natively. Teams transitioning to SQL Server can now reuse existing code without rewriting parameter styles. “Migration used to require tedious search-and-replace,” Sarabhai noted. “Now you can plug mssql-python into your existing psycopg2-style queries with minimal changes.”

Why This Matters Now

With Python’s dominance in data engineering and AI, SQL Server connectivity must keep pace. The dual parameter support addresses a top community request: flexibility without performance trade-offs. Early benchmarks show no overhead when using either style.

The mssql-python team invites developers to test the feature and report feedback. “We want to shape the future of high-performance Python-SQL Server connectivity together,” Sarabhai said.

To get started, run pip install mssql-python and see the parameter style documentation for detailed examples.

Looking Ahead

Future releases may introduce additional DBAPI-compliant styles like numeric or format, but qmark and pyformat cover the vast majority of use cases. For now, developers can write SQL their way—positional, named, or both, in the same application.

Tags: