SQLAlchemy: Select Rows Between Two Dates

Introduction

SQLAlchemy provides powerful tools to interact with databases using Python‌. One common query task is to retrieve rows within a specific date range. This tutorial demonstrates how to accomplish this with SQLAlchemy, a widely-used Object-Relational Mapping (ORM) library for Python.

Selecting Rows by Date Range

To select rows between two dates in SQLAlchemy, you first need to define your models, make a connection to your database, and start a session. Here is a step-by-step guide to performing this query in both core SQLAlchemy and using the ORM.

Setting up the Environment

# Importing necessary modules
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Date, select
from sqlalchemy.orm import sessionmaker
from datetime import datetime

# Establishing a connection to the database
engine = create_engine('sqlite:///mydatabase.db')
metadata = MetaData(bind=engine)

# Declare a table
my_table = Table('mytable', metadata,
                 Column('id', Integer, primary_key=True),
                 Column('name', String),
                 Column('date', Date)
                )

# Creating the table
metadata.create_all(engine)

# Starting a session
Session = sessionmaker(bind=engine)
session = Session()

Basic Selection with Core

# Assuming you've already connected to your db and have a table defined.

# Defining dates for the range
start_date = datetime(2021, 1, 1)
end_date = datetime(2021, 12, 31)

# Constructing the select query
query = select([my_table]).where(my_table.c.date.between(start_date, end_date))

# Executing the query
results = engine.execute(query)
for row in results:
    print(row)

Selection with ORM

# Defining an ORM class
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Date

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'mytable'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    date = Column(Date)

# Performing a query with the ORM
start_date = datetime(2021, 1, 1)
end_date = datetime(2021, 12, 31)

# Using the session to perform a query
results = session.query(MyTable).filter(MyTable.date.between(start_date, end_date)).all()
for instance in results:
    print(instance.id, instance.name, instance.date)

Advanced Usage

For more complex queries, you can join tables, apply aggregations, and use other SQL functions. Here’s a quick look at some advanced usage examples.

Joining Tables

# Assuming there's another table related to 'my_table' called 'related_table'

# Defining the related table ORM class
class RelatedTable(Base):
    __tablename__ = 'related_table'
    id = Column(Integer, primary_key=True)
    my_table_id = Column(Integer, ForeignKey('my_table.id'))
    more_data = Column(String)

# Creating a session
session = Session()

# Selecting rows with a join and date between
start_date = datetime(2021, 1, 1)
end_date = datetime(2021, 12, 31)
join_query = session.query(MyTable, RelatedTable).join(RelatedTable).filter(MyTable.date.between(start_date, end_date))

for my_table_instance, related_table_instance in join_query:
    print(my_table_instance.name, related_table_instance.more_data)

Using SQL Functions

# Using functions like `extract` to select rows by part of a date
from sqlalchemy import extract

yearly_results = session.query(MyTable).filter(extract('year', MyTable.date) == 2021).all()

for result in yearly_results:
    print(result.id, result.name)

Summary

In this tutorial, we have extensively explored how to select rows between two dates using SQLAlchemy. Whether you’re working with the core SQLAlchemy or the ORM, you now have the foundational knowledge to extract date-specific data from your database efficiently.