How to filter consistent columns using LIKE using SQLAlchemy?

Question

I have a user table with columns first_name and last_name . I am trying to create a SQLAlchemy query that will do similar on concatenating two columns, the full name of IE. Here is an example

first_name: Bob

last_name: Smith

query = "bob smi"

I am looking for a query something like:

session.query(Person).filter((Person.firstName + " " + Person.lastName).like(query+'%')

To search bob smi returns bob smith

Thanks!

Answer 1

you were close, you need to build the following query in sqla:

 root@localhost [inDB]> SELECT * FROM Person; +-----------+------------+-----------+ | person_id | first_name | last_name | +-----------+------------+-----------+ | 1 | Bob | Smith | | 2 | John | Smith | +-----------+------------+-----------+ 2 rows in set (0.00 sec) root@localhost [inDB]> SELECT * FROM Person WHERE CONCAT(first_name, ' ', last_name) LIKE 'Bob Sm%'; +-----------+------------+-----------+ | person_id | first_name | last_name | +-----------+------------+-----------+ | 1 | Bob | Smith | +-----------+------------+-----------+ 

then it becomes clear that you need a filter with the concat () function:

 from sqlalchemy import func res = session.query(Person).filter(func.concat(Person.first_name, ' ', Person.last_name).like('Bob Sm%')).all() len(res) # is 1 res, = res print res.first_name, res.last_name # 'Bob Smith' 

Answer 2

This solution should work in all types of databases since the + operator converts the SQL || when used between lines:

 session.query(Person).filter(( Person.first_name + ' ' + Person.last_name ).like('{0}%'.format(query)) )