Querying and selecting specific column in SQLAlchemy

Last Updated : 4 Aug, 2023

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

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, and 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.

By using the select() method:

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()
meta_data.reflect(bind=engine)

#don't follow the following syntax for creating the meta_data
#meta_data=MetaData(bind=engine)
#Here MetaData class doesn't have any argument bind,so we get arror.


# 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
)
#don't use [STUDENT.c.first_name,....] give ArgumentError
#[STUDENT.c.first_name,....] the syntax in older versions only

# 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

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 the 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 the same for both as we have taken the first_name and last_name columns in this example as well.

By using the select method():

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:

By using ORM query() method:

We can also use the query() method to select the specific columns or all columns of the table. 

query(): The query() method in SQLAlchemy is used to create a query object that allows you to perform database queries using SQLAlchemy's ORM.

Syntax: query(ModelClass or ModelClass.attributes)

retruns the instance of the Query class in SQLAlchemy ORM.

Note: query() method does not work with SQLAlchemy Core, because the query() method is not directly available to the SQLAlchemy Core.

Python3 
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 ORM qury() TO FETCH SPECIFIC COLUMNS
query = session.query(Students.first_name, Students.last_name)

# executing the query with db
result = query().all()

# VIEW THE ENTRIES IN THE RESULT
for record in result:
    print(record[0], record[1])

Output:

Ashish Mysterio
Rahul Kumar
Irfan Malik
Irfan Ahmed
John Wick
Mayon Irani
Ashish Mysterio
Rahul Kumar
Irfan Malik
Irfan Ahmed
John Wick
Mayon Irani

By using text() method:

we can use the text() method to select columns from the table. It worksallows with both SQLAlchemy ORM and SQLAlchemy Core.

text(): it allows us to write SQL queries directly in your code as strings.

Syntax: text('SQL_QUERY')

returns TextClause object which represents a textual SQL expression 

Python3 
#Creating the SQL statementby using the text()
stmt=text("SELECT first_name,last_name FROM students")

#executing the statement with DB
res=session.execute(stmt)

for val in res:
    print(val[0],val[1])

Note: we can use update , delete, insert, etc..statements with the text() method.

Comment

Explore