Python中PyMySQL执行DELETE * FROM语句无效问题排查

Let's cut straight to the issue—your SQL syntax is invalid, which is why the delete operation isn't doing anything (even though you didn't see an error message at first glance).

Your current query looks like this:

delete * from student

But in MySQL (and most standard SQL dialects), the DELETE statement does not use an asterisk (*). The correct syntax to delete all records from your student table is:

DELETE FROM student

If you only want to delete specific records, add a WHERE clause to target them:

DELETE FROM student WHERE age < 18

Why didn't you see an error? #

Looking at your Python code, you have a try-except block that rolls back the transaction if anything goes wrong—but you aren't printing or logging the actual exception. When the invalid SQL ran, it triggered an error, your code quietly rolled back the change, and you never got notified of what broke. That's why it seemed like "no error, but no records deleted".

To debug this easily in the future, add a line to print the error in your except block:

import pymysql
db = pymysql.connect("localhost","root","root","mydb" )
cursor = db.cursor()
sql = "DELETE FROM student"  # Fixed syntax
try:
    cursor.execute(sql)
    db.commit()
except Exception as e:
    print(f"Error details: {e}")  # This will show you what went wrong
    db.rollback()
db.close()

A quick note on using TRUNCATE as an alternative #

You mentioned TRUNCATE works, and that makes sense because TRUNCATE TABLE student is valid syntax. Just keep in mind the key differences between TRUNCATE and DELETE:

• TRUNCATE resets auto-increment counters on the table, while DELETE leaves them as-is
• TRUNCATE is faster for deleting all records (it doesn't log individual row deletions)
• In some transaction contexts, TRUNCATE can't be rolled back (though InnoDB supports rolling back TRUNCATE if you're using transactions)

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