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.

score 30 · Answer 1 · answered Nov 11 '15 at 17:03

30

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%'.

answered Nov 11 '15 at 17:03

jhaskell

485
7
7

1

This is the best answer. – coleifer Nov 20 '15 at 04:57
This is a simpler and better solution. Please mark it as the Accepted answer. – northernman Dec 23 '16 at 02:43
This works in the case-insensitive scenario, how about for the case-sensitive scenario? – WillJones Apr 12 '21 at 12:25

coleifer · Accepted Answer · 2017-04-11T17:05:55.933

19

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

edited Apr 11 '17 at 17:05

answered Dec 15 '13 at 17:21

coleifer

24,887
6
60
75

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? – Ben Dec 15 '13 at 18:02
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. – coleifer Dec 17 '13 at 16:36
1

You can use ``Activity.name.contains('Physics')`` and it should do the right thing! – coleifer Nov 20 '15 at 04:57

String matching in Peewee (SQL)

2 Answers2