How to use IN and NOT IN operators in SQLAlchemy

Introduction

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python that provides a full suite of well-known enterprise-level persistence patterns. Understanding how to leverage IN and NOT IN operators in SQLAlchemy can greatly improve the efficiency of your database queries.

Basics of IN and NOT IN Operators

The IN operator is used to compare a value against a list of values, returning true if the value matches any value in the list. In contrast, the NOT IN operator checks if a value is not present in a specified list.

# Example of IN operator
SELECT * FROM users WHERE id IN (1, 2, 3);

# Example of NOT IN operator
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

Using IN and NOT IN with SQLAlchemy

In SQLAlchemy, you will typically use in_() and not_in() when constructing queries. Let’s start with some basic examples:

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

# Engine and session setup
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query using in_
users = session.query(User).filter(User.id.in_((1, 2, 3))).all()

# Query using not_in
users = session.query(User).filter(~User.id.in_((1, 2, 3))).all()

Notice how we pass a tuple as an argument to in_() and negate it with ~ for the NOT IN case.

Working with Query Parameters

Sometimes, you may want to define the values dynamically. You can use query parameters with the IN and NOT IN operators:

user_ids = [1, 2, 3]
users = session.query(User).filter(User.id.in_(user_ids)).all()

exclude_ids = [4, 5]
users = session.query(User).filter(~User.id.in_(exclude_ids)).all()

This approach is helpful when building interactive applications where the list of values might change based on the user input.

Advanced Usage with Subqueries

For more advanced scenarios, you might use subqueries with IN and NOT IN. Here’s a subquery that selects user IDs from a ‘memberships’ table:

from sqlalchemy import select

membership_subquery = select([Membership.user_id]).where(Membership.group_id == 1)

users_in_group = session.query(User).filter(User.id.in_(membership_subquery)).all()

users_not_in_group = session.query(User).filter(~User.id.in_(membership_subquery)).all()

The subquery is used directly within the in_() and not_in() clauses.

Caveats and Best Practices

When working with IN and NOT IN operators, it’s important to consider performance implications, especially with large lists of values or complex subqueries. It’s often better to use joins or exists clauses for such cases.

Combining with Other Query Constructs

You may also combine IN and NOT IN operators with other clauses in your queries:

users = session.query(User)
            .filter(User.id.in_(user_ids))
            .filter(User.name.like('%John%'))
            .all()

This performs a filter where both the user’s ID is in a list and the name contains ‘John’.

Dealing with NULLs

Keep in mind that IN and NOT IN interact with NULL values in a specific way — a NULL value will not match anything, even another NULL; this behavior can trip up new SQLAlchemy users.

See also:

• SQLAlchemy: Select rows where column is not null
• How to select rows where a column is Null in SQLAlchemy

Conclusion

IN and NOT IN are invaluable tools in your SQLAlchemy toolkit. When used judiciously, they can make your queries more expressive and often more performant. Remember that with SQLAlchemy, as with SQL, ensuring your queries are indexed properly and well-understood is paramount to maintaining good performance.