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

The primary key columns of a MySQL table can be obtained by issuing a show keys statement. The Python example uses PyMySQL and retrieves the primary keys of a given table.

MySQL Updated 9 Nov 2021 1 min read

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

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

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