On Sep 12, 2013, at 12:02 PM, Philip Scott <[email protected]> wrote:
> Thanks Michael
>
> a column_property() against a SQL expression by definition is not writable.
> Your table doesn't have a CAST trigger inside of it for when an "int" is
> written to it that would convert it back to a string.
> in this case since "data" is already loaded fully as a single column you
> might as well just use a hybrid.
>
> I see what you mean about column_property not being writable. In my actual
> application I won't be loading the whole 'data'; what I am really trying to
> do is make a sort of psuedo-column so that the SQL that gets generated is
> something like
>
> SELECT id, data->foo AS foo FROM thing
>
> And 'foo' gets mapped as if it were a normal column so it's update-able too.
> I can deal with the casting on top of that I think.
>
> Does that make any sense? Perhaps I am trying to be too much of an Alchemist
> for my own good here :)
I can see the appeal of column_property() actually being writable, such that
you'd provide some SQL expression that allows the operation to go back the
other way when you assign. Postgresql's special types like ARRAY and HSTORE
actually introduce the concept of this actually being useful, e.g. assignment
to an expression within INSERT or UPDATE which I don't think applies at all to
any other database (I could be wrong though).
So currently column_property() doesn't do that. But you can handle the
mutations with a simple event, though a mutation does require loading "data".
To really get the "data->foo = '5'" in the INSERT statement, the Core does
support that but the ORM doesn't, the best you could do there at the moment
would be a second INSERT statement.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
# deferred() since it seems like you don't normally
# want to load this field
data = deferred(Column(MutableDict.as_mutable(HSTORE)))
# data.expression is to unwrap "deferred()"
foo = column_property(cast(data.expression['foo'], Integer))
@validates("foo")
def _set_foo(self, key, value):
if not self.data:
self.data = {}
self.data["foo"] = str(value)
# returning the integer version for local access, isn't flushed
return value
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A(foo=5)
sess.add(a1)
# pre flush
assert a1.foo == 5
sess.commit()
assert a1.foo == 5
a1.foo = 6
sess.commit()
a1 = sess.query(A).first()
# .foo is in terms of the column property
assert a1.foo == 6
# data is still deferred (usually)
assert 'data' not in a1.__dict__
assert a1.data == {"foo": "6"}
>
> All the best,
>
> Philip
>
>
> --
> 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 post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
