Peewee String Matching (SQL)

Question

I am trying to execute a query in Peewee with results that must have a specific substring in them.

For example, if I want only actions with "Physics" in the name:

schedule = Session.select().join(Activity).where(Activity.name % "%Physics%").join(Course).join(StuCouRel).join(Student).where(Student.id == current_user.id) 

The above example does not give any errors, but does not work correctly.

In python, I would just do if "Physics" in Activity.name , so I'm looking for an equivalent that I can use in a query.

Answer 1

Quick response:

just use Activity.name.contains('Physics')

---

Depending on the database backend you are using, you may want to select the correct "wildcard". Postgresql and MySQL use "%", but for Sqlite, if you execute a LIKE query, you really want to use "*" (although for ILIKE it is "%", it is confusing).

I'm going to guess that you are using SQLite, since the above query does not work, so repeat it with SQLite if you want case-sensitive with a partial string to match: Activity.name % "*Physics*" and for case insensitivity register: Activity.name ** "%Physics%" .

http://www.sqlite.org/lang_expr.html#like

Answer 2

You can also use these query methods: .contains(substring) , .startswith(prefix) , .endswith(suffix) .

For example, your where clause might be:

 .where(Activity.name.contains("Physics")) 

I find this to be case insensitive and behaves the same as LIKE '%Physics%' .