Retrieve the structure of a MySQL table from a Python Program

A MySQL table consists of one are more columns of varying types like integer, text, date and others. The PyMySQL module enables retrieving metadata of a table by sending the Describe command from a Python program.

MySQL Updated 16 Aug 2020 1 min read

Overview

  • The metadata or schema of a MySQL table is provided by the SQL statement DESCRIBE.
  • The metadata includes name of the columns, their types, key constraints and other information about each column of a MySQL table.
  • The DESCRIBE statement can also be made to provide information about select column(s) of a table by using the wildcard characters % and -.
  • A Python program can execute a DESCRIBE statement on a MySQL Server using the PyMySQL - a Python based MySQL client library.
  • The example python program creates a connection to an SQL Server instance and obtains a cursor object through which the DESCRIBE statement is executed.

Example

python
# import the mysql client library

            import pymysql

# Connection creation

            serverIP            = "127.0.0.1"

            serverUser          = "test"

            serverUserPwd       = "phraseone"

            characterSet        = "utf8mb4"

            cursorType          = pymysql.cursors.DictCursor

mySQLConnection   = pymysql.connect(host=serverIP,

                                                user=serverUser,

                                                password=serverUserPwd,

                                                charset=characterSet,

                                                cursorclass=cursorType)

try:

                # Obtain a cursor object

                cursorObject    =

mySQLConnection.cursor()

    # Execute DESCRIBE statement

                cursorObject.execute("DESCRIBE test.UserList")

    # Fetch and print the meta-data of the table

                indexList = cursorObject.fetchall()

    print(indexList)

except Exception as e:

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

finally:

                mySQLConnection.close()

Output

Output
[{'Field': 'User_Name', 'Type': 'varchar(24)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}, {'Field': 'User_Password', 'Type': 'varchar(24)', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}, {'Field': 'User_Since', 'Type': 'timestamp', 'Null': 'YES', 'Key': '', 'Default': 'CURRENT_TIMESTAMP', 'Extra': 'DEFAULT_GENERATED'}, {'Field': 'User_Level', 'Type': 'int', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}, {'Field': 'User_Report_Count', 'Type': 'int', 'Null': 'YES', 'Key': '', 'Default': None, 'Extra': ''}]