How to Delete a Column of a MySQL Table in Python

In this article, we show how to delete a column of a MySQL table in Python.

Say, we have a database of customer information. We have the columns, firstname, last name, email, phonenumber, and faxnumber. Now let's say that the company decides that they no longer will operate their business by faxing customers. Instead they just want to send emails. So, they have no need to keep the faxnumber column.

So, basically, we want to delete this column.

In Python, this can be easily done.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to update delete a column of a MySQL table in Python is shown below.

cursor.execute("ALTER TABLE Table_name DROP column_name")

So, the code above deletes the column, column_name, from the table, Table_name.

Going back to the example where we want to delete the column, faxnumber, from the table named Customers, we show the full code below.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("ALTER TABLE Customers DROP faxnumber") db.close()

So, this is all the code that is needed to delete the column name, faxnumber, from the Customers table.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to delete the column 'faxnumber' from the table, Customers, using the cursor.execute() function. Inside of this function, we place in the line, "ALTER TABLE Customers DROP faxnumber". So the whole line of code, is, cursor.execute("ALTER TABLE Customers DROP faxnumber")

We then close the database once we've done what we've needed to.

If the data has been successfully inserted into the MySQL table, a number will be returned in the Python shell equal to the amount of rows affected. So, if there were 5 rows of data, a '5' will be returned.

And this is how a column can be deleted from a MySQL table in Python.

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python