Python Engineering at Microsoft: Write SQL Your Way: Dual Parameter Style Benefits in mssql-python

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. Try it here You can install driver using pip install mssql-python Calling all Python + SQL developers! We invite the community to try out mssql-python and help us shape the future of high-performance SQL Server connectivity in Python.! 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: Scans the SQL for (name)s placeholders (context-aware – skips string literals, comments, and bracketed identifiers). Validates that every placeholder has a matching key in the dict. Builds a positional tuple in placeholder order (duplicating values for reused parameters). 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 Feature qmark (?) pyformat ((name)s) cursor.execute() cursor.executemany() connection.execute() Parameter reuse Stored procedures All SQL data types Backward compatible with qmark paramstyle N/A (new) 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: Check-out the mssql-python driver and integrate it into your projects. Share your thoughts: Open issues, suggest features, and contribute to the project. Join the conversation: GitHub Discussions | SQL Server Tech Community. Use Python Driver with Free Azure SQL DatabaseYou can use the Python Driver with the free version of Azure SQL Database! Deploy Azure SQL Database for free Deploy Azure SQL Managed Instance for free Perfect for testing, development, or learning scenarios without incurring costs. The post Write SQL Your Way: Dual Parameter Style Benefits in mssql-python appeared first on Microsoft for Python Developers Blog.