如何根据参数数量与类型动态生成存储过程调用的参数定义字符串？

I've got you covered! Here's a clean implementation of the generate_args_on_fly function that exactly meets your requirements, along with usage examples tailored to your stored procedure call scenario.

Function Implementation #

def generate_args_on_fly(args_dict: dict) -> str:
    arg_components = []
    for param, dtype in args_dict.items():
        if dtype == "string":
            # Wrap string placeholders in single quotes for SQL syntax
            arg_components.append(f"'{{{param}}}'")
        elif dtype == "number":
            # Numeric types don't need quotes
            arg_components.append(f"{{{param}}}")
        else:
            # Handle unsupported types to avoid invalid SQL
            raise ValueError(f"Unsupported data type '{dtype}' for parameter '{param}'")
    
    # Join components into a parenthesized argument string
    return f"({', '.join(arg_components)})"

How to Use It #

Let's walk through your example scenario step by step:

1. Define your parameter type dictionary (pulled from your config file):

args_config = {"id": "number", "name": "string"}

2. Generate the argument template:

arg_template = generate_args_on_fly(args_config)
# arg_template will be: "({id}, '{name}')"

3. Build your full stored procedure call string:

schema = "your_schema"
stored_proc = "your_stored_procedure"
call_base = f"call {schema}.{stored_proc}"
full_call_template = f"{call_base}{arg_template}"
# full_call_template becomes: "call your_schema.your_stored_procedure({id}, '{name}')"

4. Inject actual parameter values to get the final executable SQL:

actual_params = {"id": 101, "name": "John Doe"}
final_sql = full_call_template.format(**actual_params)
# final_sql will be: "call your_schema.your_stored_procedure(101, 'John Doe')"

Key Details #

• Double Curly Braces: The {{{param}}} syntax in the f-string creates a single curly brace placeholder ({param}) in the output, which is then usable with str.format() later to inject real values.
• SQL Syntax Compliance: String parameters are wrapped in single quotes, matching standard SQL syntax for string literals to ensure your stored procedure call is valid.
• Error Handling: The function raises a ValueError if it encounters an unrecognized data type, preventing you from generating invalid SQL accidentally. You can extend this to support other types (like boolean or date) by adding more elif clauses if your use case requires it.

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