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 dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass 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 QUERYwith engine.connect() as conn: result = conn.execute( db.select([Profile.email, Profile.name, Profile.contact]))# VIEW THE COLUMN NAMESprint(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 dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTION OBJECT)engine = db.create_engine(# CREATE THE TABLE MODEL TO USE IT FOR QUERYINGclass 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 QUERYwith engine.connect() as conn: result = conn.execute( db.select([Profile.email, Profile.name, Profile.contact]))# VIEW THE COLUMN NAMESprint(result._metadata.keys) |
Output:
