Overview
- To remove a database from the MySQL Database Server the SQL command DROP DATABASE is used.
- The DROP DATABASE command does the following:
- Deletes all the data present in the tables, deletes the schema of the tables
- Deletes the database
- A database can be deleted from a Python Program using PyMySQL Python database client.
Remove a database using Python
- Inside a Python Program, obtain a connection object to the MySQL Database server.
- Using a cursor object obtained through the connection, execute the SQL command DROP DATABASE specifying the name of the database to be deleted.
- The example below removes an already existing database from a MySQL Server.
- Since DROP DATABASE removes all the tables and their data this command has to be executed with caution.
Example
# import the mysql client for python
import pymysql
# Create a connection object
dbIP = "127.0.0.1" # IP address of the MySQL database server
dbUserName = "root" # User name of the database server
dbUserPassword = "" # Password for the database user
databaseForDeletion = "D1" # Name of the database that is to be deleted
charSet = "utf8mb4" # Character set
cusrorType = pymysql.cursors.DictCursor
connection = pymysql.connect(host=dbIP, user=dbUserName, password=dbUserPassword,
charset=charSet,cursorclass=cusrorType)
try:
# Create a cursor object
dbCursor = connection.cursor()
# SQL Statement to delete a database
sql = "DROP DATABASE "+databaseForDeletion
# Execute the create database SQL statment through the cursor instance
dbCursor.execute(sql)
# SQL query string
sqlQuery = "SHOW DATABASES"
# Execute the sqlQuery
dbCursor.execute(sqlQuery)
#Fetch all the rows
databaseCollection = dbCursor.fetchall()
for datatbase in databaseCollection:
print(datatbase)
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
connection.close()Output
{'Database': 'information_schema'}
{'Database': 'NewDatabase'}
{'Database': 'mysql'}
{'Database': 'performance_schema'}
{'Database': 'test'}In the output above the database D1 is not listed as it has been deleted by the sample Python Program using PyMySQL. To create a blank database refer to the article: Create A MySQL Database Using Python And PyMySQL .