On Tue, Feb 5, 2019 at 11:15 AM <[email protected]> wrote:
>
> Using:
> SQLAlchemy 1.2.1
> PostgreSQL 10.6
>
> I have a table with a pk and jsonb data and would like to do an upsert that
> merges the json data
>
> Example SQL:
>
> INSERT INTO json_data (id, data)
> VALUES (%(id)s, %(data)s)
> ON CONFLICT ON CONSTRAINT json_data_pk
> DO
> UPDATE SET data = json_data.data::jsonb || %(data)s;
>
> Example python:
>
> from sqlalchemy import Column, String, Table
> from sqlalchemy.dialects.postgresql import insert, JSONB
>
> table = Table(
> 'json_data',
> SqlDatabase.get_metadata(),
> Column('id', String, primary_key=True),
> Column('data', JSONB, nullable=False),
> )
> insert_stmt = insert(table).values(
> id="test",
> data={'key','value'}
> )
> update_stmt = insert_stmt.on_conflict_do_update(
> constraint=table.primary_key,
> set_=dict(data=???)
> )
>
> How do I reference the existing jsonb data and merge it with the new data in
> the on_conflict_do_update call?
note the cast to JSONB is probably not needed because that column is
already defined as JSONB. To reference a column, you just use it:
table.c.data.concat("my data")
see below
from sqlalchemy import Column, String, Table, MetaData
from sqlalchemy.dialects.postgresql import insert, JSONB
from sqlalchemy.dialects import postgresql
m = MetaData()
table = Table(
"json_data",
m,
Column("id", String, primary_key=True),
Column("data", JSONB, nullable=False),
)
insert_stmt = insert(table).values(id="test", data={"key", "value"})
update_stmt = insert_stmt.on_conflict_do_update(
constraint=table.primary_key,
set_=dict(data=table.c.data.concat("my data")),
)
# requested:
"""INSERT INTO json_data (id, data)
VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT json_data_pk
DO
UPDATE SET data = json_data.data::jsonb || %(data)s;"""
# prints:
"""
INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO UPDATE SET data = (json_data.data || %(data_1)s)
"""
print(update_stmt.compile(dialect=postgresql.dialect()))
# prints:
"""
INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT json_data_pk DO UPDATE SET data =
(json_data.data || %(data_1)s)
"""
update_stmt = insert_stmt.on_conflict_do_update(
constraint="json_data_pk", set_=dict(data=table.c.data.concat("my data"))
)
print(update_stmt.compile(dialect=postgresql.dialect()))
>
> Thanks,
> Steve
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
