Create a user in MySQL using Python and PyMySQL

Overview:

Every modern database management system comes with an exhaustive user management framework to safeguard the interests of various stakeholders of the data storage.
Managing users ensures data security and defines clear roles among the database users.
MySQL as well has a robust and exhaustive user management system to ensure secure access to the data.
A MySQL User can be created through the SQL statement CREATE USER.
The CREATE USER statement accepts several parameters including
- User Name (appears after USER clause)
- Password (appears after the IDENTIFIED BY clause)
- Role (Appears after the default ROLE)
- Resource limit options
- SSL/TLS Options
By using the PyMySQL library a Python program can connect to a MySQL Server instance.

Creating a MySQL user from a Python program using PyMySQL:

The PyMySQL implements the Python Database API Specification.
A python program can establish a database connection to MySQL server by creating a Connection object.
Creating a connection object requires calling the connect() method of the PyMySQL module by specifying an existing user name and the logon credentials of the user, the character set of the database and the type of cursor.
Using a cursor object obtained from the database connection the SQL statement CREATE USER can be executed on the MySQL server.
The Python example creates two users on the MySQLServer with username and password.

Example:

# ----- Example Python program to create users in MySQL server -----

import pymysql

# Define a method to create a database connection
def getDatabaseConnection(ipaddress, usr, passwd, charset, curtype):
sqlCon = pymysql.connect(host=ipaddress, user=usr, password=passwd, charset=charset, cursorclass=curtype);
return sqlCon

# Define a method to create MySQL users
def createUser(cursor, userName, password,
querynum=0,
updatenum=0,
connection_num=0):
try:
sqlCreateUser = "CREATE USER '%s'@'localhost' IDENTIFIED BY '%s';"%(userName, password)
cursor.execute(sqlCreateUser)
except Exception as Ex:
print("Error creating MySQL User: %s"%(Ex))

# Connection parameters and access credentials
ipaddress = "127.0.0.1" # MySQL server is running on local machine
usr = "root"
passwd = ""
charset = "utf8mb4"
curtype = pymysql.cursors.DictCursor

mySQLConnection = getDatabaseConnection(ipaddress, usr, passwd, charset, curtype)
mySQLCursor = mySQLConnection.cursor()

createUser(mySQLCursor, "analytics","a$be@ter12")
createUser(mySQLCursor, "testbed", "x@ye@iog43")

mySqlListUsers = "select host, user from mysql.user;"
mySQLCursor.execute(mySqlListUsers)

# Fetch all the rows
userList = mySQLCursor.fetchall()

print("List of users:")
for user in userList:
print(user)

Output:

List of users:

...

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

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

...