DELETE SQL query is used to delete any record from MySQL table.
The syntax for the same is given below:
DELETE FROM table_name WHERE condition
Note: In the above syntax,
WHERE clause is used to specify the condition to pin point a particular record that you want to delete. If you don't specify the condition, then all of the records of the table will be deleted.
DELETETable Data: Example
Let us see an example to delete the record in the students table (from the Python MySQL create table tutorial) whose rollno is 4. The code is given below:
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yoursername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() ## defining the Query query = "DELETE FROM students WHERE Rollno = 4" ## executing the query cursor.execute(query) ## final step to tell the database that we have changed the table data db.commit()
If the above code runs without an error then it means that the row with rollno = 4 is deleted successfully.
To check if it exists in the table or not you can use the code given below:
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() ## defining the Query query = "SELECT * FROM students" ## getting records from the table cursor.execute(query) ## fetching all records from the 'cursor' object records = cursor.fetchall() ## Showing the data for record in records: print(record)
The output for the above code is given below:
('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2) ('Amy', 'CE', 'New Delhi', 3)
Here is a snapshot of the actual output:
As we can see in the output above, the row with rollno=4 is successfully deleted.