SQLAlchemy 列到行转换，反之亦然——这可能吗? [英] SQLAlchemy Column to Row Transformation and vice versa -- is it possible?

问题描述

我正在寻找一种仅用于 SQLAlchemy 的解决方案，用于将从表单提交中收到的 dict 转换为数据库中的一系列行，每个提交的字段对应一个行.这是为了处理在应用程序中差异很大的首选项和设置.但是，它很可能适用于创建类似功能的数据透视表.我已经在 ETL 工具中看到过这种类型的事情，但我正在寻找一种直接在 ORM 中执行此操作的方法.我找不到关于它的任何文档，但也许我错过了一些东西.

I'm looking for a SQLAlchemy only solution for converting a dict received from a form submission into a series of rows in the database, one for each field submitted. This is to handle preferences and settings that vary widely across applications. But, it's very likely applicable to creating pivot table like functionality. I've seen this type of thing in ETL tools but I was looking for a way to do it directly in the ORM. I couldn't find any documentation on it but maybe I missed something.

示例:

从表单提交:{"UniqueId":1, "a":23, "b":"Hello", "c":"World"}

Submitted from form: {"UniqueId":1, "a":23, "b":"Hello", "c":"World"}

我希望它被转换(在 ORM 中)，以便它像这样记录在数据库中:

I would like it to be transformed (in the ORM) so that it is recorded in the database like this:

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

选择后，结果将(在 ORM 中)转换回来自每个单独值的一行数据.

Upon a select the result would be transformed (in the ORM) back into a row of data from each of the individual values.

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

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

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

我认为更新时最好的做法是将删除/创建包装在事务中，以便删除当前记录并插入新记录.

I would assume on an update that the best course of action would be to wrap a delete/create in a transaction so the current records would be removed and the new ones inserted.

ItemName 的最终列表将保存在单独的表中.

The definitive list of ItemNames will be maintained in a separate table.

对更优雅的解决方案完全开放，但希望尽可能远离数据库端.

Totally open to more elegant solutions but would like to keep out of the database side if at all possible.

我在 SQLAlchemy 中使用 declarative_base 方法.

I'm using the declarative_base approach with SQLAlchemy.

提前致谢...

干杯，

保罗

推荐答案

这里是 文档 处理映射到模型中字典的表结构:

Here is a slightly modified example from documentation to work with such table structure mapped to dictionary in model:

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()

这篇关于SQLAlchemy 列到行转换，反之亦然——这可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！