Requête Flask-SQLAlchemy insensible à la casse

Question

J'utilise Flask-SQLAlchemy pour effectuer des requêtes dans une base de données d'utilisateurs.

user = models.User.query.filter_by(username="ganye").first()

retournera

<User u'ganye'>

en faisant

user = models.User.query.filter_by(username="GANYE").first()

renvoie à

None

Je me demande s'il existe un moyen d'interroger la base de données sans tenir compte des majuscules et des minuscules, de sorte que le deuxième exemple renvoie toujours les résultats suivants

<User u'ganye'>

Answer 1

Vous pouvez le faire en utilisant soit le lower o upper fonctions dans votre filtre :

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

Une autre option consiste à effectuer une recherche en utilisant ilike au lieu de like :

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

Answer 2

En améliorant la réponse de @plaes, celle-ci rendra la requête plus courte si vous spécifiez seulement la ou les colonnes dont vous avez besoin :

user = models.User.query.with_entities(models.User.username).\
filter(models.User.username.ilike("%ganye%")).all()

L'exemple ci-dessus est très utile dans le cas où l'on a besoin d'utiliser le jsonify de Flask à des fins d'AJAX, puis d'y accéder dans votre javascript à l'aide des éléments suivants résultat des données :

from flask import jsonify
jsonify(result=user)

Answer 3

Vous pouvez faire

user = db.session.query(User).filter_by(func.lower(User.username)==func.lower("GANYE")).first()

Ou vous pouvez utiliser la fonction ilike

 user = db.session.query(User).filter_by(User.username.ilike("%ganye%")).first()

Answer 4

Si cela correspond à votre cas d'utilisation, vous pouvez envisager de définir un modèle personnalisé de collation sur la colonne, de sorte que la colonne traite automatiquement les comparaisons de manière insensible à la casse.

Il est intéressant de noter :

• La collation s'appliquera à toutes les requêtes sur la colonne.
• la collation s'appliquera à ORDER BY également des clauses
• une collation peut être spécifiée directement dans les requêtes, plutôt que d'être définie sur la colonne.
  • cela peut potentiellement entraîner des pénalités de performance
• Les définitions de collation sont généralement spécifiques au SGBDR, à la locale ou à la langue - consultez la documentation pertinente.
• Les noms de collation peuvent ne pas être portables entre différents SGBDR.
• les attributs de collation disponibles peuvent varier selon le SGBDR

en d'autres termes, consultez la documentation de votre SGBDR avant d'utiliser cette fonctionnalité.

Cet exemple script montre comment vous pouvez utiliser les collations pour MySQL, Postgresql (notez la casse spéciale) et Sqlite ; chaque SGBDR renvoie les trois résultats possibles pour la valeur de la requête.

import sqlalchemy as sa
from sqlalchemy import orm

data = {
    'mysql': ('mysql:///test', 'utf8mb4_general_ci'),
    'postgresql': ('postgresql:///test', 'coll'),
    'sqlite': ('sqlite://', 'NOCASE'),
}

for dialect, (uri, collation) in data.items():
    Base = orm.declarative_base()

    class Test(Base):
        __tablename__ = 't16573095'

        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.String(32, collation=collation))

    engine = sa.create_engine(uri, echo=False, future=True)
    Base.metadata.drop_all(engine)

    if dialect == 'postgresql':
        # Postgres collations are more complicated
        # - read the docs!
        with engine.begin() as conn:
            conn.execute(sa.text('DROP COLLATION IF EXISTS coll'))
            stmt = """CREATE COLLATION coll (provider='icu', locale='und-u-ks-level2', deterministic=false)"""
            conn.execute(sa.text(stmt))

    Base.metadata.create_all(engine)
    Session = orm.sessionmaker(engine, future=True)

    with Session.begin() as s:
        instances = [Test(name=name) for name in ['GANYE', 'ganye', 'gAnYe']]
        s.add_all(instances)

    with Session() as s:
        results = s.execute(sa.select(Test.name).where(Test.name == 'GaNyE')).scalars()
        print(f'{dialect:-<12}')
        for name in results:
            print(name)
        print('-' * 12)

Pour spécifier une collation dans une requête, utilisez l'attribut collationner méthode :

with Session() as s:
    query = sa.select(Test).where(Test.name.collate('coll') == 'GaNyE')
    results = s.execute(query)