On Jun 2, 2011, at 8:25 PM, Ben Chess wrote:
> On Thu, Jun 2, 2011 at 5:18 PM, Michael Bayer <[email protected]>
> wrote:
>>
>> On Jun 2, 2011, at 8:11 PM, Ben Chess wrote:
>>
>>> I'm not intending for the contents of the BLOB to be readable to
>>> MySQL. It would only be cracked open and read from within Python.
>>> Meaning Python only knows what the key actually is. So yeah, I
>>> understand the caveats of this approach. I merely want to provide a
>>> mechanism to, as a second & separate SELECT query, load the relationed
>>> instance and be able to access it from the child python instance.
>>>
>>> Hopefully that clears some things up.
>>
>> How do you want to identify what row contains the target key ? Is the
>> comparison on the SQL side or the Python side ?
>
> In Python. Below is an example of what I have.
>
> class Account:
> data = Column(JSONType)
>
> @property
> def salesperson(self):
> session = orm.session.object_session(self)
> return session.query(Salesperson, id=self.data['salesperson_id']).one()
OK that comparison, assuming you mean to have a "filter()" in there, is on the
SQL side. The extraction of the comparison value from the JSON is python side.
relationship() really has no way to deal with load or persist operations that
aren't about columns in the two tables being compared. Also, an eager load,
at least as far as being able to load Parent and related Child objects (I'm
using Parent/Child instead of Account/Salesperson here) with less than N+1
queries is not possible here, since that requires a set of rows representing
parents be compared to a set of rows representing children on the SQL side and
that's not an option. The relationship also can't really do too much with SQL
criteria, at least not query.join(), so it would be limited at best with class
level behavior.
So what you would get from a hypothetical relationship are:
1. the attribute is managed, such that it loads when non-populated, remains in
__dict__ for subsequent returns, gets expired when the object is expired, after
a commit(), etc.
2. if you assign to the attribute, "cascade" can establish that the child
object or objects are brought into the session
3. merge() will merge it in
4. potentially the unit of work could fire off rules to establish necessary
state on the parent or child based on the object association as well as its
history
There is a rough outline of a new extension here, where the end user could plug
in functions to establish "how am I loading the object", "I'm flushing, what
needs to be populated", "here's my SQL comparison logic", but this would be a
big job. It would be daunting for new users, it would have a complex usage
contract. A rudimentary version could probably happen quickly, but when people
start using it for real, the number of edge cases is huge. Could be a nice
feature, but would be an enormous maintenance job for many months, or
alternatively a little used and weakly supported feature. Attached is a
sketch of part of it, does a little bit of the above. You're free to use it
but it uses more of the internals than I think you'd want to (these are
internals that aren't guaranteed to stay the same).
But beyond all the work it would require for testing and maintenance, how
useful would it actually be, and how confusing would it be that you can kind of
build an attribute like this on your own outside of SQLAlchemy, as you've done
?
If all you need is so that hitting salesperson doesn't do the load every time,
@memoized_property is easy for that. Another example for that is attached, it
also expires itself when the object is expired. Doesn't do as much. It's not
clear how much of relationship() you'd really need.
Of course if you could find a way to copy this one aspect of your JSON
structure to a plain foreign key attribute (and there's many ways to fire off
an event like that), all of this goes away and you get SQL-level eager loading
and joins and all that.
>
>
>>>
>>> On Thu, Jun 2, 2011 at 4:10 PM, Michael Bayer <[email protected]>
>>> wrote:
>>>> Using a BLOB as a key is a really bad idea and wont work on all backends,
>>>> but other than the database-level limitations inherent, SQLAlchemy will
>>>> let you set up whatever column you'd like to use as the key just fine
>>>> within a relationship(). Guessing what the problem might be. Foreign
>>>> key ? If you rely upon your table metadata to emit CREATE TABLE
>>>> statements, you can forego using ForeignKey with your table metadata and
>>>> configure the foreign key data on the relationship itself using
>>>> "foreign_keys=[table.c.my_referencing_blob_column],
>>>> primary_join=table.c.my_key_blob_column==table.c.my_referencing_blob_column".
>>>>
>>>> As far as your eager load, both joined eager loading and subquery eager
>>>> loading rely on being able to JOIN to the target so if your backend is not
>>>> letting the JOIN part happen, I'm not sure exactly what SQL you'd like to
>>>> emit. If you'd like the "lazy" loader to just fire off immediately, you
>>>> can use the "immediate" style of loader - lazy="immediate" or
>>>> immediateload() as an option - but that won't save you on SELECT
>>>> statements.
>>>>
>>>>
>>>>
>>>>
>>>> On Jun 2, 2011, at 6:15 PM, Ben Chess wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I want to establish a relationship with an object whose key is defined
>>>>> inside a JSON BLOB column in the child. Naively, I know I can do this
>>>>> via a regular python @property that uses object_session() to then do a
>>>>> query() using the id from inside the blob. Is there a better way that
>>>>> lets sqlalchemy manage the relationship and caching?
>>>>>
>>>>> Also, I'd like to be able to sometimes eagerly load this
>>>>> relationship. Obviously I can't do in with a JOIN of the original
>>>>> query, but is there some way to define the relationship, perhaps using
>>>>> a column_property, to be able to undefer()?
>>>>>
>>>>> Thanks,
>>>>> Ben Chess
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "sqlalchemy" group.
>>>>> To post to this group, send email to [email protected].
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected].
>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>>
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected].
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
from sqlalchemy import TypeDecorator, Integer, VARCHAR, Column, create_engine
from sqlalchemy.orm import Session, object_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
import json
Base = declarative_base()
class JSONEncodedDict(TypeDecorator):
impl = VARCHAR
def process_bind_param(self, value, dialect):
if value is not None:
value = json.dumps(value)
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
class memoized_property(object):
"""A read-only @property that is only evaluated once."""
def __init__(self, fget, doc=None):
self.fget = fget
self.__doc__ = doc or fget.__doc__
self.__name__ = fget.__name__
def __get__(self, obj, cls):
if obj is None:
return self
obj.__dict__[self.__name__] = result = self.fget(obj)
return result
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
foo = Column(JSONEncodedDict)
@memoized_property
def child(self):
return object_session(self).query(Child).get(self.foo['child_id'])
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
@event.listens_for(Parent, "expire")
def on_expire(target, attrs):
if attrs is None or "child" in attrs:
target.__dict__.pop('child', None)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
c1 = Child(id=5)
p1 = Parent(foo={"child_id":5})
s.add_all([p1, c1])
s.commit()
p1 = s.query(Parent).first()
assert p1.child is c1
assert 'child' in p1.__dict__
s.expire(p1)
assert 'child' not in p1.__dict__
p1.child = Child(id=6)
s.add(p1.child)
s.commit()
from sqlalchemy import TypeDecorator, Integer, VARCHAR, Column, create_engine
from sqlalchemy.orm import Session, object_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import attributes, interfaces, properties, unitofwork, util as orm_util
import json
Base = declarative_base()
class JSONEncodedDict(TypeDecorator):
impl = VARCHAR
def process_bind_param(self, value, dialect):
if value is not None:
value = json.dumps(value)
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
class AttributeProp(interfaces.MapperProperty):
_creation_order = 0
cascade_backrefs = False
def __init__(self):
self.cascade = orm_util.CascadeOptions("save-update, merge")
def _load_child(self, state, passive):
obj = state.obj()
session = object_session(obj)
return session.query(Child).get(obj.foo['child_id'])
def instrument_class(self, mapper):
attr = attributes.register_attribute(
mapper.class_, "child",
parententity=mapper,
uselist=False, callable_=self._load_child,
useobject=True
)
unitofwork.track_cascade_events(attr, self)
def cascade_iterator(self, *arg, **kw):
return iter([])
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
foo = Column(JSONEncodedDict)
child = AttributeProp()
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
c1 = Child(id=5)
p1 = Parent(foo={"child_id":5})
s.add_all([p1, c1])
s.commit()
p1 = s.query(Parent).first()
assert p1.child is c1
assert 'child' in p1.__dict__
s.expire(p1)
assert 'child' not in p1.__dict__
p1.child = Child(id=6)
s.commit()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
