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

问题描述

我正在寻找一种仅用于SQLAlchemy的解决方案，用于将从表单提交接收到的字典转换为数据库中的一系列行，每个提交字段对应一个行.这是为了处理在不同应用程序之间差异很大的首选项和设置.但是，它很可能适用于创建数据透视表之类的功能.我已经在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":你好"，"c":世界"}

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.

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

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屋！