Deleting a MySQL User from a Python Program using Pymysql

Overview:

In MySQL, the SQL statement DROP USER deletes an user from the database management system.
The DROP USER will not delete other database objects like databases, tables, stored procedures that were created by the deleted user.
The example Python program, prints the list of users present in the MySQL server. It deletes one of the users and prints the list again. The list does not include the deleted user.

Example:

# ----- Example Python program to delete an user from MySQL server -----

import pymysql

# Connection parameters and access credentials

ip = "127.0.0.1"

usr = "root"

passwd = ""

charset = "utf8mb4"

curtype = pymysql.cursors.DictCursor

# Print the list of users

def PrintUserList(sqlCursor):

sql_GetUser = "select host, user from mysql.user;"

sqlCursor.execute(sql_GetUser)

users= sqlCursor.fetchall()

print("User List:")

for userName in users:

print(userName)

# Delete an User

def DeleteUser(sqlCursor, userName):

sql_DeleteUser = "DROP USER %s;"%userName

sqlCursor.execute(sql_DeleteUser)

# Connect to MySQLServer

sqlCon = pymysql.connect(host=ip, user=usr, password=passwd, charset=charset,

cursorclass=curtype)

cursor = sqlCon.cursor()

# Print the list of MySQL users - Before deleting

print("List of users - Before deletion of an user:")

PrintUserList(cursor)

# Delete an User

userName = "'analytics'@'localhost'"

print("Deleting an User…")

DeleteUser(cursor, userName)

print("List of users - After deletion of an user:")

# Print the list of MySQL users - After deleting

PrintUserList(cursor)

Output:

List of users - Before deletion of an user:

User List:

…

{'host': 'localhost', 'user': 'analytics'}

{'host': 'localhost', 'user': 'testbed'}

…

Deleting an User…

List of users - After deletion of an user:

User List:

…

{'host': 'localhost', 'user': 'testbed'}

…