String matching in Peewee (SQL)

Question

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

For instance, if I want only activities 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 doesn't give any errors, but doesn't work correctly.

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

Virility

Answer 1

Quick answer:

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

---

Depending on the database backend you're using you'll want to pick the right "wildcard". Postgresql and MySQL use "%", but for Sqlite if you're performing a LIKE query you will actually want to use "*" (although for ILIKE it is "%", confusing).

I'm going to guess you're using SQLite since the above query is failing, so to recap, with SQLite if you want case-sensitive partial-string matching: Activity.name % "*Physics*", and for case-insensitive: Activity.name ** "%Physics%".

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

Highland

Thanks. It's very confusing indeed. I was using SQLite for development, but I'll most likely switch to PostgreSQL for production, so that means that I would have to change it? – Virility

Yeah, I may need to add an attribute like db.wildcard so your code will be portable, though of course you can add that to your own code as well. – Highland

You can use Activity.name.contains('Physics') and it should do the right thing! – Highland

Answer 2

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

For example, your where clause could be:

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

I believe this is case-insensitive and behaves the same as LIKE '%Physics%'.

Coldiron

This is the best answer. – Highland

This is a simpler and better solution. Please mark it as the Accepted answer. – Interglacial

This works in the case-insensitive scenario, how about for the case-sensitive scenario? – Fetch