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],),
]
> Is there any other way for example to custom a ColumnType to change the
> UPDATE sql emitted?
>
> On Monday, September 14, 2020 at 12:18:49 AM UTC+7 Mike Bayer wrote:
>> __
>> 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)
>>
>>
>>
>> On Sat, Sep 12, 2020, at 3:45 AM, sonsshh wrote:
>>> 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,
>>>
>>>
>>> --
>>> 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 view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/860da96c-7295-40bf-a436-027e27c1d6d6n%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/860da96c-7295-40bf-a436-027e27c1d6d6n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/1f81a3cf-3179-4ac9-bc39-93087e088c3en%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/1f81a3cf-3179-4ac9-bc39-93087e088c3en%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/59fe9560-ed9d-4e75-ae45-a62ea2e7e46a%40www.fastmail.com.
