I'm trying to run your example, but it doesn't work:
from sqlalchemy import func
m = request.dbsession.query(models.Map).get(3)
m.screenshots = func.jsonb_set(m.screenshots, '{key}', '"value"')
request.dbsession.flush()
It ends up in a (psycopg2.ProgrammingError) can't adapt type 'dict'.
Also, from the generated SQL it seems to me that it's also doing the
full JSONB update from client side, not just inserting a key into the
database server side.
UPDATE maps SET screenshots=jsonb_set(%(jsonb_set_1)s,
%(jsonb_set_2)s, %(jsonb_set_3)s) WHERE maps.id = %(maps_id)s
{'maps_id': 3, 'jsonb_set_3': '"value"', 'jsonb_set_2': '{key}',
'jsonb_set_1': {u'small': u'2ad139ee69cdcd9e.jpg', u'full':
u'68b3f51491ff1501.jpg'}}
On 15 May 2017 at 16:18, Zsolt Ero <zsolt....@gmail.com> wrote:
> Thanks for the answer. My use case is the following:
>
> I have an object (map_obj), which has screenshots in two sizes. I'm
> using JSONB columns to store the screenshot filenames.
>
> Now, the two screenshot sizes are generated in parallel. The code is
> like the following:
>
> map_obj = query(...by id...)
> filename = generate_screenshot(size) # long running screenshot generation
>
> try:
> dbsession.refresh(map_obj, ['screenshots'])
> map_obj.screenshots = dict(map_obj.screenshots, **{size: filename})
> except Exception as e:
> logger.warning(...)
>
> It worked well for 99.9% of the cases. The problem is that in the rare
> case when both screenshots got rendered within a few milliseconds, one
> of the screenshots got lost.
>
> The simple solution was to add lockmode='update' to the refresh, so
> this way the refreshes are blocking until the other finishes the
> update.
>
> But since this means locking a full row, I was thinking a simple JSONB
> insertion would probably be better, since I can avoid locking the row.
>
> Zsolt
>
>
>
>
> On 15 May 2017 at 15:58, mike bayer <mike...@zzzcomputing.com> wrote:
>>
>>
>> On 05/15/2017 09:32 AM, Zsolt Ero wrote:
>>>
>>> In PostgreSQL 9.5+ it is finally possible to modify a single key inside a
>>> JSONB column. Usage is something like this:
>>>
>>> update maps set screenshots=jsonb_set(screenshots, '{key}', '"value"')
>>> where id = 10688
>>>
>>> Is it possible to write this query using the ORM somehow? If not, please
>>> take it as a feature request.
>>
>>
>>
>> You can use that function directly:
>>
>> my_object = session.query(Maps).get(5)
>>
>> my_object.screenshots = func.jsonb_set(my_object.screenshots, '{key}',
>> '"value"')
>>
>> session.flush()
>>
>>
>> as far as "transparent" ORM use of that, like this:
>>
>> my_object.screenshots[key] = "value"
>>
>> right now that is a mutation of the value, and assuming you were using
>> MutableDict to detect this as an ORM change event, the ORM considers
>> "screenshots" to be a single value that would be the target of an UPDATE,
>> meaning the whole JSON dictionary is passed into the UPDATE. There is no
>> infrastructure for the ORM to automatically turn certain column updates into
>> finely-detailed SQL function calls. I can imagine that there might be some
>> event-based way to make this happen transparently within the flush, however,
>> but I'd need to spend some time poking around to work out how that might
>> work.
>>
>>
>> I'm not familiar with what the advantage to jsonb_set() would be and I can
>> only guess it's some kind of performance advantage. I'd be curious to see
>> under what scenarios being able to set one element of the JSON vs. UPDATEing
>> the whole thing is a performance advantage significant compared to the usual
>> overhead of the ORM flush process; that is, Postgresql is really fast, and
>> for this optimization to be significant, you probably need to be calling the
>> Core function directly anyway rather than going through the whole ORM flush
>> process. But this is all based on my assumption as to what your goal of
>> using this function is.
>>
>>
>>
>>>
>>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> 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
>>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>>> To post to this group, send email to sqlalchemy@googlegroups.com
>>> <mailto:sqlalchemy@googlegroups.com>.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
>> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> --- 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/hjjIyEC8KHQ/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
