On 4/4/15 7:47 PM, Daniel Kerkow wrote:
>
> 2015-04-05 1:29 GMT+02:00 Mike Bayer <[email protected]
> <mailto:[email protected]>>:
>
>
>
> On 4/4/15 7:22 PM, Daniel Kerkow wrote:
>> Hi,
>> I am new to SQLAlchemy, doing my first steps with Flask.
>> I have the following model using JSONB data type in PostgreSQL.
>>
>> The JSON data looks like
>>
>> |
>> {'key1':'value1','key2':'value2'}
>> |
>>
>> The Docs are relatively sparse regarding this topic.
>
> the cases you ask for are all described at :
>
>
> http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON
>
>> How can I query the properties column for containing a specific
>> key or key=value combination?
>
> select([table]).where(table.c.col['key'] == 'somevalue')
>
>
> Seems I am doing something wrong here:
>
> select(Record).where(Record.c.properties['key'] == 'value')
>
> TypeError: '_BoundDeclarativeMeta' object is not iterable
>
> Did I miss to import something? Any preparations?
First off, if you are using the select() construct, it accepts a list of
things to SELECT from, so select([Record]).
Secondly, "Record" looks a whole lot like a declarative ORM class due to
the "BoundDeclarativeMeta" line; there's a difference between using
table metadata and an ORM class, in this case that the ORM class doesn't
use the .c. attribute. If you are using ORM classes you'd likely want
to use session.query() which is generally more appropriate.
>
>
>
>> How can I update these?
>
> update([table]).values({table.c.col['key'] = 'value'})
>
>> Can I get a list of unique keys in all records?
>
> i dunno. Whats the Postgresql query you'd like to emit?
>
>
> In raw psql, the following works:
> select *
> from records
> where properties->>'color' = 'black';
>
>
>
>
>
>>
>> Any help is welcome!
>>
>> Daniel
>>
>> |
>> classRecord(Base):
>>
>> """represents single stratigraphic units"""
>>
>> # ID of corresponding site:
>> site_id
>> =db.Column(db.Integer,db.ForeignKey('public.sites.id
>> <http://public.sites.id>'))
>>
>> # depth values:
>> depth =db.Column(db.Numeric,nullable=True)
>> upper_boundary =db.Column(db.Numeric,nullable=True)
>> lower_boundary =db.Column(db.Numeric,nullable=True)
>>
>> # stratigraphic properties, represented as key/value store
>> properties =db.Column(JSONB)
>> |
>>
>> --
>> 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]
>> <mailto:[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to a topic in
> the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/vk6L6152vE8/unsubscribe.
> To unsubscribe from this group and all its topics, send an email
> to [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
