数据集层级递归：基于BFS实现SQL依赖案例ID查询修正

Got it, let's break down what's going wrong and fix this properly.

First, that pyodbc error happens because your original code creates a nested list, which messes up SQL parameter passing:

• After the first iteration, you append first_tuple (a list of dependent IDs) directly to cms_id_list. So your list becomes something like [1, [2,3]].
• When the loop hits that nested list [2,3], you pass it as the parameter to the query. Pyodbc treats each element in the list as a separate parameter, but your query only has one ? marker—hence the "1 parameter marker, 2 parameters supplied" crash.

BFS is exactly the right approach here for traversing all those dependencies without nesting. Here's the corrected code that will give you a nice flat list like [1,2,3,4,5]:

import pyodbc

# Set up your starting case ID and tracking tools
target_case_id = 1
visited_ids = set()
bfs_queue = [target_case_id]

# Replace these with your actual database connection details
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_pwd')
cursor = conn.cursor()

while bfs_queue:
    current_id = bfs_queue.pop(0)  # BFS uses FIFO; use pop() instead for DFS if you prefer
    if current_id in visited_ids:
        continue
    visited_ids.add(current_id)
    
    # Fetch all distinct dependent IDs for the current case
    cursor.execute('SELECT distinct dep_cms_id FROM bfs_Test WHERE cms_id = ?', current_id)
    dependent_tuples = cursor.fetchall()
    
    # Process each dependent ID
    for dep_tuple in dependent_tuples:
        dep_id = dep_tuple[0]
        # Stop when we hit 0, and skip IDs we've already processed
        if dep_id != 0 and dep_id not in visited_ids:
            bfs_queue.append(dep_id)
            visited_ids.add(dep_id)

# Convert the visited set to a sorted list (remove sorted() if order doesn't matter)
flat_dependency_list = sorted(visited_ids)
print(flat_dependency_list)  # Outputs [1,2,3,4,5] as you wanted

# Always clean up database connections
cursor.close()
conn.close()

What makes this code work better: #

• Queue-based BFS: We process each ID one at a time, no nested lists to break the SQL parameter passing.
• Visited set: Prevents duplicate IDs from being processed multiple times (even with DISTINCT in SQL, this adds extra safety for cyclic dependencies if they exist).
• Explicit 0 check: Stops traversal as soon as we hit a dependency ID of 0, which is your base case.
• Proper connection handling: Makes sure we close the cursor and connection when done to avoid resource leaks.

Here's a quick walkthrough of the flow:

1. Start with your target ID in the queue.
2. Pull an ID from the queue, mark it as visited, then fetch its dependencies.
3. Add any valid (non-0, unvisited) dependencies to the queue.
4. Repeat until the queue is empty—meaning we've traversed all possible dependencies.
5. Convert the visited set to a list for your flat output.

This should fix both the parameter error and give you the flattened dependency list you need.

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