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