Python SQLAlchemy – Get column names dynamically

In this article, we are going to see how to dynamically get the column names of a table in SQLAlchemy using Python.

Used database for demonstration:

Student table

So, our student table has 5 columns namely sno, name, dob, class, and section, and our task is to fetch all these column names in our Python code.

First of all, we will import the sqlalchemy module, create an engine and then create a connection with the database. Then we will execute a query on the table whose column names we want.

Example 1:

Now using the keys method of that result object obtained by running the query we can get all the column names dynamically.

Python3

from sqlalchemy import create_engine

table_name = 'student'

engine = create_engine("mysql+pymysql://root:root123@localhost/geeksforgeeks")
connection = engine.connect()

result = connection.execute(f"SELECT * FROM {table_name}")

print(result.keys())

Output:

Output of above code

Example 2:

We can also use the result.cursor.description of the result object. The result.cursor.description is a list containing tuples whose 1st element is the name of the column. Let us run a for loop on it and store the first element of it in our custom columns variable.

Python3

from sqlalchemy import create_engine

table_name = 'student'
engine = create_engine("mysql+pymysql://root:root123@localhost/geeksforgeeks")
connection = engine.connect()
result = connection.execute(f"SELECT * FROM {table_name}")
columns = []

for elem in result.cursor.description:
    columns.append(elem[0])

print(columns)

Output of above code