SQLAlchemy: Select Records in Last Day, Week, or Month

Introduction

SQLAlchemy provides a flexible and powerful ORM for Python, enabling developers to programmatically interact with databases in a structured way. Selecting records based on timeframes, such as the last day, week, or month, is a common requirement in database operations. This tutorial demonstrates how to achieve time-based queries using SQLAlchemy.

Setting the Stage

Before diving into the specifics of time-based queries, it is important to setup the environment with SQLAlchemy and a sample database model. Make sure you have SQLAlchemy installed via pip:

pip install SQLAlchemy

Consider a simple model representing a ‘Sale’ as follows:

from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

engine = create_engine('sqlite:///sales.db')
Session = sessionmaker(bind=engine)
session = Session()


class Sale(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    amount = Column(Integer)
    timestamp = Column(DateTime)

Base.metadata.create_all(engine)

With this setup, you can insert some records with timestamps for demonstration.

Basic Queries

Selecting records from the last day involves understanding datetime operations. Here’s a basic example:

from sqlalchemy import func
from datetime import datetime, timedelta

last_day = datetime.utcnow() - timedelta(days=1)

recent_sales = session.query(Sale).filter(Sale.timestamp >= last_day).all()

This simple query filters records where the timestamp is greater than or equal to one day ago from now.

Weekly and Monthly Queries

To select records from the last week or month, you adjust the timedelta accordingly:

# Last week
last_week = datetime.utcnow() - timedelta(weeks=1)
weekly_sales = session.query(Sale).filter(Sale.timestamp >= last_week).all()

# Last month
last_month = datetime.utcnow() - timedelta(days=30)  # Approximation
monthly_sales = session.query(Sale).filter(Sale.timestamp >= last_month).all()

In these queries, the timedelta changes to capture a larger span of time. Adjust the days for the month query based on the specific requirements (e.g., 28, 30, 31).

Using timezone-aware dates

It’s important to consider timezones when performing time-based queries. Here’s how you can make your queries timezone-aware:

from sqlalchemy import extract
from datetime import datetime
from pytz import utc

last_day = datetime.utcnow().replace(tzinfo=utc) - timedelta(days=1)

recent_sales = session.query(Sale).filter(Sale.timestamp >= last_day).all()

By setting the ‘replace(tzinfo=utc)’, you are making sure that the comparison takes place in the UTC timezone.

Advanced Usage: Using SQL Functions

SQLAlchemy also gives you the ability to use SQL functions directly, like the EXTRACT function, for more complex queries like this:

current_year = datetime.utcnow().year
last_month_number = (datetime.utcnow() - timedelta(days=30)).month
monthly_sales = (session.query(Sale)
                .filter(extract('year', Sale.timestamp) == current_year)
                .filter(extract('month', Sale.timestamp) == last_month_number)
                .all())

This query gets all the sales records from the last month, considering the actual month change and not approximately 30 days before.

Dealing with SQL Alchemy ORM Limitations

Sometimes the ORM might not provide the exact functionality you need, or complex queries might be slow. In such cases, using raw SQL or hybrid properties can be useful:

# Raw SQL query example
raw_query = '''
SELECT * FROM sales
WHERE timestamp >= datetime('now', '-7 days')
'''

weekly_sales = session.execute(raw_query).fetchall()

This demonstrates injecting a raw SQL query into the SQLAlchemy session.

Batch Processing

When querying large datasets, consider utilizing batch processing to conserve memory:

# Batch processing for large datasets
for weekly_sale in session.query(Sale).filter(Sale.timestamp >= last_week).yield_per(1000):
    # Process each record in batches of 1000
    print(weekly_sale)

This yielding technique ensures that you don’t overwhelm the memory with an excessive number of objects, which is essential for large databases.

Conclusion

In conclusion, selecting records from the last day, week, or month in SQLAlchemy can be achieved with a combination of datetime and ORM functions. Understanding both basic and advanced techniques can help you write efficient and accurate queries for your database operations. Whether you are a novice or an experienced developer, mastering these principles is key to handling temporal data effectively using SQLAlchemy.