Flexible SQL Parameterization in Python: How mssql-python Supports Both Named and Positional Placeholders

If you've ever written SQL in Python, you've likely faced the choice between positional placeholders (?) and named placeholders (%(name)s). Each has its loyal advocates—positional fans love its brevity, while named fans appreciate the clarity. With mssql-python, the debate ends. This driver now supports both qmark and pyformat parameter styles, giving you the freedom to write SQL your way. Whether you're building complex queries, assembling dynamic filters, or migrating existing code from other DBAPI drivers, this dual support streamlines your workflow. Below, we answer common questions about this feature and how it can improve your Python-SQL Server interactions.

1. What parameter styles does mssql-python now support?

mssql-python follows the DB-API 2.0 specification (PEP 249) and now supports two popular parameter styles: qmark and pyformat. The qmark style uses positional ? placeholders combined with a tuple or list of values. For example: cursor.execute("SELECT * FROM users WHERE id = ? AND status = ?", (42, "active")). The pyformat style uses named placeholders like %(name)s paired with a dictionary of values: cursor.execute("SELECT * FROM users WHERE id = %(id)s AND status = %(status)s", {"id": 42, "status": "active"}). This dual support means you can choose the style that best fits your query complexity, coding preferences, or existing codebase without changing drivers.

Flexible SQL Parameterization in Python: How mssql-python Supports Both Named and Positional Placeholders — Source: devblogs.microsoft.com

2. When would you choose positional over named parameters?

Positional parameters (qmark) are ideal for simple, short queries where the order of values is obvious and unlikely to cause errors. For instance, a quick lookup like SELECT * FROM products WHERE id = ? is concise and readable. Developers who value brevity often prefer this style when writing ad-hoc scripts or straightforward SELECT/INSERT statements with only a few parameters. The syntax is lighter—no need to type out dictionary keys—and it integrates seamlessly with tuples returned by other functions. However, as the number of parameters grows, maintaining the correct order becomes tricky. That's where named parameters shine, offering self-documenting code that reduces the risk of accidentally swapping values.

3. How does named parameter style improve code readability?

Named parameters transform your SQL queries into self-documenting statements. Instead of counting ? placeholders, each parameter is explicitly labeled with a descriptive name, making the intent crystal clear. For example, compare a six‑parameter insert using qmark: cursor.execute("INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?, ?)", ("Jane", "Doe", "jane@example.com", "Engineering", 95000, "2025-03-01")) versus the same query with pyformat: cursor.execute("INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES (%(first_name)s, %(last_name)s, %(email)s, %(dept)s, %(salary)s, %(hire_date)s)", {"first_name": "Jane", ...}). The named version immediately tells you which value corresponds to which column, drastically reducing the chance of mixing up arguments. This becomes especially valuable in team settings where code reviews benefit from explicit labeling.

4. Can you reuse the same named parameter multiple times in a single query?

Absolutely—this is one of the standout advantages of named parameters. With pyformat, you can reference the same key in multiple places within a query, and you only need to pass the value once in the dictionary. For example, in an audit log update you might need to record the same user and timestamp for both modified_by and approved_by: 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""", {...}). This not only reduces code duplication but also ensures consistency—changing the user or time in one place updates all occurrences. Positional parameters would require you to repeat the same value multiple times in the tuple, making the query longer and harder to maintain.

5. How does dual parameter style help when migrating code from other drivers?

Many Python DBAPI drivers, such as psycopg2 (PostgreSQL) or mysql‑connector, default to named parameters. If you're moving an existing application from another database to SQL Server or Azure SQL, your code likely contains many %(name)s placeholders. Previously, you'd need to rewrite all those queries to use ? before switching to mssql‑python. With the new dual support, you can keep your existing pyformat queries exactly as they are—no manual conversion needed. This dramatically reduces migration effort and risk. Likewise, if you're porting code from a driver that only supports qmark, mssql‑python retains backward compatibility. This flexibility means you can adopt mssql‑python incrementally, mixing and matching parameter styles within the same project if desired.

6. What business requirement drove the addition of both parameter styles?

The decision stemmed from real‑world pain points. Initially, mssql‑python only supported qmark (positional). For simple queries that was fine, but as applications grew, developers faced increasingly complex SQL statements with many parameters. Tracking the order of a dozen ? placeholders became error‑prone—one misplaced value could cause subtle data corruption or runtime errors. Named parameters solve this by making each placeholder explicit and reusable. The business need was clear: developers demanded a safer, more maintainable way to handle dynamic queries. By supporting both styles, mssql‑python empowers teams to choose the approach that best matches their project complexity while easing the transition from other database systems that already rely on named parameters.

7. How can you start using dual parameter styles in mssql‑python?

Getting started is simple. First, install the latest version of mssql‑python using pip: pip install mssql-python. Once installed, you can immediately use either parameter style in your cursor.execute calls. For positional, pass a tuple: cursor.execute("SELECT * FROM users WHERE id = ?", (42,)). For named, pass a dictionary: cursor.execute("SELECT * FROM users WHERE id = %(id)s", {"id": 42}). The driver automatically detects which style you're using based on the type of the second argument. There's no configuration switch needed. We encourage you to try it out and share your feedback. The mssql‑python team is actively working to improve performance and features, and community input helps us prioritize what matters most to Python + SQL Server developers.