SQLAlchemy Column to Row Transformation and vice versa - is this possible?

Question

I am looking for the only SQLAlchemy solution to convert a dict obtained from a form view into a series of rows in a database, one for each field presented. This applies to settings and settings that vary widely across applications. But this is most likely applicable for creating a pivot table, such as functionality. I saw such things in ETL tools, but I was looking for a way to do this directly in ORM. I could not find the documentation, but maybe I missed something.

Example:

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

I would like it to be converted (in ORM) so that it is written to the database as follows:

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

After selection, the result will be converted (in ORM) back to the data string from each of the individual values.

 --------------------------------------------------- | UniqueId | a | b | c | --------------------------------------------------- | 1 | 23 | Hello | World | --------------------------------------------------- 

I would suggest that when upgrading, it would be best to wrap the delete / create in the transaction so that the current records are deleted and new ones are inserted.

The final list of ItemNames will be saved in a separate table.

Fully open to more elegant solutions, but, if at all possible, will avoid using a database.

I am using the declarative_base method with SQLAlchemy.

Thanks in advance...

Greetings

Floor

Answer 1

The following is a slightly modified example from the documentation for working with such a table structure displayed in the dictionary in the 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()