How to custom 'merge' behavior of a column type?

[sonsshh]

Hi,

I'm trying to change the behavior of Postgres ARRAY type when calling

    session.merge(obj)

where obj has a links ARRAY(Text) column. 

What I want is as below:

    obj = Obj(links=['link2'],...)

    in DB I have obj row already and obj.links=['link1']

    session.merge(obj)

    session.commit() updates obj.links=['link1', 'link2'] instead of overwriting obj.links=['link2']

    

I want this sql to be emitted when updating this field.

   CASE WHEN obj.links IS NULL 

       OR NOT ARRAY[obj.links] @> %(links)s
   THEN array_cat(obj.links, %(links)s)
   ELSE obj.links END

I've been researching but still got no clue where to start.

Is there anyone who can help?

Thanks a lot,

[Mike Bayer]

you would need to accomplish this manually.

existing_obj = session.query(MyClass).get(obj.id)

if existing_obj is not None:

    existing_obj.links.extend(obj.links)

obj = session.merge(obj)

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/860da96c-7295-40bf-a436-027e27c1d6d6n%40googlegroups.com.

[sonsshh]

Hi Mike,

My use case involves receiving a lot of data over the network, processing on the model and persisting them.

Therefore, using the manual approach would be quite inefficient.

Currently I have to use raw sql and session.execute(), is this the only way?

Is there any other way for example to custom a ColumnType to change the UPDATE sql emitted?

[Mike Bayer]

On Tue, Sep 15, 2020, at 12:02 AM, sonsshh wrote:

Hi Mike,

My use case involves receiving a lot of data over the network, processing on the model and persisting them.

Therefore, using the manual approach would be quite inefficient.

session.merge() is not any more efficient than what I illustrated.   it runs on one object at a time and is not well optimzied for large lists of objects.   it compares all the attribute values in Python.

Currently I have to use raw sql and session.execute(), is this the only way?

The example I showed uses query.get(), or you can get a bunch of objects at once using session.query().  there's no need to use raw sql.

if OTOH you are trying to do this entirely on the SQL side then you can use a Core update() construct that assigns the array column using the Postgresql array concatenation operator.   The example below illustrates how to do this both for all rows at once, and on a per-row basis, with no SELECT in use.

from sqlalchemy import ARRAY

from sqlalchemy import bindparam

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import Integer

from sqlalchemy.dialects.postgresql import ARRAY

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import Session

Base = declarative_base()

class A(Base):

    __tablename__ = "a"

    id = Column(Integer, primary_key=True)

    data = Column(ARRAY(Integer))

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_all(

    [A(data=[1, 2, 3]), A(data=[4, 5, 6]), A(data=[7, 8, 9]),]

)

s.commit()

# example one - update all rows to the same value

s.query(A).update({"data": A.data + [10, 11, 12]}, synchronize_session=False)

assert list(s.query(A.data).order_by(A.id)) == [

    ([1, 2, 3, 10, 11, 12],),

    ([4, 5, 6, 10, 11, 12],),

    ([7, 8, 9, 10, 11, 12],),

]

# example two - update rows to individual values using executemany

s.execute(

    A.__table__.update()

    .where(A.id == bindparam("_id"))

    .values(data=A.data + bindparam("values")),

    [

        {"_id": 1, "values": [13, 14, 15]},

        {"_id": 2, "values": [16, 17, 18]},

        {"_id": 3, "values": [19, 20, 21]},

    ],

)

assert list(s.query(A.data).order_by(A.id)) == [

    ([1, 2, 3, 10, 11, 12, 13, 14, 15],),

    ([4, 5, 6, 10, 11, 12, 16, 17, 18],),

    ([7, 8, 9, 10, 11, 12, 19, 20, 21],),

]

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1f81a3cf-3179-4ac9-bc39-93087e088c3en%40googlegroups.com.