Contains does not work as expected with % and _

[StefanosChaliasos]

Consider a table with the following values:

id | name
-- | -------
1 | foo
2 | foo%
3 | foo%bar
4 | foobar

When I use the contains method with "%" as an argument, I expect to return foo% and foo%bar, but peewee returns all the columns of the table.

Example query:

Table1.select(Table1.name).where(Table1.name.contains("'%")).objects()

I expected that contains abstracts the semantics of SQL and properly handles the special characters.

[coleifer]

What database are you using?

[StefanosChaliasos]

Ι've tested it on sqlite and mysql

[coleifer]

This is working fine for me on both Sqlite and Postgresql:

In [1]: from peewee import *                                                                    

In [2]: db = SqliteDatabase(':memory:')                                                         

In [3]: class User(Model): 
   ...:     username = TextField() 
   ...:     class Meta: 
   ...:         database = db 
   ...:         table_name = 'users' 
   ...:                                                                                         

In [4]: db.create_tables([User])                                                                

In [5]: usernames = ['foo', 'foo%', 'foo%bar', 'foobar']                                        

In [6]: User.insert_many([(u,) for u in usernames], [User.username]).execute()                  
Out[6]: 4

In [7]: User.select().where(User.username.contains('%'))[:]                                     
Out[7]: [<User: 2>, <User: 3>]

In [8]: db = PostgresqlDatabase('peewee_test')                                                  

In [9]: User._meta.set_database(db)                                                             

In [10]: db.create_tables([User])                                                               

In [11]: User.insert_many([(u,) for u in usernames], [User.username]).execute()                 
Out[11]: <peewee.ModelTupleCursorWrapper at 0x7f17a8da8160>

In [12]: User.select().where(User.username.contains('%'))[:]                                    
Out[12]: [<User: 2>, <User: 3>]

[coleifer]

For MySQL, simply use a backslash to escape the wildcard characters:

.where(User.username.contains('\\%'))

[StefanosChaliasos]

Strange, I run the following:

In [1]: from peewee import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class User(Model):
   ...:     username = TextField()
   ...:     class Meta:
   ...:         database = db
   ...:         table_name = 'users'
   ...:

In [4]: usernames = ['foo', 'foo%', 'foo%bar', 'foobar']

In [5]: db.create_tables([User])

In [6]: User.insert_many([(u,) for u in usernames], [User.username]).execute()
Out[6]: 4

In [7]: query = User.select().where(User.username.contains('%'))

In [8]: print(query)
SELECT "t1"."id", "t1"."username" FROM "users" AS "t1" WHERE ("t1"."username" LIKE '%%%')

In [9]: print(query[:])
[<User: 1>, <User: 2>, <User: 3>, <User: 4>]

I use peewee-3.13.3 and sqlite 3.32.3.

The generated SQL query is not equivalent to what I expect.

The correct query should be something like the following

SELECT "t1"."id", "t1"."username" FROM "users" AS "t1" WHERE ("t1"."username" LIKE '%\%%' ESCAPE '\')

[coleifer]

I must be insane. I think I put in a tiny code-change and had loaded that into memory when I generated that output, because now I'm replicating your output. I will fix this for the common case, thanks for reporting.