So I thought HSTORE would work like ARRAY, in that “foo -> ‘bar’ = ‘bat’” would work just like in a SELECT, but it doesn’t.
So I took a look at the PG docs http://www.postgresql.org/docs/9.1/static/hstore.html, OK use concatenation, we support that as it says right here at http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=hstore#sqlalchemy.dialects.postgresql.HSTORE : from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.postgresql import HSTORE Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(HSTORE) e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) s.add(A(data={"foo": "bar", "a": "b"})) s.commit() s.query(A).update({A.data: A.data + {"foo": "bat", "hoho": "lala"}}, synchronize_session="fetch") assert s.query(A.data).scalar() == {"foo": "bat", "hoho": "lala", "a": "b"} the UPDATE looks like: UPDATE a SET data=(a.data || %(data_1)s) {'data_1': {'foo': 'bat', 'hoho': 'lala’}} works fine On Nov 18, 2013, at 1:24 PM, Jonathan Vanasco <jonat...@findmeon.com> wrote: > forgot to add -- this creates the following SQL: > > UPDATE user_data SET prefs=(user_data.prefs || hstore(ARRAY[%(param_1)s, > %(param_2)s])) WHERE user_data.prefs ? %(prefs_1)s > {'prefs_1': 'inboundBusStop', 'param_1': 'transitAvaiable', 'param_2': 'true'} > > Note that it's using one of the htsore array formats , and a bitwise > operator. This allows you to just to the minimal SQL needed (instead of > having to slurp the whole hstore to edit a field, then toss the entire thing > back again ) > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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