Reviewed by: Sumit Sarabhai

If you’ve been writing SQL in Python, you already know the debate: positional parameters (?) or named parameters (%(name)s)? Some developers swear by the conciseness of positional. Others prefer the clarity of named. With mssql-python, you no longer need to choose – we support both. 
 
We’ve added dual parameter style support to mssql-python, enabling both qmark and pyformat parameter styles in Python applications that interact with SQL Server and Azure SQL. This feature is especially useful if you’re building complex queries, dynamically assembling filters, or migrating existing code that already uses named parameters with other DBAPI drivers.

What Are Parameter Styles? 

The DB-API 2.0 specification (PEP 249) defines several ways to pass parameters to SQL queries. The two most popular are: 

• qmark – Positional ? placeholders with a tuple/list of values. 
• pyformat – Named %(name)s placeholders with a dictionary of values.

  # qmark style 
  cursor.execute("SELECT * FROM users WHERE id = ? AND status = ?", (42, "active")) 
   
  # pyformat style 
  cursor.execute("SELECT * FROM users WHERE id = %(id)s AND status = %(status)s", 
                 {"id": 42, "status": "active"}) 

Business Requirement 

Previously, mssql-python only supported qmark. It works fine for simple queries, but as parameters multiply, tracking their order becomes error-prone: 

# Which ? corresponds to which value? 
cursor.execute( 
    "UPDATE users SET name=?, email=?, age=? WHERE id=? AND status=?", 
    (name, email, age, user_id, status) 
) 

Mix up the order and it’s easy to introduce subtle, hard to spot bugs. 

Why Named Parameters? 

• Self-documenting queries – No more guessing which ? maps to what: 

qmark — 6 parameters, which is which? 
cursor.execute( """INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?, ?)""", ("Jane", "Doe", "jane.doe@company.com", "Engineering", 95000, "2025-03-01") ) 
pyformat — every value is labeled 
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", "last_name": "Doe", "email": "jane.doe@company.com", "dept": "Engineering", "salary": 95000, "hire_date": "2025-03-01"} ) 

• Parameter reuse – Use the same value multiple times without repeating it: 

Audit log: record who made the change and when 
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": "approved", "user": "admin@company.com", "now": datetime.now(), "order_id": 5042} ) 
3 unique values, used 5 times — no duplication needed 

• Dynamic query building – Add filters without tracking parameter positions:

def search_orders(customer=None, status=None, min_total=None, date_from=None): 
    query_parts = ["SELECT * FROM orders WHERE 1=1"] 
    params = {} 
  
    if customer: 
        query_parts.append("AND customer_id = %(customer)s") 
        params["customer"] = customer 
  
    if status: 
        query_parts.append("AND status = %(status)s") 
        params["status"] = status 
  
    if min_total is not None: 
        query_parts.append("AND total >= %(min_total)s") 
        params["min_total"] = min_total 
  
    if date_from: 
        query_parts.append("AND order_date >= %(date_from)s") 
        params["date_from"] = date_from 
  
    query_parts.append("ORDER BY order_date DESC") 
    cursor.execute(" ".join(query_parts), params) 
    return cursor.fetchall() 
  
# Callers use only the filters they need 
recent_big_orders = search_orders(min_total=500, date_from="2025-01-01") 
pending_for_alice = search_orders(customer=42, status="pending") 

• Dictionary Reuse Across Queries 

The same parameter dictionary can drive multiple queries:

report_params = {"region": "West", "year": 2025, "status": "active"} 
  
# Summary count 
cursor.execute( 
    """SELECT COUNT(*) FROM customers 
       WHERE region = %(region)s AND status = %(status)s""", 
    report_params 
) 
total = cursor.fetchone()[0] 
  
# Revenue breakdown 
cursor.execute( 
    """SELECT department, SUM(revenue) 
       FROM sales 
       WHERE region = %(region)s AND fiscal_year = %(year)s 
       GROUP BY department 
       ORDER BY SUM(revenue) DESC""", 
    report_params 
) 
breakdown = cursor.fetchall() 
  
# Top performers 
cursor.execute( 
    """SELECT name, revenue 
       FROM sales_reps 
       WHERE region = %(region)s AND fiscal_year = %(year)s AND status = %(status)s 
       ORDER BY revenue DESC""", 
    report_params 
) 
top_reps = cursor.fetchall() 
# Same dict, three different queries — change the filters once, all queries update 

The Solution: Automatic Detection 

mssql-python now detects which style you’re using based on the parameter type: 

• tuple/list → qmark (?) 

• dict → pyformat (%(name)s) 

No configuration needed. Existing qmark code requires zero changes. 

from mssql_python import connect 
 
# qmark - works exactly as before 
cursor.execute("SELECT * FROM users WHERE id = ?", (42,)) 
 
# pyformat - just pass a dict! 
cursor.execute("SELECT * FROM users WHERE id = %(id)s", {"id": 42})

How It Works 

When you pass a dict to execute(), the driver: 

1. Scans the SQL for %(name)s placeholders (context-aware – skips string literals, comments, and bracketed identifiers). 
2. Validates that every placeholder has a matching key in the dict. 
3. Builds a positional tuple in placeholder order (duplicating values for reused parameters). 
4. Replaces each %(name)s with ? and sends the rewritten query to ODBC. 

User Code                                  ODBC Layer 
─────────                                  ────────── 
cursor.execute(                            SQLBindParameter(1, "active") 
  "WHERE status = %(status)s               SQLBindParameter(2, "USA") 
   AND country = %(country)s",      →      SQLExecute( 
  {"status": "active",                       "WHERE status = ? 
   "country": "USA"}                          AND country = ?" 
)                                          ) 

The ODBC layer always works with positional ? placeholders. The pyformat conversion is purely a developer-facing convenience with zero overhead to database communication. 

Clear Error Messages 

Mismatched styles or missing parameters produce actionable errors – not cryptic database exceptions: 

cursor.execute("WHERE id = %(id)s AND name = %(name)s", {"id": 42}) 
# KeyError: Missing required parameter(s): 'name'. 
 
cursor.execute("WHERE id = ?", {"id": 42}) 
# TypeError: query uses positional placeholders (?), but dict was provided. 
 
cursor.execute("WHERE id = %(id)s", (42,)) 
# TypeError: query uses named placeholders (%(name)s), but tuple was provided.

Real-World Examples 

Example 1: Web Application

def add_user(name, email): 
    with connect(connection_string) as conn: 
        with conn.cursor() as cursor: 
            cursor.execute( 
                "INSERT INTO users (name, email) VALUES (%(name)s, %(email)s)", 
                {"name": name, "email": email} 
            ) 

Example 2: Batch Operations 

cursor.executemany( 
    "INSERT INTO users (name, age) VALUES (%(name)s, %(age)s)", 
    [{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}] 
) 

Example 3: Financial Transactions 

def transfer_funds(from_acct, to_acct, amount): 
    with connect(connection_string) as conn: 
        with conn.cursor() as cursor: 
            cursor.execute( 
                "UPDATE accounts SET balance = balance - %(amount)s WHERE id = %(id)s", 
                {"amount": amount, "id": from_acct} 
            ) 
            cursor.execute( 
                "UPDATE accounts SET balance = balance + %(amount)s WHERE id = %(id)s", 
                {"amount": amount, "id": to_acct} 
            ) 
    # Automatic commit on success, rollback on failure 

Things to Keep in Mind 

• Don’t mix styles in one query. Use either ? or %(name)s, not both. The driver determines which style you’re using from the parameter type (tuple vs dict), not from the SQL text. If placeholders don’t match the parameter type, you’ll get a clear TypeError explaining the mismatch. If both placeholder types appear in the SQL, only one set gets substituted, leading to parameter count mismatches at execution time. 

# Mixing styles - raises TypeError 
cursor.execute( "SELECT * FROM users WHERE id = ? AND name = %(name)s", {"name": "Alice"} # Driver finds %(name)s but also sees unmatched ? ) 
# ODBC error: parameter count mismatch (2 placeholders, 1 value) 
# Pick one style and use it consistently 
cursor.execute( "SELECT * FROM users WHERE id = %(id)s AND name = %(name)s", {"id": 42, "name": "Alice"} ) 

• Extra dict keys are OK.  Unused parameters are silently ignored, this is by design to enable parameter dictionary reuse across different queries. 

• SQL injection safe. Both styles use ODBC parameter binding under the hood. Values are never interpolated into the SQL string, they are always safely bound by the driver. 

• Literal % in SQL. Use %% to escape if you need a literal %(…)s pattern in your query text. 

cursor.execute( 
    "SELECT * FROM users WHERE name LIKE %(pattern)s", 
    {"pattern": "%alice%"}  # The % inside the VALUE is fine 
) 
 
# But if you need a literal %(...)s in SQL text itself, use %% 
cursor.execute( 
    "SELECT '%%(example)s' AS literal WHERE id = %(id)s", 
    {"id": 42} 
)  

• mssql_python.paramstyle reports “pyformat”. The DB-API 2.0 spec only allows a single value for this module-level constant. We set it to pyformat because it’s the more expressive style and the one we recommend for new code. But qmark is fully supported at runtime, the driver accepts both styles transparently based on whether you pass a tuple or a dict. Think of paramstyle = “pyformat” as the advertised default, not a limitation. 

Compatibility at a Glance 

Takeaway 

Use ? for quick, simple queries. Use %(name)s for complex, multi-parameter queries where clarity and reuse matter. You don’t have to pick a side – use whichever fits the situation. The driver handles the rest. 

Whether you’re building dynamic queries, or simply want more readable SQL, dual paramstyle support makes mssql-python work the way you already think. 

Try It and Share Your Feedback! 

We invite you to:

1. Check-out the mssql-python driver and integrate it into your projects.
2. Share your thoughts: Open issues, suggest features, and contribute to the project.
3. Join the conversation: GitHub Discussions | SQL Server Tech Community.