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/w3wiki") 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/w3wiki") 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