Skip to content
Related Articles

Related Articles

Querying and selecting specific column in SQLAlchemy

View Discussion
Improve Article
Save Article
Like Article
  • Last Updated : 22 Jun, 2022

In this article, we will see how to query and select specific columns using SQLAlchemy in Python.

For our examples, we have already created a Students table which we will be using:

Selecting specific column in SQLAlchemy:

Syntax: sqlalchemy.select(*entities)

Where: Entities to SELECT from. This is typically a series of ColumnElement for Core usage and ORM-mapped classes for ORM usage.

SQLAlchemy Core

In this example, we have used the SQLAlchemy Core. The already created students table is referred which contains 4 columns, namely, first_name, last_name, course, score. But we will be only selecting a specific column. In the example, we have referred to the first_name and last_name columns. Other columns can also be provided in the entities list.

Python




import sqlalchemy as db
 
# Define the Engine (Connection Object)
engine = db.create_engine("mysql+pymysql://\
root:password@localhost/Geeks4Geeks")
 
# Create the Metadata Object
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
 
# Get the `students` table from the Metadata object
STUDENTS = meta_data.tables['students']
 
# SQLAlchemy Query to SELECT specific column
query = db.select([
    STUDENTS.c.first_name,
    STUDENTS.c.last_name
])
 
# Fetch all the records
result = engine.execute(query).fetchall()
 
# View the records
for record in result:
    print("\n", record[0], record[1])

Output:

Output – SQLAlchemy Core

SQLAlchemy ORM

In this example is similar to the previous one except for the fact that it is built on SQLAlchemy ORM. SQLAlchemy ORM is a more pythonic implementation of the SQLAlchemy, as you can see in the code, that we have created a Python class to refer to the student table. The syntax is same in both cases with a minor change in the way we are defining the column names. The columns in ORM are defined using ORM-mapped classes. The output is same for both as we have taken first_name and last_name columns in this example as well.

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 Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50),
                           primary_key=True)
    last_name  = db.Column(db.String(50),
                           primary_key=True)
    course     = db.Column(db.String(50))
    score      = db.Column(db.Float)
 
# SQLAlCHEMY CORE QUERY TO FETCH SPECIFIC COLUMNS
query = db.select([Students.first_name, Students.last_name])
 
# FETCH ALL THE RECORDS IN THE RESPONSE
result = engine.execute(query).fetchall()
 
# VIEW THE ENTRIES IN THE RESULT
for record in result:
    print(record[0], record[1])

Output:


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!