递归调用block_unblock函数后旧输入值残留引发数据库错误的问题求助

我写了一个叫block_unblock的函数，用来修改MySQL表password里ABlocked字段的值。为了防止用户输入带空格的ID，我打算在检测到空格时重新调用这个函数。但问题来了，当我输入正确的ID完成操作后，之前那些带空格的旧输入值好像还在，导致后续出现数据库错误，而且程序会重复执行操作步骤。

我的代码 #

def block_unblock():
    try:
        while True:
            user = input("Enter the desired ID: ")
            if " " in user:
                print("The ID should not contain spaces")
                block_unblock()

            # Check if the user exists in the PASSWORD table using a parameterized query
            cur.execute("SELECT EXISTS(SELECT 1 FROM password WHERE ID = %s)", (user,))
            result = cur.fetchone()

            if result[0] == 0:  # If the user is NOT IN PASSWORD list.
                print("User not found in the list.")
                continue

            # Check if the user exists in the PASSWORD table using a parameterized query
            cur.execute("SELECT EXISTS(SELECT 1 FROM password WHERE ID = %s)", (user,))
            result = cur.fetchone()

            if result[0] == 0:  # If the user is NOT IN PASSWORD list.
                print("User not found...")
                again = input("What would you like to do from here?\n1: Try another ID\n2: Exit\n")
                again = numinput_taker(again, "Please provide a valid input", ['1', '2'])
                # If the user input was 1 it will loop from start.
                if again == 2:
                    break
            elif result[0] == 1:  # If the user IS IN PASSWORD list.
                opr = input("Type:\n1: Unblock\n2: Block\n")
                opr = numinput_taker(opr, "Please provide a valid input", ['1', '2'])

                if opr == 1:  # Unblocking
                    cur.execute("SELECT ABlocked FROM password WHERE ID = %s", (user,))
                    result = cur.fetchone()
                    if result[0] == "n":  # If that user currently IS NOT BLOCKED.
                        print("This user is not blocked")
                        again = input("What would you like to do from here?\n1: Try another ID\n2: Exit\n")
                        again = numinput_taker(again, "Please provide a valid input", ['1', '2'])
                        if again == 2:
                            break
                    elif result[0] == "y":  # If that user IS currently BLOCKED.
                        cur.execute("UPDATE password SET ABlocked = 'n' WHERE ID = %s", (user,))
                        connection.commit()
                        print("The user has been unblocked.")
                        break

                elif opr == 2:  # Blocking
                    cur.execute("SELECT ABlocked FROM password WHERE ID = %s", (user,))
                    result = cur.fetchone()
                    if result[0] == "y":  # If that user currently IS BLOCKED.
                        print("This user is already blocked")
                        again = input("What would you like to do from here?\n1: Try another ID\n2: Exit\n")
                        again = numinput_taker(again, "Please provide a valid input", ['1', '2'])
                        if again == 2:
                            break
                    elif result[0] == "n":  # If that user is currently NOT BLOCKED.
                        cur.execute("UPDATE password SET ABlocked = 'y' WHERE ID = %s", (user,))
                        connection.commit()
                        print("The user has been blocked.")
                        break
    except ms.Error as e:
        print("Error connecting to the database:", e)

程序输出 #

Enter the desired ID: 1 2
The ID should not contain spaces
Enter the desired ID: 3 4
The ID should not contain spaces
Enter the desired ID: 1
Type:
1: Unblock
2: Block
2
The user has been blocked.
Type:
1: Unblock
2: Block
2
Error connecting to the database: 1292 (22007): Truncated incorrect DOUBLE value: ' 3 4'
Type:
1: Unblock
2: Block
2
This user is already blocked
What would you like to do from here?
1: Try another ID
2: Exit

问题分析与解决办法 #

问题根源

你遇到的是递归调用导致的函数栈残留问题：当输入带空格的ID时，你调用block_unblock()会开启一个新的函数实例，但原来的函数实例并没有终止——它会在新实例执行完后，继续执行原来的代码逻辑，这时候user变量还是原来带空格的值，所以会继续去执行数据库操作，最终触发错误。

修复方案

别用递归，直接用continue回到循环开头重新获取输入就好了，修改这段代码：

if " " in user:
    print("The ID should not contain spaces")
    continue  # 回到循环开头，重新获取输入

这样就不会产生多个函数实例，每次输入错误后都会重新开始循环，使用新的输入值，旧的错误输入不会残留。

另外，你的代码里还有重复的用户存在性检查，可以删掉其中一次，减少不必要的数据库查询，让代码更简洁。

备注：内容来源于stack exchange，提问作者Riku