find primary keys of mysql table in python

To find the primary keys of a MySQL table in Python, we can use the SQLalchemy library which provides an easy-to-use interface for connecting to MySQL databases and querying metadata tables.

Here's an example code snippet that demonstrates how to find the primary keys of a MySQL table using SQLalchemy and the information_schema metadata table:

main.py
from sqlalchemy import create_engine, MetaData

# create the database engine
engine = create_engine('mysql://user:password@host:port/database')

# create a metadata object
metadata = MetaData(bind=engine)

# specify the table name
table_name = 'my_table'

# get the primary keys for the table from the information_schema table
primary_keys = []
results = engine.execute("SELECT column_name FROM information_schema.key_column_usage WHERE table_name = '{}' AND constraint_name = 'PRIMARY'".format(table_name))
for row in results:
    primary_keys.append(row[0])

# print the primary keys
print("Primary keys for table '{}': {}".format(table_name, primary_keys))
660 chars
20 lines

This code creates a database engine with the connection information, creates a metadata object to hold information about the table, specifies the table name we want to query, and then executes a SQL query against the information_schema table to retrieve the primary key columns for our specified table. Finally, we iterate over the results and append the primary key column names to a list which we print out.