SQLAlchemy 列到行转换及反向操作--可能吗？

Question

我在寻找一个只用SQLAlchemy的解决方案，目的是把从表单提交过来的字典转换成数据库中的一系列行，每个字段对应一行。这是为了处理在不同应用中变化很大的偏好和设置。不过，这种方法很可能也适用于创建类似数据透视表的功能。我在ETL工具中见过这种情况，但我想找一种直接在ORM中实现的方法。我没有找到相关的文档，可能是我漏掉了什么。

举个例子：

从表单提交的数据是：{"UniqueId":1, "a":23, "b":"Hello", "c":"World"}

我希望在ORM中将其转换成这样记录在数据库中：

_______________________________________
|UniqueId| ItemName   | ItemValue     |
---------------------------------------
|  1     |    a       |    23         |
---------------------------------------
|  1     |    b       |    Hello      |
---------------------------------------
|  1     |    c       |    World      |
---------------------------------------

在进行查询时，结果会在ORM中被转换回每个单独值的一行数据。

---------------------------------------------------
| UniqueId  |  a     |     b      |       c       |

---------------------------------------------------
|   1       |  23    |   Hello    |   World       |

---------------------------------------------------

我认为在更新时，最好的做法是把删除和创建操作放在一个事务中，这样可以先删除当前记录，再插入新的记录。

ItemNames的确切列表会保存在一个单独的表中。

我完全欢迎更优雅的解决方案，但如果可能的话，希望尽量避免数据库那边的操作。

我正在使用SQLAlchemy的声明性基础方法。

提前谢谢你们...

祝好，

保罗

Answer 1

这里有一个稍微修改过的例子，来自于文档，它展示了如何处理这种表结构，并将其映射到模型中的字典：

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import relation, sessionmaker

metadata  = MetaData()
Base = declarative_base(metadata=metadata, name='Base')

class Item(Base):

    __tablename__ = 'Item'
    UniqueId = Column(Integer, ForeignKey('ItemSet.UniqueId'),
                      primary_key=True)
    ItemSet = relation('ItemSet')
    ItemName = Column(String(10), primary_key=True)
    ItemValue = Column(Text) # Use PickleType?

def _create_item(ItemName, ItemValue):
    return Item(ItemName=ItemName, ItemValue=ItemValue)

class ItemSet(Base):

    __tablename__ = 'ItemSet'
    UniqueId = Column(Integer, primary_key=True)
    _items = relation(Item,
                      collection_class=attribute_mapped_collection('ItemName'))
    items = association_proxy('_items', 'ItemValue', creator=_create_item)

engine = create_engine('sqlite://', echo=True)
metadata.create_all(engine)

session = sessionmaker(bind=engine)()
data = {"UniqueId": 1, "a": 23, "b": "Hello", "c": "World"}
s = ItemSet(UniqueId=data.pop("UniqueId"))
s.items = data
session.add(s)
session.commit()