Overview
- A table in the MySQL database can be deleted using the Drop table SQL Statement.
- Deleting a table removes all the rows from the MySQL table.
- Any indexes of the table are also deleted, as the indexes will have nothing to point to after the rows of the table and the table are deleted.
Example
# Example Python program that deletes a table from a MySQL database
# import MySQL client library
import pymysql
# Database server connection details
serverIP = "127.0.0.1"
userName = "root"
pwd = "BanniInside1$"
charSet2Use = "utf8mb4"
whichCursor = pymysql.cursors.DictCursor
connection2DB = pymysql.connect(host=serverIP, user=userName, password=pwd,
charset=charSet2Use, cursorclass=whichCursor)
try:
# Create a MySQL table
dbCur = connection2DB.cursor()
createTableSQL = """create table workplace.Albums(Id INT NOT NULL AUTO_INCREMENT,
Album VARCHAR(128),
Artist VARCHAR(128),
Released DATE,
Genre VARCHAR(128),
Length TIME,
Producer VARCHAR(128),
PRIMARY KEY (Id)
)"""
dbCur.execute(createTableSQL)
showTablesSQL = "show tables in workplace"
# Print the list of tables
dbCur.execute(showTablesSQL)
tableList = dbCur.fetchall()
print(tableList)
# Delete the table
deleteTableSQL = "drop table workplace.Albums"
dbCur.execute(deleteTableSQL)
# Print the new list of tables
dbCur.execute(showTablesSQL)
tableList = dbCur.fetchall()
print(tableList)
except Exception as e:
print("Exception occured:{}".format(e))
finally:
connection2DB.close()Output
[{'Tables_in_workplace': 'Albums'}]
()