The select() function is the foundational method for querying data in SQLAlchemy. It translates SQLAlchemy-style queries into SQL SELECT statements optimized for your specific backend database. The query mechanism introduced in SQLAlchemy 2.0 represents a significant improvement over earlier versions of the framework.
Database Preparation
In this post, I will demonstrate how to query records from a single database table. Let's begin by creating a database schema. We'll define a users table and map it to the User class. We'll include three key columns: id (serving as the primary key), name (to store the user's name), and first_seen (to represent the account creation timestamp). Additionally, we'll implement a __repr__ method to ensure our User objects display in a readable format when printed.
Finally, we'll create an in-memory database using the SQLite engine. We'll generate the table using our Model class and define a Session object to manage database connections and transactions.
from sqlalchemy import Table, Column, Integer, String, create_engine, MetaData
from sqlalchemy.orm import sessionmaker, Mapped, mapped_column, DeclarativeBase, aliased
from sqlalchemy.sql.expression import select
from typing import Optional
class Model(DeclarativeBase):
pass
class User(Model):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String())
first_seen: Mapped[str] = mapped_column(String())
def __repr__(self):
return "User(user_id={self.id}, " \
"name='{self.name}', " \
"first_seen={self.first_seen})".format(self=self)
engine = create_engine('sqlite:///:memory:')
Model.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
Test Data
With our database prepared, let's add two test users: Gregory and Maria. This demonstrates SQLAlchemy's seamless integration with Python code. We simply create User class instances and add them to the database session – no manual SQL statements or tedious Python-to-database connection management required.
with Session() as session:
user = User(id=1, name='Gregory', first_seen='2024-12-01')
session.add(user)
user = User(id=2, name='Maria', first_seen='2024-12-03')
session.add(user)
session.commit()
Querying the Data
Now that we have data in our database, let's explore how to retrieve it. The simplest method is to use the select() function to create a Select object, which we then execute using a Session. This Select object represents an SQL SELECT statement that we execute with the session.execute() method.
Query All Data
print("Get all users")
stmt = select(User)
print("SQLAlchemy select translated to SQL\n{}".format(stmt))
with Session() as session:
users = session.execute(stmt)
for user in users:
print(user)
SQLAlchemy select translated to SQL
SELECT users.id, users.name, users.first_seen
FROM users
(User(user_id=1, name='Gregory', first_seen=2024-12-01),)
(User(user_id=2, name='Maria', first_seen=2024-12-03),)
This code snippet demonstrates two key SQLAlchemy features:
- SQLAlchemy creates a sqlalchemy.sql.expression.Select object that can be further filtered or modified before execution
- SQLAlchemy automatically translates the Select object into a database-specific SQL statement, based on the engine we specified in create_engine('sqlite:///:memory:'). For more complex queries, the generated SQL may vary slightly between different database backends.
Query the First Result with first()
The first() method returns only the first record from the query results, regardless of how many records exist in the database. This method returns a Row object containing a single User object, eliminating the need for an explicit loop to retrieve the data.
print("Get the first user")
smtp = select(User)
with Session() as session:
user = session.execute(smtp).first()
print(user)
(User(user_id=1, name='Gregory', first_seen=2024-12-01),)
Query Specific Columns
By specifying individual column names in our Select statement, we can retrieve only the data we need. In this example, we'll fetch just the User.name column:
print("Get only selected columns")
smtp = select(User.name)
with Session() as session:
user = session.execute(smtp).first()
print(user)
('Gregory',)
Query Filtered Rows with where()
To retrieve records that meet specific criteria, we use the where() method of the Select object. In this example, we'll filter results by the User.id column:
print("Get only selected rows using 'where'")
smtp = select(User.name).where(User.id == 2)
with Session() as session:
user = session.execute(smtp).first()
print(user)
('Maria',)
Query by Primary Key with get()
The get() method is a convenient shortcut for retrieving a single record by its primary key. It serves as a specialized form of the where() method, automatically filtering by the table's primary key column. In our case, the data is filtered by id, which we defined as the primary key of the User table.
with Session() as session:
user = session.get(User, 2)
print(user)
User(user_id=2, name='Maria', first_seen=2024-12-03)
The whole code is presented below:
Summary
In this post, I've covered the most commonly used methods for querying data in SQLAlchemy 2.0. These queries generate a Select object that can be further filtered and customized using the techniques demonstrated above. This approach enables seamless integration of database query logic with your Python codebase. For those transitioning from older SQLAlchemy versions, please refer to this post to see the key differences in the query API.