How To Select Only One Column Using SQLAlchemy?

In this article, we are going to see how to select only one column using SQLAlchemy in Python.

SQLAlchemy is a large SQL toolkit with lots of different components. The two largest components are SQLAlchemy Core and SQLAlchemy ORM. The major difference between them is SQLAlchemy Core is a schema-centric model that means everything is treated as a part of the database i.e., rows, columns, tables, etc while SQLAlchemy Core uses an object-centric view which encapsulates the schema with business objects. SQLAlchemy is a more pythonic implementation. In this post, we shall look at the SQLAlchemy core and ORM and how to select only one column using it.

Installing SQLAlchemy

SQLAlchemy is available via the pip install package.

pip install sqlalchemy

However, if you are using a flask you can make use of its own implementation of SQLAlchemy. It can be installed using:

pip install flask-sqlalchemy

Before we proceed with the code, make sure you have a database to work with. In the below examples, we are going to make use of the mySQL database. Feel free to use any database but the connection string will vary accordingly.

The syntax, given below, holds true for both to the SQLAlchemy core and ORM.

Syntax: sqlalchemy.sql.expression.select(*args, **kw)

Creates a SQL SELECT statement

SQLAlchemy Core

Python

import sqlalchemy as db

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

# CREATE THE METADATA OBJECT
metadata_obj = db.MetaData()

# DEFINE THE PROFILE TABLE WITH 3 COLUMNS
profile = db.Table(
    'profile',
    metadata_obj,
    db.Column('email', db.String(50), primary_key=True),
    db.Column('name', db.String(100)),
    db.Column('contact', db.Integer),
)

# CREATE THE PROFILE TABLE IN THE DATABASE
metadata_obj.create_all(engine)

# INSERT RECORDS IN THE PROFILE TABLE
stmt = profile.insert().values(("amitpathak@zmail.com",
                                "Amit Pathak",
                                879456123))
engine.execute(stmt)
stmt = profile.insert().values(("amitmishra@zmail.com",
                                "Amit Mishra",
                                456789123))
engine.execute(stmt)
stmt = profile.insert().values(("ravipandey@zmail.com",
                                "Ravi Pandey",
                                321456987))
engine.execute(stmt)

# SQLAlCHEMY CORE QUERY TO FETCH SINGLE COLUMN (EMAIL)
query = db.select([profile.c.email])

# FETCH ALL THE RECORDS IN THE RESPONSE
result = engine.execute(query).fetchall()

# VIEW THE ENTRIES IN THE RESULT
for record in result:
    print("\n", record)

Output:

In the above code, we have used SQLAlchemy core to select one column from a table. We first created a profile table with 3 columns, namely, email, name, and contact. We then entered 3 different records in this table. The records are then fetched using the select method of the sqlalchemy library.

SQLAlchemy ORM

In the first example, we have already created the profile table and provided 3 records in the table. In this example, we focus on how to extract a single column using SQLAlchemy ORM. As we can see, both ORM and core have the same syntax when it comes to querying a single row. The only difference is the way we define the metadata of the profile table. In the ORM code, it seems more pythonic since the usage of python class is observed. For this example, we have fetched the name column. We first define the profile table and then use this definition to fetch the name column from the profile table.

Python

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

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTIO 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)

# SQLAlCHEMY CORE QUERY TO FETCH SINGLE COLUMN (EMAIL)
query = db.select([Profile.name])

# FETCH ALL THE RECORDS IN THE RESPONSE
result = engine.execute(query).fetchall()

# VIEW THE ENTRIES IN THE RESULT
for record in result:
    print("\n", record)

Output: