PostgreSQL @>操作符在SQLAlchemy中的等价写法是什么？

Hey there, I’ve tackled this exact scenario before—converting PostgreSQL’s @> range containment operator to SQLAlchemy code. There are a couple of solid approaches depending on your use case:

If your column is a PostgreSQL range type (like INT4RANGE, TSTZRANGE, etc.), SQLAlchemy has a native contains() method for these types that maps directly to the @> operator. It’s clean and aligns with ORM best practices.

Example usage:
Suppose you have an Event model with a time_range field of type TSTZRANGE, and you want to fetch all events where the range includes a specific timestamp:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from your_models import Event
from datetime import datetime

# Set up your database session
engine = create_engine("postgresql://user:password@host/db_name")
Session = sessionmaker(bind=engine)
session = Session()

# The target time we want to check
target_timestamp = datetime.now()

# This translates to: SELECT * FROM event WHERE time_range @> :target_timestamp
matching_events = session.query(Event).filter(Event.time_range.contains(target_timestamp)).all()

This works for range-in-range checks too:

from sqlalchemy.dialects.postgresql import TSTZRANGE

target_range = TSTZRANGE(datetime(2024, 1, 1), datetime(2024, 1, 31))
matching_events = session.query(Event).filter(Event.time_range.contains(target_range)).all()

If you’re working with older SQLAlchemy versions, or need to use @> with non-range types (like PostgreSQL arrays, which also support @>), you can explicitly call the operator using SQLAlchemy’s op() method. This is a direct mirror of the raw SQL syntax.

Example for range types:

# Equivalent to SELECT * FROM event WHERE time_range @> :target_timestamp
matching_events = session.query(Event).filter(Event.time_range.op('@>')(target_timestamp)).all()

And for array containment (e.g., checking if an array column contains a specific array):

# Assume Event has a `tags` array column; fetch events where tags includes ['python']
matching_events = session.query(Event).filter(Event.tags.op('@>')(['python'])).all()

• For SQLAlchemy 1.4+, the contains() method for range types is fully supported and should be your go-to choice for readability.
• Both approaches will generate the exact PostgreSQL @> operator syntax under the hood, so performance is identical to writing raw SQL.

内容的提问来源于stack exchange，提问作者Raghav Patnecha