You may have been in a situation which you may need to figure out what is the PRIMARY key field of a given table. Or even if it is a compound key what are the columns building the PRIMARY key. This usually helps a lot when you’re making some generic tools to do some jobs on a given table.

The Script is fairly easy, the following SQL command returns back all the INDEXes of a given Table :

SHOW INDEX FROM `tbl_name`

As you will see in the result, all the indexes are returned including the PRIMARY ones. The only concern is if the PRIMARY KEY is a compound key it has one entry in the list for each column.

Table           Non_unique  Key_name      Seq_in_index  Column_name
---------------------------------------------------------------------
test_table      0           PRIMARY       1             Config_ID
test_table      0           PRIMARY       2             User_ID
test_table      1           idxSettingID  1             User_ID

So you can easily loop through the results and find the compound keys.

Here is a sample Python script which does the same thing, using the List Comprehension :

if cursor.execute('SHOW INDEX FROM `test_table`') :
    return  [row[4] for row in cursor.fetchall() if row[2].upper() == 'PRIMARY']
return []

Also you can use the following SQL to get information about the table and loop through it in Python :

DESCRIBE `tbl_name`