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)# CREATE THE METADATA OBJECTmetadata_obj = db.MetaData()# DEFINE THE PROFILE TABLE WITH 3 COLUMNSprofile = 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 DATABASEmetadata_obj.create_all(engine)# INSERT RECORDS IN THE PROFILE TABLEstmt = 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 RESPONSEresult = engine.execute(query).fetchall()# VIEW THE ENTRIES IN THE RESULTfor 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 dbfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()# DEFINE THE ENGINE (CONNECTIO OBJECT)# 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)# SQLAlCHEMY CORE QUERY TO FETCH SINGLE COLUMN (EMAIL)query = db.select([Profile.name])# FETCH ALL THE RECORDS IN THE RESPONSEresult = engine.execute(query).fetchall()# VIEW THE ENTRIES IN THE RESULTfor record in result: print("\n", record) |
Output:
