Delete Data From MySQL in Python – Delete rows, columns from MySQL Table

This Python tutorial is focused on Delete data from MySQL Database in Python programming. That means we are going to learn the following things in this Python MySQL lesson.

Before starting the tutorial, please note that here we are going to use MySQL Connector/Python, This is a module or you can say a connector that can establish a connection in between your MySQL Database server and Python program. You can use other modules too. All the syntax and method of accessing the database is almost the same. So don’t worry about this.

As you are going to learn how to delete data from MySQL database in Python programming, we can assume that you already have installed MySQL Connector. And also we can assume that you know How to Connect MySQL with Python and How to create a database in MySQL using Python.

The next thing you need to create a database in MySQL using Python and create MySQL table.

If you have those things then it is fine. If you don’t have data in your MySQL table you may learn How to insert data into MySQL Table in Python Programming.

I have mentioned some hyperlinks of my previous tutorials just to confirm that you know the basics.

 



Here in this whole tutorial, let’s take the below MySQL Table

delete data from MySQL table in Python

demo example to show how to delete data from MySQL table in Python

The table name is codespeedy and the table has three columns you can see in the above picture.

Now we are going to do delete operation using MySQL delete query in Python.

Delete a row in MySQL Table using Python

In this below Python program, we will delete a row from the above-given MySQL table.

Row to be deleted: 3rd row ( That contain Python for column category )

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_your_username_here",
  passwd="put_your_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_query = "DELETE FROM codespeedy where category='Python'"

my_database.execute(sql_query)
db_connection.commit()
print("Row(s) deleted successfully!!!!")

Output:

Row(s) deleted successfully!!!!

Explanation of the above program to delete a row from MySQL database table

  • Firstly, we have imported mysql.connector in our Python program.
  • Then, we have stored the hostname, username, password and database name in a variable db_connection.
  • Using the cursor() method we have created a cursor object to create a new connection.
  • in sql_query variable, we have stored the MySQL query to delete the 3rd row from our MySQL table codespeedy.
    • DELETE FROM codespeedy where category='Python'
  • Finally, execute() method in Python executes our MySQL statement to delete a row from MySQL database table.
  • Don’t forget to use commit() otherwise your deletion will not work.
    • actually, commit() method must be used to every DML statement in order to save your work. Otherwise, it will not be saved in your database.

Now, after executing our Python program, the table will look like this:

delete data from MySQL table in Python

MySQL table after executing the Python program to delete a row from MySQL table in Python

 

How to delete columns from MySQL table in Python

Suppose, we don’t need a column anymore in our MySQL table. Thus we want a table to delete from our table. In order to do that we can use almost the same program as we did for the previous one. But here we need to change the MySQL query.

For example,

We have to delete the column “duration” from our table.

Python program to delete a column from a MySQL table

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_your_username_here",
  passwd="put_your_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_query = "ALTER TABLE codespeedy DROP COLUMN duration"

my_database.execute(sql_query)
db_connection.commit()
print("column deleted successfully!!!")

Output:

column deleted successfully!!!

The table will now look like this:

delete column from mysql table in Python

Table after executing the program to delete column from MySQL table in Python

Here we have simply used MySQL alter table query in our Python program to delete a column from our MySQL table

 

Delete a table from MySQL in Python

Finally, we have reached our last portion of this tutorial. Now, we are going to delete the table from our MySQL database.

Python program to delete a table from MySQL database

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="put_your_username_here",
  passwd="put_your_password_here",
  database="database_name"
)
my_database = db_connection.cursor()
sql_query = "DROP TABLE codespeedy"

my_database.execute(sql_query)
db_connection.commit()
print("Table deleted successfully!!!")

Output:

Table deleted successfully!!!

Now we can’t show you how our table looks like now! Because the table is not existing in our database anymore. RIP the table.

Here we have used MySQL DROP TABLE query to delete the table from the database.

We hope you enjoyed this Python MySQL tutorial.

 

Leave a Reply

Your email address will not be published. Required fields are marked *