别名化SQLAlchemy模型或基础SQL表中的字段名称 [英] Aliasing field names in SQLAlchemy model or underlying SQL table

问题描述

是否可以在表的架构或模型的类定义中为(My)SQL列或SQLAlchemy对象属性添加别名?

Is it possible to alias (My)SQL columns or SQLAlchemy object attributes in either the table's schema or the model's class definition?

例如，给定下表:

Users
---------------------
username | ...

我希望使用以下表格表示形式:

I would like to have the following table representation:

Users
---------------------
id | username | ...

其中User.id映射到User.username而不重复此数据.

Where User.id maps to User.username without duplicating this data.

将其嵌入到表模式中将是理想的，但是使用ORM的解决方案就足够了.

Embedding this into the table schema would be ideal, but a solution with the ORM would be sufficient.

class User():
    __tablename__ = 'Users'
    username = Column(STRING, primary_key=True, alias='id')

---

了解详情

我的用例是，我正在存储来自各个网站的抓取数据.我正在使用我拥有的最佳用户ID为该数据编制索引，该用户ID可能是用户名，数字ID甚至全名.

My use case is that I'm storing scraped data from various websites. I'm indexing this data with the best user ID that I have, which may be a username, numeric ID, or even a full name.

但是，为了规范化跨表的操作，我希望每个表都具有一个"id"字段，并映射到可能的最佳ID.

However, in order to normalize operations across tables I'd like each table to have an 'id' field, mapped to the best possible ID.

我当然有两种选择:

1. 将选定的ID重命名为"Users.id"，从而使分析人员失去可读性.
2. 将所选ID中的数据复制到"Users.id"中，除了浪费存储空间之外，还使数据库写操作更加复杂.

推荐答案

以下是 SQLAlchemy文档，它完全可以满足您的要求:

Here is an elegant solution described in the SQLAlchemy Docs, which does exactly what you want:

from sqlalchemy.orm import synonym
class User():
    __tablename__ = 'Users'
    username = Column(STRING, primary_key=True)
    id = synonym('username')

它按预期方式工作:

>>> john = session.query(User).filter_by(id='John Doe').first()
>>> print([john.id, john.username])
['John Doe', 'John Doe']
>>> john.id is john.username
True

顺便说一句，User类必须是SQLAlchemy declarative_base()类的子类:

By the way, the User class must be a subclass of SQLAlchemy declarative_base() class:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
    ...

转到文档以获取更多参考

这篇关于别名化SQLAlchemy模型或基础SQL表中的字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！