How to execute queries in SqlAlchemy

SQLAlchemy is a powerful Python library that simplifies working with databases, enabling developers to interact with databases using Python code rather than writing raw SQL queries. In this Answer, we’ll dive into the world of executing queries in SQLAlchemy, covering the essential techniques and patterns to retrieve, filter, and manipulate data from the database.

Connecting to the database

To execute queries in SQLAlchemy, we must first establish a connection to the database.

Basic querying

You can find the playground below with a database.py file, which holds all the table-creating boilerplate code and a main.py file. Let’s start with some basic querying techniques in SQLAlchemy:

1. Query all records: To retrieve all records from a table, use the query() method along with the table class.

main.py
database.py
# Import statements
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
CustomBase = declarative_base()
# Define Table Class Product
class CustomProduct(CustomBase):
    __tablename__ = 'custom_products'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String)
    custom_price = Column(Float)
# Define Table Class Customer
class CustomCustomer(CustomBase):
    __tablename__ = 'custom_customers'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String)
    custom_email = Column(String, unique=True)
# Create a Custom Engine
custom_engine = create_engine('sqlite:///:memory:')
# Bind the Custom Tables
CustomBase.metadata.create_all(custom_engine)

Code explanation

In the Database.py file: 

  • This file defines two SQLAlchemy table classes (CustomProduct and CustomCustomer) with corresponding columns, creating an in-memory SQLite database engine (custom_engine), and binding the table classes to the engine, allowing interaction with the tables in an SQLite database.

In the main.py file: 

  • Lines 1–2: These are the import statements required for the file.

  • Lines 4–17: These lines define the custom tables CustomProduct and CustomCustomer and insert dummy values into them for the data population. On line 17, the changes are committed to persist in the actual database.

  • Lines 20–26: These lines are used to query and retrieve all records from the custom_products and custom_customers tables, printing information about each record, including product names and prices for the custom_products table, and customer names and emails for the custom_customers table.

  • Line 29: This line of code closes the SQLAlchemy session (custom_session), releasing the resources and connections associated with it.

2. Filtering records: We can filter records by applying conditions using SQLAlchemy’s filter() method. For instance, to retrieve products with a price greater than $10:

main.py
database.py
# Create a query to filter products with a price greater than $10
MyProducts = session.query(Product).filter(Product.price > 10.0).all()
# Print the filtered products
for product in MyProducts:
    print(f"Product: {product.name}, Price: {product.price}")

3. Querying specific columns: When querying a database table using SQLAlchemy, if there’s a need to retrieve only specific columns, we can use the query() method along with slicingSelecting only required part from the table. to select and fetch only the desired columns instead of fetching the entire row.

main.py
database.py
# Query specific columns from the products table
columns_to_select = [Product.name]
MyProducts = session.query(*columns_to_select).all()
# Print the selected columns
for product in MyProducts:
    print(f"Product: {product[0]}")

Advanced querying

SQLAlchemy provides more advanced querying capabilities as well:

1. Sorting results: We can order the results using the order_by() method. Run the following code to retrieve products sorted by price in descending order:

main.py
database.py
# Query products sorted by price in descending order
MyProducts = session.query(Product).order_by(Product.price.desc()).all()
# Print the sorted products
for product in MyProducts:
    print(f"Product: {product.name}, Price: {product.price}")

2. Aggregating data: SQLAlchemy supports aggregation functions like count(), sum(), avg(), etc. Run the following code to count the number of products in the table:

main.py
database.py
from sqlalchemy import func
# Count the number of products
product_count = session.query(func.count(Product.id)).scalar()
# Print the product count
print(f"Total number of products: {product_count}")

Executing raw SQL queries

While SQLAlchemy promotes SQL abstraction, we can still execute raw SQL queries if necessary. Here’s how we can do that:

main.py
database.py
# Define a raw SQL query
sql_query = "SELECT * FROM products WHERE price > :price_threshold"
# Execute the raw SQL query with parameters
result = session.execute(sql_query, {"price_threshold": 10.0})
# Fetch and print the query results
for row in result:
    print(f"Product: {row.name}, Price: {row.price}")

Conclusion

Executing queries in SQLAlchemy empowers us to interact with databases seamlessly, whether it’s fetching data, applying filters, aggregating results, or joining tables. The library’s rich feature set and elegant Pythonic syntax make it a valuable tool for database operations in Python applications, enabling developers to work efficiently and maintain clean, readable code. Mastering SQLAlchemy’s querying capabilities is crucial to becoming proficient in database-driven application development.

Unlock your potential: SQLAlchemy fundamentals series, all in one place!

To deepen your understanding of SQLAlchemy, explore our series of Answers below:

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved