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

 


Copyright 2024 © pythontic.com