How to Delete All Rows of a MySQL Table in Python

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

Basically, we completely clear all records from the table. This deletes all entries in the table.

The table and with all its columns and definitions still exists. However, all records in the table will be deleted.

Simply, we keep the table. However, all records in it will be erased, as if starting from a clean slate.

This can be used in instances where we want to keep the table, with all its columns and definitions, but just start with a clean slate, all records erased.

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

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

cursor.execute("TRUNCATE TABLE table_name")

So, the code above deletes all rows from the table, Table_name. This erases all records of the table, leaving the table completely blank of any entries.

Below, we show the full code to delete all rows from the table named Table_name.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("TRUNCATE TABLE table_name") db.close()

So, this is all the code that is needed to delete all rows from the table named Table_name.

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 all rows from the table, Table_name, using the cursor.execute() function. Inside of this function, we place in the line, "TRUNCATE TABLE table_name". So the whole line of code, is, cursor.execute("TRUNCATE TABLE table_name")

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 '0' will be returned in the Python shell.

And this is we can erase all entries 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