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.
To execute queries in SQLAlchemy, we must first establish a connection to the database.
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.
# Import statementsfrom sqlalchemy import create_engine, Column, Integer, String, Floatfrom sqlalchemy.ext.declarative import declarative_baseCustomBase = declarative_base()# Define Table Class Productclass CustomProduct(CustomBase):__tablename__ = 'custom_products'custom_id = Column(Integer, primary_key=True)custom_name = Column(String)custom_price = Column(Float)# Define Table Class Customerclass 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 Enginecustom_engine = create_engine('sqlite:///:memory:')# Bind the Custom TablesCustomBase.metadata.create_all(custom_engine)
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:
# Create a query to filter products with a price greater than $10MyProducts = session.query(Product).filter(Product.price > 10.0).all()# Print the filtered productsfor 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
# Query specific columns from the products tablecolumns_to_select = [Product.name]MyProducts = session.query(*columns_to_select).all()# Print the selected columnsfor product in MyProducts:print(f"Product: {product[0]}")
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:
# Query products sorted by price in descending orderMyProducts = session.query(Product).order_by(Product.price.desc()).all()# Print the sorted productsfor 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:
from sqlalchemy import func# Count the number of productsproduct_count = session.query(func.count(Product.id)).scalar()# Print the product countprint(f"Total number of products: {product_count}")
While SQLAlchemy promotes SQL abstraction, we can still execute raw SQL queries if necessary. Here’s how we can do that:
# Define a raw SQL querysql_query = "SELECT * FROM products WHERE price > :price_threshold"# Execute the raw SQL query with parametersresult = session.execute(sql_query, {"price_threshold": 10.0})# Fetch and print the query resultsfor row in result:print(f"Product: {row.name}, Price: {row.price}")
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:
What is SQLAlchemy?
Understand SQLAlchemy’s role in Python database management, offering ORM, SQL expressions, and efficient data handling.
How to connect with a database in SQLAlchemy
Learn how to establish a connection with different databases using SQLAlchemy’s engine and session.
How to create a table in SQLAlchemy
Discover how to define and create database tables using SQLAlchemy’s ORM and core functionalities.
How to execute queries in SQLAlchemy
Explore how to perform CRUD operations efficiently using SQLAlchemy’s query execution methods.
How to use relationships and associations in SQLAlchemy?
Master the implementation of table relationships and associations for structured data modeling.
Free Resources