Flask-SQLAlchemy小写索引-跳过功能，SQLAlchemy反射不支持 [英] Flask-SQLAlchemy Lower Case Index - skipping functional, not supported by SQLAlchemy reflection

问题描述

首先。抱歉，如果已解决此问题，但我找不到答案。

First off. Apologies if this has been answered but I can not find the answer any where.

我需要在Flask-SQLAlchemy对象上定义小写索引。

I need to define a lowercase index on a Flask-SQLAlchemy object.

我遇到的问题是我需要将模型用户名和电子邮件字段存储为小写，以便可以使用 User.query.filter（func.lower（用户名）== func.lower（用户名）.first（）

The problem I have is I need a models username and email fields to be stored lowercase so that I can check them with User.query.filter(func.lower(username) == func.lower(username)).first()

到目前为止，我一直通过插入这些来处理字段为小写字母，但在此特定情况下，我需要用户名来保留使用它定义的大小写。

Up to now I have always dealt with this by just inserting these fields as lowercase but in this particular instance I need the username to preserve the case that it was defined with.

我认为我走在正确的轨道上，但是正在一个我从未见过的问题。

I think I am on the right tracks but am running in to an issue I have not seen before.

class User(UserMixin, db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    # Indexes for username and email are defined below User
    # They are functional lowercase indexes
    username = db.Column(db.String(32))
    email = db.Column(db.String(255))
    password_hash = db.Column(db.String(255))

    ...

db.Index('ix_users_username', func.lower(User.username), unique=True)
db.Index('ix_users_email', func.lower(User.email), unique=True)

我看不到这个问题。我猜想 func.lower（）要求将索引的定义与我在其他搜索中所知道的分开。

I can't see a problem with this. I am guessing that the func.lower() requires the Index to be defined separate from what I can tell with my other searches.

现在，当我运行迁移时出现问题，我得到以下错误：

Now the problem comes when I run the migration I get the following errors:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
.../lib/python3.4/site-packages/alembic/util/messaging.py:69: UserWarning: autogenerate skipping functional index ix_users_email; not supported by SQLAlchemy reflection
  warnings.warn(msg)
.../lib/python3.4/site-packages/alembic/util/messaging.py:69: UserWarning: autogenerate skipping functional index ix_users_username; not supported by SQLAlchemy reflection

我不确定100％为什么反射不支持此功能。我期望在迁移过程中像以前一样添加它们，但将其包裹在较低位置。

I'm not 100% sure why this isn't supported by reflection. I was expecting in my migration that it would add them as it did before but with it wrapped in lower.

我不反对手动编写迁移（因为我m假设它是可能的，但不是100％不确定怎么做），但是没有人能指出为什么它开箱即用吗？

I am not adverse to writing the migration manually (as I'm presuming its possible, not 100% sure how) but can anyone point out why this doesn't work out of the box?

在此先感谢
Joe

Thanks in advance Joe

我通过在迁移中添加以下行来解决了这个问题。

I solved this by adding the following line in to the migration.

op.create_index('ix_users_username', 'users', [sa.text('lower(username)')])
op.create_index('ix_users_email', 'users', [sa.text('lower(email)')])

推荐答案

您需要使用alembic的 execute 唯一索引 c>方法。在迁移脚本中添加类似的内容。

You'd need to add the UNIQUE INDEX manually using alembic's execute method. Add something like this to your migration script.

from alembic import op
# ...

def upgrade():
    # ...
    op.execute(
        """ CREATE UNIQUE INDEX users_normalized_username
            ON users
            (lower(username))
        """
    )

def downgrade():
    # ...
    op.execute("DROP INDEX users_normalized_username")

您还可以添加 ColumnProperty 用户名的规范化表示。

You could also add a ColumnProperty so you have access the normalized representation of the username.

from sqlalchemy import Column, String, func, orm

class User(Base):
    __tablename__ = 'users'

    username = Column(String(32))
    normalized_username = orm.column_property(func.lower(username))

    # ...

这不会在数据库中创建额外的列，但是您可以查询和比较

This won't create an extra column in your database but you'll be able to query and compare against it.

session.query(User).filter(User.normalized_username == func.lower(username)).first()

这篇关于Flask-SQLAlchemy小写索引-跳过功能，SQLAlchemy反射不支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！