Delete a MySQL database using Python and PyMySQL

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 .


Copyright 2024 © pythontic.com