World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? How to leave/exit/deactivate a Python virtualenvironment Change the hyperparameter means ? Why is my f1_scores different when i calculate them manually vs output by sklearn.metrics What is the method for assigning a non-scalar value to a PyTorch tensor? Find the Largest and smallest items in a collection. How Python interpreter Works? Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Python MySQL - Drop Table
Neha Kumawat
a year ago
Table of Contents
- Introduction
- Removing a Table Using Python
- Drop Table Only if Exists
Introduction
You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete.
Syntax
Following is the syntax of the DROP TABLE statement in MySQL −
DROP TABLE table_name;
Example
Before deleting a table get the list of tables using the SHOW TABLES statement as follows −
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb |
+-----------------+
| contact |
| cricketers_data |
| employee |
| sample |
| tutorials |
+-----------------+
5 rows in set (0.00 sec)
Following statement removes the table named sample from the database completely −
mysql> DROP TABLE sample;
Query OK, 0 rows affected (0.29 sec)
Since we have deleted the table named sample from MySQL, if you get the list of tables again you will not find the table name sample in it.
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb |
+-----------------+
| contact |
| cricketers_data |
| employee |
| tutorials |
+-----------------+
4 rows in set (0.00 sec)
Removing a Table Using Python
You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.
To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
Example
Following table drops a table named EMPLOYEE from the database.
import mysql.connector
#establishing the connection conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb'
)
#Creating a cursor object using the cursor() method cursor = conn.cursor()
#Retrieving the list of tables print("List of tables in the database: ")
cursor.execute("SHOW Tables") print(cursor.fetchall())
#Doping EMPLOYEE table if already exists cursor.execute("DROP TABLE EMPLOYEE")
print("Table dropped... ")
#Retrieving the list of tables print(
"List of tables after dropping the EMPLOYEE table: "
)
cursor.execute("SHOW Tables") print(cursor.fetchall())
#Closing the connection
conn.close()
Output
List of tables in the database:
[('employee',), ('employeedata',), ('sample',), ('tutorials',)]
Table dropped...
List of tables after dropping the EMPLOYEE table:
[('employeedata',), ('sample',), ('tutorials',)]
Drop Table Only if Exists
If you try to drop a table which does not exist in the database, an error occurs as −
mysql.connector.errors.ProgrammingError: 1051 (42S02): Unknown table 'mydb.employee'
You can prevent this error by verifying whether the table exists before deleting, by adding the IF EXISTS to the DELETE statement.
Example
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving the list of tables
print("List of tables in the database: ")
cursor.execute("SHOW Tables")
print(cursor.fetchall())
#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
print("Table dropped... ")
#Retrieving the list of tables
print("List of tables after dropping the EMPLOYEE table: ")
cursor.execute("SHOW Tables")
print(cursor.fetchall())
#Closing the connection
conn.close()
Output
List of tables in the database:
[('employeedata',), ('sample',), ('tutorials',)]
Table dropped...
List of tables after dropping the EMPLOYEE table:
[('employeedata',), ('sample',),
('tutorials',)]
Liked what you read? Then don’t break the spree. Visit our insideAIML blog page to read more awesome articles.
Or if you are into videos, then we have an amazing Youtube channel as well. Visit our InsideAIML Youtube Page to learn all about Artificial Intelligence, Deep Learning, Data Science and Machine Learning.
Keep Learning. Keep Growing.