如何优化PyODBC Cursor带参/无参查询执行，规避冗余分支写法？

Hey there, let's break down how to optimize this code while ditching the redundant branch logic—and fix a small misstep in the original implementation too.

First, let's call out two key issues in the original code:

1. Misused with statement: PyODBC's cursor.execute() returns the Cursor object itself, which doesn't implement the context manager protocol (no __enter__/__exit__ methods). That with block isn't doing anything useful here and could even cause unexpected behavior.
2. Redundant branch: The if/else to handle parameterless vs parameterized queries adds unnecessary code complexity.

Optimized Implementation #

Here's a cleaner version that eliminates the branch and fixes the with issue:

def query_fetch(self, sql_query, params=None, fetchall=True, *args):
    # Unify parameter handling: pass empty args if params is None, else pass params
    self.cursor.execute(sql_query, *(params or ()))
    
    # Handle fetch logic, accounting for empty results
    rows = self.cursor.fetchall() if fetchall else self.cursor.fetchone()
    
    # Return consistent list format (avoid errors if fetchone returns None)
    return list(rows) if rows is not None else []

How This Works #

• No more branches: The trick here is using Python's argument unpacking (* operator). When params is None, params or () evaluates to an empty tuple, so *() expands to nothing—making the call equivalent to cursor.execute(sql_query). When params is a valid sequence (like (val1, val2)), it gets unpacked and passed to execute, just like the original parameterized branch.
• Fixed context manager issue: We removed the unnecessary with block since execute doesn't require context management. The Cursor's lifecycle is tied to your database connection, so you don't need to wrap execute in a with here.
• More robust result handling: If fetchone returns None (no matching rows), we return an empty list instead of letting a list comprehension throw a TypeError.

Bonus: Handle Single-Valued Params #

If you might pass a single value (instead of a tuple/list) for params, you can add a small check to ensure it's always a sequence:

def query_fetch(self, sql_query, params=None, fetchall=True, *args):
    # Ensure params is always a sequence (even for single values)
    execute_params = () if params is None else (params,) if not isinstance(params, (list, tuple)) else params
    self.cursor.execute(sql_query, *execute_params)
    
    rows = self.cursor.fetchall() if fetchall else self.cursor.fetchone()
    return list(rows) if rows is not None else []

This way, passing params="some_value" will work correctly instead of throwing an error (PyODBC expects parameters to be a sequence for parameterized queries).

内容的提问来源于stack exchange，提问作者PgDn