SQLAlchemy use "IN" to select pairwise correspondence

Question

Consider the following DB:

from sqlalchemy import String, select, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    name: Mapped[str] = mapped_column(String(30))
    surname: Mapped[str] = mapped_column(String(30))
    def __repr__(self):
        return f"User(name={self.name!r}, surname={self.surname!r})"

engine = create_engine("sqlite+pysqlite:///test3.sqlite3", echo=True)

Base.metadata.create_all(engine)

with Session(engine) as session:
    user1 = User(
        name="Mario",
        surname="Rossi"
    )
    user2 = User(
        name="Mario",
        surname="Bianchi",
    )
    user3 = User(
        name="Giovanni",
        surname="Bianchi",
    )
    session.add_all([user1, user2, user3])
    session.commit()

Now suppose I have a list of users I want to find:

users = [("Mario", "Rossi"), ("Giovanni", "Bianchi")]

Then I would run:

names = [name for name, _ in users]
surnames = [surname for _, surname in users]
with Session(engine) as session:
    stmt = select(User).where(User.name.in_(names)).where(User.surname.in_(surnames))
    print(session.execute(stmt).scalars().all())

which returns:

[User(name='Mario', surname='Rossi'), User(name='Mario', surname='Bianchi'), User(name='Giovanni', surname='Bianchi')]

but "Mario Bianchi" was not in the list of input users I had.

How can I concatenate IN statements in order to sleect only pairwise correspondence?

That is, if I have varA in (el1, el2) AND varB IN (el3, el4), I do not wat to select entries with varA==el1 AND varB==el4

Answer 1

You need to use more explicit OR Boolean condition sqlalchemy.or_(conditions) ensures that each (name, surname) pair is checked explicitly.

from sqlalchemy import or_

with Session(engine) as session:
    stmt = select(User).where(
        or_(*[(User.name == name) & (User.surname == surname) for name, surname in users])
    )
    print(session.execute(stmt).scalars().all())

This works across all databases, including SQLite.

PostgreSQL-Specific Optimization

If you were using PostgreSQL, we can use tuple_ for a more elegant and optimized query:

from sqlalchemy.sql.expression import tuple_

with Session(engine) as session:
    stmt = select(User).where(tuple_(User.name, User.surname).in_(users))
    print(session.execute(stmt).scalars().all())