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