[Python] SQLAlchemy use "IN" to select pairwise correspondence

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

Continue reading...