Retrieve The Structure Of A MySQL Table From A Python Program

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:

# 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:

[{'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': ''}]

 


Copyright 2020 © pythontic.com