SqlAlchemy like filter case insensitive but it should be case sensitive

Question

I am using SqlAlchemy for a database, but I have a problem when using like function in queries.

My database is SQLite.

My request is like this:

self.session.query(Value.scan).filter(Value.current_value.like("%" + search + "%"), Value.tag == Tag.tag, Tag.visible == True).distinct().all()

The column Value.current_value is a String, you can see the declaration here:

class Value(Base):
    current_value = Column(String, nullable=False)

search variable is a str coming from a rapid search bar, and is case sensitive (I never call lower or upper on it).

I want to do a case sensitive search, but the results are case insensitive.

I did some research and like should be case sensitive, and ilike case insensitive, so I don't understand why it's case insensitive.

Should I choose another type for my column that has to be case sensitive?

Another strange thing is that I have the same problem when using the function contains on the same column (case insensitive result), but not when using operators like ==, !=, <, or > (case sensitive result)

Does semeone knows why it's case sensitive with operators, but not with like and contains functions?

Best regards

Lucie

Answer 1

In SQLite, LIKE is by default case insensitive.

What I had to do is activating the case_sensitive_like pragma.

I created a class to activate the pragma like this:

class ForeignKeysListener(PoolListener):
    """
    Class to activate the pragma case_sensitive_like, that makes the
    like and contains functions case sensitive
    """
    def connect(self, dbapi_con, con_record):
        db_cursor = dbapi_con.execute('pragma case_sensitive_like=ON')

When you create the engine, you just have to add the listener like this:

engine = create_engine(
    'sqlite:///' + os.path.join(self.folder, 'database', 'mia2.db'),
    listeners=[ForeignKeysListener()])