I might be interpreting all this wrong, but I don't think the
column_property needs to be writable.
I think the situation is this:
Under Postgres, with HSTORE it's possible to INSERT/UPDATE/DELETE only
certain values from within the store.
Under SqlAlchemy, the entire object is retreived/replaced
I think I know what you're talking about.
Under Postgres, with HSTORE it's possible to INSERT/UPDATE/DELETE only
certain values from within the store...
Given:
class TestClass(Base):
__tablename__ = 'test_class'
id = Column(Integer, primary_key=True)
kv =
Column(sa_mutable.MutableDict.as_mutable(sqlalchemy.dialects.postgresql.HSTORE)
, nullable=True )
which creates...
## BEGIN
## CREATE TABLE test_class (
## id SERIAL NOT NULL,
## kv HSTORE,
## PRIMARY KEY (id)
##)
if we create a few hstore entires..
import string
for x in range(0,5):
sampledict = dict([(i,i) for i in string.ascii_lowercase])
expected_pass = TestClass( kv=sampledict )
dbSession.add(expected_pass)
dbSession.flush()
generated_id = expected_pass.id
dbSession.commit()
## BEGIN (implicit)
## INSERT INTO test_class (kv) VALUES (%(kv)s) RETURNING test_class.id
## {'kv': {'a': 'a', 'c': 'c', 'b': 'b', 'e': 'e', 'd': 'd', 'g': 'g', 'f':
'f', 'i': 'i', 'h': 'h', 'k': 'k', 'j': 'j', 'm': 'm', 'l': 'l', 'o': 'o',
'n': 'n', 'q': 'q', 'p': 'p', 's': 's', 'r': 'r', 'u': 'u', 't': 't', 'w':
'w', 'v': 'v', 'y': 'y', 'x': 'x', 'z': 'z'}}
## COMMIT
We can operate on k/v pairs within Postgres; but we replace the entire
column in SqlAlchemy
SELECT id, kv->'y' , kv->'z' AS foo FROM test_class ;
UPDATE test_class SET kv = delete(kv, 'z');
SELECT id, kv->'y' kv->'z' AS foo FROM test_class ;
UPDATE test_class SET kv = kv || ( 'z => z' );
SELECT id, kv->'y' , kv->'z' AS foo FROM test_class ;
IIRC , sqlalchemy already tracks the changed keys via MutableDict ; the
necessary work would be to generate the sql for specific UPDATE/DELETE on
only the mutated keys. Not sure about the select.
sidenote-- i found the postgres docs to have a bunch of errors on syntax.
the above works on 9.2, but is not what the 9.x docs specify.
--
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.
