Your Privateness Decisions Choose-Out Icon

What Are Parameter Types? 

The DB-API 2.0 specification (PEP 249) defines a number of methods to move parameters to SQL queries. The 2 hottest are: 

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

  # qmark model 
  cursor.execute("SELECT * FROM customers WHERE id = ? AND standing = ?", (42, "lively")) 
   
  # pyformat model 
  cursor.execute("SELECT * FROM customers WHERE id = %(id)s AND standing = %(standing)s", 
                 {"id": 42, "standing": "lively"}) 

Enterprise Requirement 

Beforehand, mssql-python solely supported qmark. It really works fantastic for easy queries, however as parameters multiply, monitoring their order turns into error-prone: 

# Which ? corresponds to which worth? 
cursor.execute( 
    "UPDATE customers SET title=?, e mail=?, age=? WHERE id=? AND standing=?", 
    (title, e mail, age, user_id, standing) 
) 

Combine up the order and it’s straightforward to introduce delicate, arduous to spot bugs. 

Why Named Parameters? 

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

qmark — 6 parameters, which is which? 
cursor.execute( """INSERT INTO staff (first_name, last_name, e mail, division, wage, hire_date) VALUES (?, ?, ?, ?, ?, ?)""", ("Jane", "Doe", "jane.doe@firm.com", "Engineering", 95000, "2025-03-01") ) 
pyformat — each worth is labeled 
cursor.execute( """INSERT INTO staff (first_name, last_name, e mail, division, wage, hire_date) VALUES (%(first_name)s, %(last_name)s, %(e mail)s, %(dept)s, %(wage)s, %(hire_date)s)""", {"first_name": "Jane", "last_name": "Doe", "e mail": "jane.doe@firm.com", "dept": "Engineering", "wage": 95000, "hire_date": "2025-03-01"} ) 

• Parameter reuse – Use the identical worth a number of instances with out repeating it: 

Audit log: report who made the change and when 
cursor.execute( """UPDATE orders SET standing = %(new_status)s, modified_by = %(person)s, approved_by = %(person)s, modified_at = %(now)s, approved_at = %(now)s WHERE order_id = %(order_id)s""", {"new_status": "authorized", "person": "admin@firm.com", "now": datetime.now(), "order_id": 5042} ) 
3 distinctive values, used 5 instances — no duplication wanted 

• Dynamic question constructing – Add filters with out monitoring parameter positions:

def search_orders(buyer=None, standing=None, min_total=None, date_from=None): 
    query_parts = ["SELECT * FROM orders WHERE 1=1"] 
    params = {} 
  
    if buyer: 
        query_parts.append("AND customer_id = %(buyer)s") 
        params["customer"] = buyer 
  
    if standing: 
        query_parts.append("AND standing = %(standing)s") 
        params["status"] = standing 
  
    if min_total isn't None: 
        query_parts.append("AND whole >= %(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(" ".be part of(query_parts), params) 
    return cursor.fetchall() 
  
# Callers use solely the filters they want 
recent_big_orders = search_orders(min_total=500, date_from="2025-01-01") 
pending_for_alice = search_orders(buyer=42, standing="pending") 

• Dictionary Reuse Throughout Queries 

The identical parameter dictionary can drive a number of queries:

report_params = {"area": "West", "yr": 2025, "standing": "lively"} 
  
# Abstract rely 
cursor.execute( 
    """SELECT COUNT(*) FROM prospects 
       WHERE area = %(area)s AND standing = %(standing)s""", 
    report_params 
) 
whole = cursor.fetchone()[0] 
  
# Income breakdown 
cursor.execute( 
    """SELECT division, SUM(income) 
       FROM gross sales 
       WHERE area = %(area)s AND fiscal_year = %(yr)s 
       GROUP BY division 
       ORDER BY SUM(income) DESC""", 
    report_params 
) 
breakdown = cursor.fetchall() 
  
# High performers 
cursor.execute( 
    """SELECT title, income 
       FROM sales_reps 
       WHERE area = %(area)s AND fiscal_year = %(yr)s AND standing = %(standing)s 
       ORDER BY income DESC""", 
    report_params 
) 
top_reps = cursor.fetchall() 
# Identical dict, three completely different queries — change the filters as soon as, all queries replace 

The Resolution: Automated Detection 

mssql-python now detects which model you’re utilizing based mostly on the parameter sort: 

• tuple/checklist → qmark (?) 

• dict → pyformat (%(title)s) 

No configuration wanted. Current qmark code requires zero modifications. 

from mssql_python import join 
 
# qmark - works precisely as earlier than 
cursor.execute("SELECT * FROM customers WHERE id = ?", (42,)) 
 
# pyformat - simply move a dict! 
cursor.execute("SELECT * FROM customers WHERE id = %(id)s", {"id": 42})

How It Works 

If you move a dict to execute(), the motive force: 

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

Consumer Code                                  ODBC Layer 
─────────                                  ────────── 
cursor.execute(                            SQLBindParameter(1, "lively") 
  "WHERE standing = %(standing)s               SQLBindParameter(2, "USA") 
   AND nation = %(nation)s",      →      SQLExecute( 
  {"standing": "lively",                       "WHERE standing = ? 
   "nation": "USA"}                          AND nation = ?" 
)                                          ) 

The ODBC layer all the time works with positional ? placeholders. The pyformat conversion is only a developer-facing comfort with zero overhead to database communication. 

Clear Error Messages 

Mismatched kinds or lacking parameters produce actionable errors – not cryptic database exceptions: 

cursor.execute("WHERE id = %(id)s AND title = %(title)s", {"id": 42}) 
# KeyError: Lacking required parameter(s): 'title'. 
 
cursor.execute("WHERE id = ?", {"id": 42}) 
# TypeError: question makes use of positional placeholders (?), however dict was supplied. 
 
cursor.execute("WHERE id = %(id)s", (42,)) 
# TypeError: question makes use of named placeholders (%(title)s), however tuple was supplied.

Actual-World Examples 

Instance 1: Net Utility

def add_user(title, e mail): 
    with join(connection_string) as conn: 
        with conn.cursor() as cursor: 
            cursor.execute( 
                "INSERT INTO customers (title, e mail) VALUES (%(title)s, %(e mail)s)", 
                {"title": title, "e mail": e mail} 
            ) 

Instance 2: Batch Operations 

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

Instance 3: Monetary Transactions 

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

Issues to Preserve in Thoughts 

• Don’t combine kinds in a single question. Use both ? or %(title)s, not each. The motive force determines which model you’re utilizing from the parameter sort (tuple vs dict), not from the SQL textual content. If placeholders don’t match the parameter sort, you’ll get a transparent TypeError explaining the mismatch. If each placeholder varieties seem within the SQL, just one set will get substituted, resulting in parameter rely mismatches at execution time. 

# Mixing kinds - raises TypeError 
cursor.execute( "SELECT * FROM customers WHERE id = ? AND title = %(title)s", {"title": "Alice"} # Driver finds %(title)s but additionally sees unmatched ? ) 
# ODBC error: parameter rely mismatch (2 placeholders, 1 worth) 
# Choose one model and use it persistently 
cursor.execute( "SELECT * FROM customers WHERE id = %(id)s AND title = %(title)s", {"id": 42, "title": "Alice"} ) 

• Further dict keys are OK.  Unused parameters are silently ignored, that is by design to allow parameter dictionary reuse throughout completely different queries. 

• SQL injection protected. Each kinds use ODBC parameter binding beneath the hood. Values are by no means interpolated into the SQL string, they’re all the time safely sure by the motive force. 

• Literal % in SQL. Use %% to escape should you want a literal %(…)s sample in your question textual content. 

cursor.execute( 
    "SELECT * FROM customers WHERE title LIKE %(sample)s", 
    {"sample": "%alice%"}  # The % contained in the VALUE is ok 
) 
 
# However should you want a literal %(...)s in SQL textual content itself, use %% 
cursor.execute( 
    "SELECT '%%(instance)s' AS literal WHERE id = %(id)s", 
    {"id": 42} 
)  

• mssql_python.paramstyle experiences “pyformat”. The DB-API 2.0 spec solely permits a single worth for this module-level fixed. We set it to pyformat as a result of it’s the extra expressive model and the one we advocate for brand new code. However qmark is totally supported at runtime, the motive force accepts each kinds transparently based mostly on whether or not you move a tuple or a dict. Consider paramstyle = “pyformat” because the marketed default, not a limitation. 

Compatibility at a Look 

Takeaway 

Use ? for fast, easy queries. Use %(title)s for advanced, multi-parameter queries the place readability and reuse matter. You don’t have to select a aspect – use whichever suits the scenario. The motive force handles the remainder. 

Whether or not you’re constructing dynamic queries, or just need extra readable SQL, twin paramstyle assist makes mssql-python work the best way you already suppose. 

Attempt It and Share Your Suggestions! 

We invite you to:

1. Examine-out the motive force and combine it into your tasks.
2. Share your ideas: Open , counsel options, and contribute to the challenge.
3. Be a part of the dialog:  | .