将外部数据添加到 SQLAlchemy 对象集合

Question

我正在使用flask-sqlalchemy和Postgres DB构建基于Flask的API应用程序。

• 数据库中users表保存每个用户的记录。此表的PK username(这里没有什么特别的(
• 数据库中performance表保存用户性能。此表的 PK 是日期。此表中每个用户都有一列，由 username 命名。(我知道有人说这种结构不理想 - 但它是由不相关的要求所要求的(。

例子：

PK = "username", tablename = "users"
| username | firstname | lastname | 
-----------------------------------
| alice    | Alice     | Johns    |
| bob      | Bob       | Speed    |

PK = "timestamp", tablename = "performance"
| timestamp | alice | bob  | 
-----------------------------------
| 2017-11-2 | 1     | 5    |
| 2017-11-3 | 6     | 9    |

我使用 SQLAlchemy 来访问users表。

我愿意创建 REST API，它将接收一个 Date 参数并返回所有用户的集合及其在该日期的性能。

什么是正确的 SQLAlchemy 查询，因此不会为每个用户单独选择性能值。

Answer 1

如果绝对无法更改架构，则可以取消透视性能数据并与用户联接。关系的示例映射，因为没有提供：

In [2]: class User(Base):
   ...:     __tablename__ = "users"
   ...:     username = Column(Unicode(255), primary_key=True)
   ...:     firstname = Column(Unicode(255))
   ...:     lastname = Column(Unicode(255))
   ...:     
In [3]: class Performance(Base):
   ...:     __tablename__ = "performance"
   ...:     timestamp = Column(Date, primary_key=True)
   ...:     # NOTE: this works **only** in a class body context,
   ...:     # and your schema shouldn't be like this anyway.
   ...:     for name in ['alice', 'bob']:
   ...:         locals()[name] = Column(Integer, nullable=False)
   ...:         

使用检查获取具有性能数据的用户名。您还可以保留用户名的静态列表：

In [11]: users = inspect(Performance).attrs.keys()[1:]
In [12]: users
Out[12]: ['alice', 'bob']

形成非透视查询 - 只有一种方法：

In [15]: from sqlalchemy.dialects import postgresql
    ...: performance = session.query(
    ...:         func.unnest(postgresql.array(users)).label('username'),
    ...:         func.unnest(postgresql.array(
    ...:             [getattr(Performance, name)
    ...:              for name in users])).label('value')).
    ...:     filter(Performance.timestamp == '2017-11-2').
    ...:     subquery()
    ...: 

将用户与其性能值联接：

In [24]: session.query(User, performance.c.value).
    ...:     join(performance, performance.c.username == User.username).
    ...:     all()
Out[24]: 
[(<__main__.User at 0x7f79eb5d2c88>, 1),
 (<__main__.User at 0x7f79eb5d2cf8>, 5)]

---

如果要更改架构以使性能数据存储为(timestamp, username, value)元组，则只需执行以下操作：

In [2]: class User(Base):
   ...:     __tablename__ = "users"
   ...:     ...
   ...:     performance = relationship("BetterPerformance")
   ...:
In [25]: class BetterPerformance(Base):
    ...:     __tablename__ = "better_performance"
    ...:     timestamp = Column(Date, primary_key=True)
    ...:     username = Column(ForeignKey('users.username'), primary_key=True)
    ...:     value = Column(Integer, nullable=False)
    ...: 
In [13]: session.query(User, BetterPerformance.value).
    ...:     join(User.performance).
    ...:     filter(BetterPerformance.timestamp == '2017-11-2').
    ...:     all()
Out[13]: 
[(<__main__.User at 0x7f6ae3282c18>, 1),
 (<__main__.User at 0x7f6ae3282ba8>, 5)]

甚至：

In [17]: session.query(User).
    ...:     join(User.performance).
    ...:     options(contains_eager(User.performance)).
    ...:     filter(BetterPerformance.timestamp == '2017-11-2').
    ...:     all()
Out[17]: [<__main__.User at 0x7f6ae3282c18>, <__main__.User at 0x7f6ae3282ba8>]
In [18]: [u.performance[0].value for u in _]
Out[18]: [1, 5]