SqlAlchemy concat two columns for `ilike` query

May 31, 2023

I’m trying to recreate the following (PostgreSQL) query in SqlAlchemy:

select
       u.first_name,
       u.last_name,
from users u
where ((u.first_name ||' '|| u.last_name) ilike '%Mark Zuckerberg%')

Essentially I’m concatenating the two columns then searching by the full name, which in my case is passed in via a user query.

Is there a way to do this in SqlAlchemy?

>Solution :

Example, how you can do it

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

Base = declarative_base()


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


# Create the SQLAlchemy engine
engine = create_engine('postgresql://username:password@host:port/database_name')

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Concatenate the first_name and last_name columns
full_name = User.first_name + ' ' + User.last_name

# Perform the query
query = session.query(User.first_name, User.last_name).filter(full_name.ilike('%Mark Zuckerberg%'))

# Retrieve the results
results = query.all()

# Print the results
for first_name, last_name in results:
    print(f'First Name: {first_name}, Last Name: {last_name}')