How to insert row with SQLAlchemy ORM using a subquery with string_agg?

Question

I have an SQL query that inserts a row into a table. One of the columns is assigned a value returned from sub-query that aggregates multiple values using string_agg. How can this be written using SQLAlchemy ORM?

The postgres query

INSERT INTO blobs (_id, data) VALUES (nextval('blobs_id_seq'),
  (SELECT string_agg(blobs.data, NULL ORDER BY blobs._id DESC) 
    FROM blobs where blobs._id IN (1,2) ) );

The table ORM

    class Blob(Base):
      __tablename__ = 'blobs'
      _id = Column(Integer, Sequence('blobs_id_seq'), primary_key=True, unique=True)
     data = Column(LargeBinary)

    Base.metadata.create_all(engine)

    db = Session(engine)
    db.add(Blob(data=b'aa'))
    db.add(Blob(data=b'bb'))
    db.add(Blob(data=b'cc'))
    db.commit()

Invalid ORM insert

    # Should yield a "Blob" with data = b"bbaa"
    db.add(Blob(data=db.query(func.string_agg(Blob.data, None))
      .filter( Blob._id.in_([1, 2]) )
      .order_by( Blob._id.desc() ) ))

Aage Torleif · Accepted Answer · 2020-10-03 03:34:20Z

0

It turns out there is a dialect specific helper aggregate_order_by that renders a query that outputs the expected results.

db.add(Blob(data=db.query(func.string_agg(Blob.data,
    aggregate_order_by(None, Blob._id.desc())))
  .filter( Blob._id.in_([1, 2]) )))

answered Oct 3, 2020 at 3:34

Aage Torleif

2,0251 gold badge23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert row with SQLAlchemy ORM using a subquery with string_agg?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related