Get the list of mysql users

Overview:

  • The user table of mysql database of a MySQL server has columns for users and their privileges.
  • Querying the database.user table retrieves the list of users of a MySQL database server.
  • pymysql is a Python library which is used to connect to a MySQL database server from a Python Program.
  • Pymysql is developed fully in Python and is compliant to Python Database API specification version 2.0.

 

Querying the list of mysql users from a Python Program:

  • A database connection is established by calling the connect method of the pymysql module and passing the parameters which include the IP address of MySQL Server, MySQL user name, password and the name of the database to connect to.
  • A cursor object is retrieved from the connection object, which is used to execute the database query to get the list of users from the MySQL server.
  • Once the query is executed, the query results are obtained by calling the fetchall() method of the cursor object.

 

Example:

# import the mysql client for Python

import pymysql

 

# Connect to the MySQL Database Server

dbIPAddresss    = "127.0.0.1"

dbUsr          = "root"

dbPwd                = ""

dbName          = "mysql"

dbCharset       = "utf8mb4"

cursorType      = pymysql.cursors.DictCursor

 

try:

 

    mySQLConnection   = pymysql.connect(host=dbIPAddresss,

                                    user=dbUsr,

                                    password=dbPwd,

                                    db=dbName,

                                    charset=dbCharset,

                                    cursorclass=cursorType,

                                    autocommit=True)

   

    # Get a cursor object from the connection

    cursorObject    = mySQLConnection.cursor()                                    

    getUserSQL       = "select user from user group by user"

 

    # Execute the SQL query for retrieving the user list from MySQL

    cursorObject.execute(getUserSQL)

 

    # Execute the SQL SELECT query

    cursorObject.execute(getUserSQL)

 

    # Fetch all the user records

    sqlUsers = cursorObject.fetchall()

 

    # Print the MySQL user name

    for user in sqlUsers:

        print(user)     

 

except Exception as e:

    print("Exeception occured:{}".format(e))

 

finally:

    mySQLConnection.close()

 

Output:

{'user': 'newuser'}

{'user': 'root'}

{'user': 'user1'}

 


Copyright 2024 © pythontic.com