Python – SQLAlchethe Column to Row Transformation and vice versa — is it possible

ormpivot tablepythonsqlalchemytransformation

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.

Example:

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

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

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.

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.

I'm using the declarative_base approach with SQLAlchemy.

Thanks in advance…

Cheers,

Paul

Best Answer

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