How to get column names from SQLAlchemy?

In this article, we will discuss how to get column names using SQLAlchemy in Python.

SQLAlchemy is an open-source SQL toolkit and object-relational mapper for the Python programming language released under the MIT License. It gives full power and flexibility of SQL to an application. To follow along with this article, we need to have sqlalchemy and anyone database installed in our system. We have used the MySQL database for this article’s understanding.

To install sqlalchemy using pip:

pip install sqlalchemy

For our examples, we have already created a Profile table under Geeks4Geeks schema name, which we will be using:

There are different ways through which we can get the column names in SQLAlchemy.

Method 1: Using keys()

Here we will use key() methods to get the get column names. It returns an iterable view which yields the string keys that would be represented by each Row.

Syntax: sqlalchemy.engine.Result.keys()

Python

import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")

# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):

    __tablename__ = 'profile'

    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)


# PREPARING SQLALCHEMY QUERY
with engine.connect() as conn:
    result = conn.execute(
        db.select([Profile.email, Profile.name,
                   Profile.contact]))

# VIEW THE COLUMN NAMES
print(result.keys())

Output:

In the above example, we have used SQLAlchemy ORM to create a Profile table. 

• Firstly, the engine is configured so that it can be used to perform SQL transactions. 
• Then, the Profile table is defined in the form of ORM class.
• Using the sqlalchemy engine we are querying the Profile table to select all the records from the table for columns email, name, and contact. The information is stored in the result variable. This variable holds the column names and the records that have been fetched from the Profile table. The datatype of result variable is sqlalchemy.engine.Result.
• To access the column names we can use the method keys() on the result. It returns a list of column names.
• Since, we queried only three columns, we can view the same columns on the output as well.

Method 2: Using table metadata

This returns the ‘key’ for the Table Metadata.

Syntax: sqlalchemy.schema.Table.key

Python

import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")

# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):

    __tablename__ = 'profile'

    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)


# PREPARING SQLALCHEMY QUERY
with engine.connect() as conn:
    result = conn.execute(
        db.select([Profile.email, Profile.name,
                   Profile.contact]))

# VIEW THE COLUMN NAMES
print(result._metadata.keys)

Output: