使用 SQLAlchemy 的多语言文本字段
我目前正在评估一个项目的 SQLAlchemy。这是我的架构：
- 一个 LANGUAGE 表，其中包含支持的每种语言的一行
- TRANSLATION 表包含 (ID, LANGUAGE_ID, STR)
- 各种表将存储 TRANSLATION_ID，而不是存储文本，例如 BOOK(ID, TITLE_TRANSLATION_ID, ABSTRACT_TRANSLATION_ID)
现在，假设每个请求都有可用的当前语言 ID（例如，通过线程变量...），我需要 SQLAlchemy 自动加入 TRANSLATION 表，因此具有当前语言的文本字段。类似于：
class Book(Base):
id = Column(Integer, primary_key=True)
title = TranslatableText()
abstract = TranslatableText()
检索时，ORM 会自动使用当前语言 ID 连接到 TRANSLATION 表，而 my_book.title 会给出当前语言的标题。
我还需要它来跨关系工作：如果一个类包含其他类的外键，而这些类也包含可翻译的文本字段，那么我希望也能检索到这些外键。
最后，我还需要能够获取每个字段的 TRANSLATION_ID，例如通过 my_book.title_translation_id。
我并不期待一个完整的解决方案，但我想知道这样的事情是否可行，以及从哪里开始。
I am currently evaluating SQLAlchemy for a project. Here is my schema:
- a LANGUAGE table, with a row for each language supported
- a TRANSLATION table with (ID, LANGUAGE_ID, STR)
- various tables will, instead of storing text, store TRANSLATION_IDs, for example, BOOK(ID, TITLE_TRANSLATION_ID, ABSTRACT_TRANSLATION_ID)
Now, assuming each request has the current language ID available (for example, through a thread variable...), I would need SQLAlchemy to automatically join the TRANSLATION table, and thus have text fields in the current language. Something like:
class Book(Base):
id = Column(Integer, primary_key=True)
title = TranslatableText()
abstract = TranslatableText()
When retrieving, the ORM would automatically join to the TRANSLATION table with the current language ID, and my_book.title would give me the title in the current language.
I also need this to work across relations: if a class contains foreign keys to other classes that also contain translatable text fields, I would ideally like those to be retrieved too.
Lastly, I would also need to be able to get to the TRANSLATION_ID for each field, for example through my_book.title_translation_id.
I am not expecting a complete solution, but I'd like to know if something like this is feasible, and where to start.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您必须使用 的概念http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html#mixin-and-custom-base-classes
创建一个顶级类并编写一些函数，例如 read、写作和创造。始终调用该函数来创建数据库或从数据库读取数据。
如果您不想实现 mixin 类，那么您也可以使用事件 http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.MapperEvents.translate_row
You have to use the concept of http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html#mixin-and-custom-base-classes
Create one top level class and write some funciton like read, write and create. Always call that function to create or read data from the database.
If you dont want to implement the mixin classes then also you can use event http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.MapperEvents.translate_row
我知道我的答案迟到了，也许它对这个新版本的 sqlalchemy 有帮助，
就像 Sqlalchemy 中的
String类型一样，有名为Unicode的类型，也适用于Text< /code> 替代方案是UnicodeText。这些类型帮助生成带有 N 前缀的查询。
例如，要在 MSSQL 中编写多种语言，您必须添加
N作为查询前缀，如下所示，但 sqlalchemy 的字符串不会添加 N 前缀，但 Unicode 添加前缀。
另外，Unicode 是 SQLAlchemy 中 String 的子类，因此您可以使用与 String 相同的内容。
I know my answer is late, may be it help in this new version of sqlalchemy
Just Like
StringType in Sqlalchemy there is type namedUnicodeand Also forTextalternative there isUnicodeText.these Type Helps generate query with N Prefix.
For Example For Writing Multiple Language in MSSQL You Have to Add
Nas prefix to query like given belowbut sqlalchemy's String Does not add the N prefix but the Unicode Adds the Prefix.
Also Unicode is SubClass of String in SQLAlchemy so you can use just same like String.