不区分大小写的Flask-SQLAlchemy查询

Question

我正在使用Flask-SQLAlchemy从用户数据库中查询数据; 然而，

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

将返回

<User u'ganye'>

进行中

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

返回

None

我想知道是否有一种方法可以不区分大小写地查询数据库，以便第二个示例仍然会返回。

<User u'ganye'>

- Ganye

Answer 1

你可以通过在过滤器中使用lower或upper函数来实现：

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

另一个选项是使用 ilike 而不是 like 进行搜索：

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

- plaes

---

相较于在用户名列创建索引的 filter_by 方法，这会使查询变慢吗？ - CaptainDaVinci

Answer 2

在@plaes的答案基础上进行改进，如果只指定所需的列，则该查询将变得更短：

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

如果需要使用Flask的jsonify进行AJAX处理并且在JavaScript中通过 data.result 访问它，则上面的示例非常有用：

The above example is very useful in case one needs to use Flask's jsonify for AJAX purposes and then in your javascript access it using data.result:

from flask import jsonify
jsonify(result=user)

- iChux

---

@VedranŠego 我已经看到这个链接了，感谢提供参考。我也会进行自己的测试。 - iChux

Answer 3

你可以做

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

或者您可以使用 ilike 函数。

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

- Mohammad Aarif

---

第二个选项不应该有百分数。 - axwell

你是不是想用filter()而不是filter_by()？根据https://dev59.com/k3I95IYBdhLWcg3w2Rvz，我认为filter_by()只能使用关键字而不是表达式。 - Lou K

Answer 4

我认为最好的解决方案是

func.lower(User.username).contains(username.lower())

它将生成类似于 SQL 的语句

WHERE (lower(user.username) LIKE '%%' || %s || '%%')

这意味着您的数据库用户名将被转换为小写，并且您的Python值用户名也将被转换为小写。因此，现在您无需担心大小写敏感性。

如果您的用户名是Sara Smith，那么ara，sar，Sara或甚至a S都可以使用。

- Никита Филонов

Answer 5

如果符合您的使用情况，您可以考虑在列上设置自定义排序规则，这样该列就会自动以不区分大小写的方式处理比较。

值得注意的是：

• 排序规则将适用于该列上的所有查询
• 排序规则也将适用于ORDER BY子句
• 可以直接在查询中指定排序规则，而不是在列上定义
  • 这可能会导致性能损失
• 排序规则定义往往是特定于RDBMS / 区域设置 / 语言的 - 请参阅相关文档
• 排序规则名称可能无法在不同的RDBMS之间移植
• 可用的排序规则属性可能因RDBMS而异

换句话说，在使用此功能之前，请查阅您的RDBMS文档。

此示例脚本展示了如何在MySQL、Postgresql（注意特殊处理）和Sqlite中使用排序规则；每个RDBMS都返回查询值的三种可能结果。

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)

在查询中指定排序规则，请使用属性的collate方法：

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

- snakecharmerb

Answer 6

对于声明式用户来说，不使用query的等效方法是ColumnOperators.ilike方法。

class User(DeclarativeBase):
    username: str

req = select(User).where(User.username.ilike("GANYE"))
result = session.execute(req)

将呈现为lower(User.username) LIKE lower("GANYE")

这在SQLAlchemy 2.0中仍然适用，并且与AsyncSession兼容，适用于使用它的人。

- Trimatix

---

2.0版本的方法没有改变 - 它与之前的版本完全相同。 - undefined

非常感谢你的帮助，你发现得真准，我已经更新了答案。 - undefined

Answer 7

• 根据一个字段

  uname = "ganye"
  user = models.User.query.filter(User.username.ilikef(f"%{uname}%").first()
  

• 根据多个字段

  return db.session.query(Post).filter(
      Post.title.ilike(f"%{search_query}%")
      | Post.body.ilike(f"%{search_query}%")
  )
  

- inauris