- SQLAlchemy Core
- Expression Language
- Connecting to Database
- Creating Table
- SQL Expressions
- Executing Expression
- Selecting Rows
- Using Textual SQL
- Using Aliases
- Using UPDATE Expression
- Using DELETE Expression
- Using Multiple Tables
- Using Multiple Table Updates
- Parameter-Ordered Updates
- Multiple Table Deletes
- Using Joins
- Using Conjunctions
- Using Functions
- Using Set Operations
- SQLAlchemy ORM
- Declaring Mapping
- Creating Session
- Adding Objects
- Using Query
- Updating Objects
- Applying Filter
- Filter Operators
- Returning List and Scalars
- Textual SQL
- Building Relationship
- Working with Related Objects
- Working with Joins
- Common Relationship Operators
- Eager Loading
- Deleting Related Objects
- Many to Many Relationships
- SQLAlchemy Useful Resources
- SQLAlchemy - Quick Guide
- SQLAlchemy - Useful Resources
- SQLAlchemy - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQLAlchemy ORM - Using Query
All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
Query objects are initially generated using the query() method of the Session as follows −
q = session.query(mapped class)
Following statement is also equivalent to the above given statement −
q = Query(mappedClass, session)
The query object has all() method which returns a resultset in the form of list of objects. If we execute it on our customers table −
result = session.query(Customers).all()
This statement is effectively equivalent to following SQL expression −
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers
The result object can be traversed using For loop as below to obtain all records in underlying customers table. Here is the complete code to display all records in Customers table −
from sqlalchemy import Column, Integer, String from sqlalchemy import create_engine engine = create_engine('sqlite:///sales.db', echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Customers(Base): __tablename__ = 'customers' id = Column(Integer, primary_key = True) name = Column(String) address = Column(String) email = Column(String) from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() result = session.query(Customers).all() for row in result: print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)
Python console shows list of records as below −
Name: Ravi Kumar Address: Station Road Nanded Email: firstname.lastname@example.org Name: Komal Pande Address: Koti, Hyderabad Email: email@example.com Name: Rajender Nath Address: Sector 40, Gurgaon Email: firstname.lastname@example.org Name: S.M.Krishna Address: Budhwar Peth, Pune Email: email@example.com
The Query object also has following useful methods −
|Sr.No.||Method & Description|
It adds one or more column expressions to the list of result columns to be returned.
It adds a mapped entity to the list of result columns to be returned.
It returns a count of rows this Query would return.
It performs a bulk delete query. Deletes rows matched by this query from the database.
It applies a DISTINCT clause to the query and return the newly resulting Query.
It applies the given filtering criterion to a copy of this Query, using SQL expressions.
It returns the first result of this Query or None if the result doesn’t contain any row.
It returns an instance based on the given primary key identifier providing direct access to the identity map of the owning Session.
It applies one or more GROUP BY criterion to the query and return the newly resulting Query
It creates a SQL JOIN against this Query object’s criterion and apply generatively, returning the newly resulting Query.
It returns exactly one result or raise an exception.
It applies one or more ORDER BY criterion to the query and returns the newly resulting Query.
It performs a bulk update query and updates rows matched by this query in the database.