Case Insensitive Flask-Sqlalchemy Query

Case Insensitive Flask-SQLAlchemy Query

You can do it by using either the lower or upper functions in your filter:

from sqlalchemy import func
user = models.User.query.filter(func.lower(User.username) == func.lower("GaNyE")).first()

Another option is to do searching using ilike instead of like:

.query.filter(Model.column.ilike("ganye"))

Trying to make a query case insensitive in Flask-SQLAlchemy

You should use filter instead of filter_by:

from sqlalchemy import func

result = db.session.query(countries).filter(
   func.lower(countries.name) == name.lower(),
   func.lower(countries.capital) == capital.lower()
).first()

If countries is a table (not model) you should use countries.c.name syntax.

Similar topic: Case Insensitive Flask-SQLAlchemy Query.

Case-insensitive exact match with SQLAlchemy

If you need only case-insensitivity use upper or lower since like is not only about case-insensitivity

example of lower:

my_string = 'BarFoo'
session.query(Foo).filter(func.lower(Foo.bar) == my_string.lower()).all()

see some more info on like here how to execute LIKE query in sqlalchemy?

SQLAlchemy and multi-column case-insensitive query

You would need to build the query looping through each key of the dictionary.

As you didn't give any code sample, I'm going to call the table model class TableModel and each column will be column_1, column_2, etc.

Something like this should work:

d = {'column_1': 'some_string', 'column_3': 'another_string'}
# skipping 'column_2' just to exemplify how every column is optional in the dictionary

my_query = TableModel.query

for k in d:
    my_query = my_query.filter(getattr(TableModel, k).ilike(d[k]))

And that's about it. Afterwards you can use my_query as any other query, e.g., my_query.count() or my_query.all()

SqlAlchemy like filter case insensitive but it should be case sensitive

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()])

SQLAlchemy case insensitive IN based search query?

This should compile exactly...

query( models.Object )\
.filter( 
     sqlalchemy.func.upper( models.Object.fieldname )\
     .in_( (sqlalchemy.func.upper(foo) , sqlalchemy.func.upper(bar), ) )
)\
.all()

---

1. you could also just pass in uppercase text. personally, i would do in_( foo.uppercase() , bar.uppercase() )

2. SqlAlchemy works with the DBAPI to pass bind parameters into your backend datastore. Translation -- values are automatically escaped.

---

if you want to do a list of strings , something like this should work

.in_( [ i.upper() for i in inputs ] )
.in_( [ sqlalchemy.func.upper(i) for i in inputs ] )

---

Just want to add that if you want to optimize these selects for speed, and are on Postgres or Oracle, you can create a 'function index'

CREATE INDEX table_fieldname_lower_idx ON table(lower(fieldname))

the query planner (in the database) will know to use that lower(fieldname) index when searching against a lower(fieldname) query.

How to change sqlalchemy Oracle dialect use upper case for case insensitive?

After reading the source code I found a way, writing my own dialect to get case insensitive.

class MyOracleIdentifierPreparer(OracleIdentifierPreparer):

    def _requires_quotes(self, value):
        """Return True if the given identifier requires quoting."""
        lc_value = value.lower()
        legal_characters = re.compile(r"^[A-Z0-9_$.]+$", re.I)
        # uc_value = value.upper()
        return (
            lc_value in self.reserved_words
            or value[0] in self.illegal_initial_characters
            or not legal_characters.match(str(value))
            # remove check of case!
            # or ((lc_value != value) and (uc_value != value)) 
        )

class NoCaseDialect(oracle.dialect):
    """replace preparer"""

    preparer = MyOracleIdentifierPreparer
    supports_statement_cache = True
    # requires_name_normalize = False

---

Related Topics