How to SELECT with several where?

Question

I contact you after having tried several things for hours.

I'm currently developing an application (which I won't use, just for my skills) which is a basic contact manager. Each contact will contain information such as name, age, email etc.

I have a problem when using SQL Alchemy.

In my application, I would like the user to be able to search the contacts by entering only part of the information, for example, the last name, and get all the results with that last name.

The problem is that I don't see how to create a SELECT that takes all the parameters into account. Indeed, when I inform several parameters, if one of them is set to "None", the query will not work as I want.

Let's take an example, I want to search for these contacts:

last_name = 'Vincent', first_name = 'Pierre', age = 20
last_name = 'Vincent', first_name = 'Paul', age = 22
last_name = 'Vincent, first_name = 'Jacques', age = 25

If I perform a search with only the last_name = 'Vincent', the query will not return these three entries since the first_name and age of these three entries are not None.

How can I do in my code to correctly include the fact that "If I have a parameter set to None, then it should ignore this parameter"?

Thanks in advance !

# -*- coding: utf-8 -*-
# PyContacts/data_manager.py

from sqlalchemy import create_engine, Column, Integer, String, select
from sqlalchemy.orm import declarative_base, Session


# engine & Base creation
engine = create_engine('sqlite:///contacts.db')
Base = declarative_base()

class Contact(Base):
    __tablename__ = 'contacts'
    id = Column('id', Integer, primary_key=True)
    last_name = Column('last_name', String)
    first_name = Column('first_name', String)
    age = Column('age', Integer)
    # Canceled data to simplify our code atm
    #mail = Column('mail', String)
    #phone = Column('phone', String)

    def __repr__(self):
        return f"Contact(id={self.id!r}, last_name={self.last_name!r}, first_name={self.first_name!r}, age={self.age!r})"

# Create tables that do not exists
Base.metadata.create_all(engine)

def add_contact(last_name=None, first_name=None, age=None):
    # creating a session (=interaction with database) to add a contact
    with Session(engine) as session:
        session.add(Contact(last_name=last_name, first_name=first_name, age=age))
        session.commit()

def update_contact(last_name=None, first_name=None, age=None):
    pass

def select_contact(last_name=None, first_name=None, age=None):
    with Session(engine) as session:
                stmt = select(Contact).where(
                        Contact.last_name.in_([last_name, None]),
                        Contact.first_name.in_([first_name, None]),
                        Contact.age.in_([age, None])
                    )

    for contact in session.scalars(stmt):
        print(contact)


# contacts.db
# last_name = 'Vincent', first_name = 'Pierre', age = 20
# last_name = 'Vincent', first_name = 'Paul', age = 22
# last_name = 'Vincent, first_name = 'Jacques', age = 25
select_contract(last_name = 'Vincent')
# Expected : 3 entries
# Actual result : 0 entry

https://stackoverflow.com/questions/7942547/using-or-in-sqlalchemy — rasjani, Jan 23 '23 at 11:19

score 0 · Answer 1 · answered Jan 25 '23 at 02:47

You can dynamically build queries based on parameters provided to your function:

def select_contact(
    last_name: str | None = None,
    first_name: str | None = None,
    age: int | None = None,
) -> list[Contact]:
    stmt = select(Contact)

    if last_name is not None:
        stmt = stmt.where(Contact.last_name == last_name)
    if first_name is not None:
        stmt = stmt.where(Contact.first_name == first_name)
    if age is not None:
        stmt = stmt.where(Contact.age == age)

    with sessionmaker() as session:
        return session.scalars(stmt).all()

Alternatively you could probably write something like this using coalesce:

stmt = (
    select(Contact)
    .where(
        Contact.first_name == func.coalesce(first_name, Contact.firt_name)
    )
)

But I find first one easier to read, plus not sure if there would be a performance difference

How to SELECT with several where?

1 Answers1