python - SQLAlchemy 中的数据透视表

Question

想象一个由日期和名称作为主键的表。

|Date|Name|Value|
|D1  |   A|    2|
|D1  |   B|    3|
|D2  |   A|    3|
|D2  |   C|    1|

如何编写一个 SQLAlchemy 查询生成(日期，值)对，其中值是该日期所有名称的所有值？值元组的长度为 len(distinct(Name))，缺失值将包含一些标记值(例如 None)。

i.e.
[
(D1, 2, 3, None),
(D2, 3, None, 1),
...
]

我不是为了让我批准而在这里要求完整的答案。如果您能告诉我要在文档中搜索/阅读哪些概念(或者实际上，请告诉我任何有用的或相关的内容)，我会(尝试)自己弄清楚并发布更新.

[请不要使用 pandas 解决方案]

简单的答案是以长格式加载查询并使用 numpy.searchsorted 在 numpy 中进行旋转，但如果提交的答案使用它，我会对一些性能诊断感兴趣，以便与简单的解决方案进行比较。

Answer 1

我建议不要在 SQL Server 端执行此操作，除非您有大量数据(即使在这种情况下，我也不确定它是否会对性能产生显着的积极影响)。

相反，在他们擅长的地方使用不同的工具:

• MS-SQL(或其他 RDBMS)存储数据
• SQLAlchemy 插入/查询该数据
• numpy 例程或 pandas.pivot 来旋转数据

下面的代码是独立的，可以运行(使用 sqlite)并显示如何获得您想要的最终结果:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import pandas as pd

_db_uri = 'sqlite:///:memory:'
engine = create_engine(_db_uri, echo=True)
Session = sessionmaker(bind=engine)

Base = declarative_base(engine)

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    date = Column(String)
    name = Column(String)
    value = Column(Integer)

def _test():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    session = Session()

    def _add_test_data():
        rows = [
            MyTable(date="D1", name="A", value=2),
            MyTable(date="D1", name="B", value=3),
            MyTable(date="D2", name="A", value=3),
            MyTable(date="D2", name="C", value=1),
        ]
        session.add_all(rows)
        session.commit()

    # create test data
    _add_test_data()

    # use `sa` to query data from the database
    # q = session.query(MyTable)  # all columns
    q = session.query(MyTable.date, MyTable.name, MyTable.value)  # explicit

    # read data into pandas directly from the query `q`
    df = pd.read_sql(q.statement, q.session.bind)
    print(df)

    # pivot the results
    df_pivot = df.pivot(index="date", columns="name", values="value")
    print(df_pivot)


if __name__ == '__main__':
    _test()