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 Objectmeta_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 objectSTUDENTS = meta_data.tables['students']# SQLAlchemy Query to SELECT specific columnquery = 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 recordsresult = engine.execute(query).fetchall()# View the recordsfor 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 dbfrom sqlalchemy.ext.declarative import declarative_baseBase = 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 QUERYINGclass 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 COLUMNSquery = db.select([Students.first_name, Students.last_name])# FETCH ALL THE RECORDS IN THE RESPONSEresult = engine.execute(query).fetchall()# VIEW THE ENTRIES IN THE RESULTfor 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 dbfrom sqlalchemy.ext.declarative import declarative_baseBase = 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 QUERYINGclass 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 COLUMNSquery = session.query(Students.first_name, Students.last_name)# executing the query with dbresult = query().all()# VIEW THE ENTRIES IN THE RESULTfor 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 DBres=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.
