Python中SQLite3在类调用场景下DELETE操作失效问题求助

Let’s break down why your DELETE operation is being ignored, even though other SQL commands work fine. Here are the key troubleshooting steps and fixes tailored to your scenario:

Key Troubleshooting Steps #

1. Verify the ID Value & Its Type #

You’re converting self.actual_pick[i][0] to a string with str(), but if your id column in SQLite is an integer type, this could cause an unexpected mismatch. While SQLite does implicit type conversion, it’s not 100% reliable—especially if there’s hidden formatting in the value.

First, add a print statement to confirm exactly what value you’re passing to the DELETE query:

if delete:
    raw_id = self.actual_pick[i][0]
    print(f"Raw ID value: {raw_id}, Type: {type(raw_id)}")
    # ... rest of your code

If raw_id is already an integer, skip converting it to a string—pass it directly to the query. This aligns with your manual console test where using an integer ID worked.

2. Check if the DELETE Actually Matches Rows #

No errors mean your SQL is valid, but it might not be targeting any rows. After executing the DELETE, check the cursor’s rowcount attribute to see how many rows were affected:

cursor.execute("DELETE FROM db_inventory WHERE id = ?", (value_to_remove,))
print(f"Rows deleted: {cursor.rowcount}")

If rowcount is 0, your value_to_remove isn’t matching any id in the table. This points to an issue with how you’re retrieving the ID (e.g., wrong index i, or the value stored in self.actual_pick[i][0] isn’t the correct database ID).

3. Rule Out Connection/Transaction Issues #

Even with the with block (which auto-commits on exit if no exceptions occur), double-check for hidden connection conflicts:

• Are other parts of your code opening separate database connections without committing? This could lead to stale data showing in your Tkinter window even after a successful DELETE.
• Never call db.close() inside the with block—this breaks the context manager’s connection handling, leading to the ProgrammingError you encountered. The with block safely closes the connection when it exits.

Recommended Fixes #

Here’s a revised version of your delete logic with validation checks built in:

def update_inventory(self, open_web=True, update_trigger=False, sold=False, delete=False):
    # 获取用户操作需求的相关代码，包括待删除的库存项目
    if delete:
        try:
            # Get and validate the raw ID
            item_id = self.actual_pick[i][0]
            print(f"Attempting to delete item ID: {item_id}")
            
            import sqlite3
            with sqlite3.connect("MTGdb.db") as db:
                cursor = db.cursor()
                # Pass the ID directly (skip string conversion if it's an integer)
                cursor.execute("DELETE FROM db_inventory WHERE id = ?", (item_id,))
                
                affected_rows = cursor.rowcount
                if affected_rows == 0:
                    print(f"No item found with ID {item_id}")
                else:
                    print(f"Successfully deleted {affected_rows} item(s)")
                # The with block auto-commits here—no need for explicit db.commit()
        except IndexError:
            print(f"Invalid index {i} for self.actual_pick")
        except Exception as e:
            print(f"Error during delete: {str(e)}")

Final Notes #

• The most likely issue is either a mismatched ID type (string vs integer) or an incorrect ID value being pulled from self.actual_pick[i][0].
• Since your manual console test works, focus on ensuring the ID passed in the class method matches exactly what you use in the console.
• Tkinter runs in a single thread—make sure all database operations are executed in the main thread to avoid connection conflicts.

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