Getting the primary key of a MySQL table through Python and PyMySQL

Overview:

Primary key of a MySQL table can be retrieved using the SQL statement show keys or by querying the database information_schema.
Note that the primary key of a table is a clustered index. Hence the SQL statements show keys or show index can be used to get this information.

Example:

# Example Python program that gets the primary key column name(s)
# of a MySQL table

import pymysql

# Connect with the DB Server
ip2Connect = "127.0.0.1"
dbUsr = "root"
dbUsrPwd = ""

tableName = "test.usr_login"
cusrorType = pymysql.cursors.DictCursor

connectionInstance = pymysql.connect(host=ip2Connect, user=dbUsr, password=dbUsrPwd,
cursorclass=cusrorType)

try:
# Get cursor from connection
cur = connectionInstance.cursor()

# SQL Show statement to get primary key
sqlStatement = "SHOW keys FROM " + tableName + " WHERE Key_name = 'PRIMARY'"

# Execute the SHOW keys statement
cur.execute(sqlStatement)

# Fetch all the rows
primaryKeys = cur.fetchall()

for primaryKey in primaryKeys:
print(primaryKey)

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

finally:
connectionInstance.close()

Output:

{'Table': 'usr_login', 'Non_unique': 0, 'Key_name': 'PRIMARY', 'Seq_in_index': 1, 'Column_name': 'usr', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': '', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': ''}

{'Table': 'usr_login', 'Non_unique': 0, 'Key_name': 'PRIMARY', 'Seq_in_index': 2, 'Column_name': 'ts', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': '', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': ''}