- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can you delete a record from a table using MySQL in Python?
We may at times need to delete certain rows from a table. Suppose, we have a table of details of students in the class. It is possible that one of the students left the class and hence, we do not require the details of that particular student. Hence, we need to delete that particular row or record from the table.
The “DELETE FROM” statement in MySQL is used to delete a row or record from the table, The “WHERE” clause is used to specify the row to be deleted. If WHERE clause is not used, then all the records will be deleted.
Syntax
Delete all the rows −
DELETE FROM table_name
Delete a specific row −
DELETE FROM table_name WHERE condition
Steps invloved to delete records from a table using MySQL in python
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
commit the changes made using the commit() method
close the connection
Suppose we have a table named “Student” as follows −
+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+
Example
Suppose, we have the above table of students and we want to delete the record of Kriti from the above table.
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="DELETE FROM Students WHERE Name='Kriti'" cursor.execute(query) db.commit() query="SELECT * FROM Students" cursor.execute(query) for row in cursor: print(row) db.close()
The above code deletes a row from the table and prints the remaining rowsof the table.
Output
(‘Karan’, 4 ,’Amritsar’ , 95) (‘Sahil’ , 6, ‘Amritsar’ ,93) (‘Amit’ , 9, ‘Delhi’ , 90) (‘Priya’ , 5, ‘Delhi’ ,85)
NOTE
The db.commit() in the above code is important. It is used to commit the changes made to the table. Without using commit(), no changes will be made in the table.
- Related Questions & Answers
- How can you delete a table from a database in MySQL Python?
- Delete a specific record from a MySQL table by using AND in WHERE clause
- How can you test if some record exists or not in a MySQL table using Python?
- How to delete last record (on condition) from a table in MySQL?
- How can we delete a single row from a MySQL table?
- How can we delete multiple rows from a MySQL table?
- How can we delete all rows from a MySQL table?
- How can you update certain values in a table in MySQL using Python?
- How can you select data from a table based on some criteria using MySQL in Python?
- How to delete a column from a table in MySQL?
- Delete multiple entries from a MySQL table
- Can we select second largest record from a table without using LIMIT clause in MySQL?
- How can I create a stored procedure to delete values from a MySQL table?
- Delete all records from a table in MySQL?
- How can we search a record from MySQL table having a date as a value in it?