- SQLAlchemy Tutorial
- SQLAlchemy - Home
- SQLAlchemy - Introduction
- 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
- Dialects
- SQLAlchemy Useful Resources
- SQLAlchemy - Quick Guide
- SQLAlchemy - Useful Resources
- SQLAlchemy - Discussion
Returning List and Scalars
There are a number of methods of Query object that immediately issue SQL and return a value containing loaded database results.
Here’s a brief rundown of returning list and scalars −
all()
It returns a list. Given below is the line of code for all() function.
session.query(Customers).all()
Python console displays following SQL expression emitted −
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers
first()
It applies a limit of one and returns the first result as a scalar.
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers LIMIT ? OFFSET ?
The bound parameters for LIMIT is 1 and for OFFSET is 0.
one()
This command fully fetches all rows, and if there is not exactly one object identity or composite row present in the result, it raises an error.
session.query(Customers).one()
With multiple rows found −
MultipleResultsFound: Multiple rows were found for one()
With no rows found −
NoResultFound: No row was found for one()
The one() method is useful for systems that expect to handle “no items found” versus “multiple items found” differently.
scalar()
It invokes the one() method, and upon success returns the first column of the row as follows −
session.query(Customers).filter(Customers.id == 3).scalar()
This generates following SQL statement −
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id = ?