On Fri, Jul 3, 2020, at 9:21 AM, Pedro Ferreira wrote:
> Hi,
>
> I was wondering if there's some way to create some sort of a "virtual"
> model which is not based on an actual table?
>
> Use case: I have a model (e.g. `Room`) which contains a `data` JSONB
> attribute/column which is supposed to store plugin-specific data. I
> would like plugin code to be able to create a "virtual" model which
> references `Room` 1-to-1 and injects a `plugin_data` backref into it.
> That means I would be able to do `room.plugin_data.user`, for instance,
> which would use `room.data['user_id']` to fetch a `User` object.
>
> Right now, our plugins do this by means of an additional table. E.g., in
> this case we would have `PluginRoom`, which would store `user_id` and
> have a 1-to-1 relationship with `Room`, with a backref.
>
> I was wondering if there's some way we can have a similar pattern based
> on unstructured JSONB data.
Python can do this using patterns like the `__getattr__` method. here's a 30
second proof of concept. note the use of flag_modified() to ensure that an
attribute set is flagged for unit of work update.
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.orm.attributes import flag_modified
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
_data = Column(JSONB, default={})
def __init__(self, **kw):
self._data = {}
self._data.update(kw)
def __getattr__(self, key):
if key.startswith("_"):
raise AttributeError(key)
return self._data[key]
def __setattr__(self, key, value):
if key.startswith("_"):
object.__setattr__(self, key, value)
else:
self._data[key] = value
flag_modified(self, "_data")
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
a1 = A(foo="bar", bar="bat")
s.add(a1)
s.commit()
assert s.query(A._data).first() == ({"foo": "bar", "bar": "bat"},)
a1.bar = "new bat"
s.commit()
assert s.query(A._data).first() == ({"foo": "bar", "bar": "new bat"},)
>
> Cheers,
>
> Pedro
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5581edb1-3cbe-2179-f925-dbeaaa7523b8%40cern.ch.
>
>
> *Attachments:*
> * signature.asc
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c7e0b7f2-49fd-4fce-8cd6-b4b4ecca530b%40www.fastmail.com.
