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(username) == func.lower(username) 检查它们).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.
我并不反对手动编写迁移(因为我假设它是可能的,而不是 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?
提前致谢乔
我通过在迁移中添加以下行解决了这个问题.
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
方法手动添加 UNIQUE INDEX
.将类似的内容添加到您的迁移脚本中.
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
以便您可以访问 username
的规范化表示.
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屋!