Querying and selecting specific column in SQLAlchemy

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.