Re: [sqlalchemy] efficient strategies for partial loads of large relationships ?
like, OMFG this is brilliant. -- 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.
Re: [sqlalchemy] efficient strategies for partial loads of large relationships ?
Mike, This is absolutely brilliant! thank you! -- 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.
Re: [sqlalchemy] efficient strategies for partial loads of large relationships ?
I'm going to add this example to the docs. Let me know if I'm
missing something such that it doesn't actually work.
On Thu, Sep 27, 2018 at 9:11 PM Mike Bayer wrote:
>
> On Thu, Sep 27, 2018 at 5:38 PM Jonathan Vanasco wrote:
> >
> > I have a handful of relationships where there could be 100s of matching
> > rows, but in most views I only want to see a subset of them. perhaps the
> > most recent 5 items of 500 candidates..
> >
> > to handle this so far, i've been constructing a join with correlated
> > subquery. it works, but this doesn't seem efficient to write or execute.
> >
> > does anyone have an idea for a better approach? It feels wonky to
> > reimplement similar queries so much in my model definition.
> >
> > i don't want to use `dynamic` loading, because that relationships strategy
> > returns a query object and exposes a slightly different usage pattern..
> > for various compatibility reasons, I need the relationship attribute to
> > represent a fully loaded collection. it can be view-only collection - but
> > must be limited to a certain number of elements.
>
> good news! I almost never do anything smart anymore as I am old and
> tired, but I thought of something cool here! use a window function!
> works great and should work with any kind of loader too! has to do
> the non-primary mapper thing, since you can't introduce the window
> function in the WHERE or ON clause, but that's no big deal, seems to
> work!!
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
>
> id = Column(Integer, primary_key=True)
> data = Column(String)
> bs = relationship("B")
>
>
> class B(Base):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> a_id = Column(ForeignKey("a.id"))
> data = Column(String)
>
> def __repr__(self):
> return "B(id=%r, a_id=%r)" % (self.id, self.a_id)
>
> partition = select([
> B,
> func.row_number().over(order_by=B.id, partition_by=B.a_id).label('index')
> ]).alias()
> partitioned_b = mapper(B, partition, non_primary=True)
>
> A.partitioned_bs = relationship(
> partitioned_b,
> primaryjoin=and_(partitioned_b.c.a_id == A.id, partitioned_b.c.index < 10)
> )
>
> 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(
> id=i,
> bs=[B(id=j + (i * 100)) for j in range(1, 100)]
> ) for i in range(1, 10)
> ])
> s.commit()
>
> for a1 in s.query(A).options(selectinload(A.partitioned_bs)):
> print(a1.partitioned_bs)
>
>
>
> >
> >
> > --
> > 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.
--
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.
Re: [sqlalchemy] efficient strategies for partial loads of large relationships ?
On Thu, Sep 27, 2018 at 5:38 PM Jonathan Vanasco wrote:
>
> I have a handful of relationships where there could be 100s of matching rows,
> but in most views I only want to see a subset of them. perhaps the most
> recent 5 items of 500 candidates..
>
> to handle this so far, i've been constructing a join with correlated
> subquery. it works, but this doesn't seem efficient to write or execute.
>
> does anyone have an idea for a better approach? It feels wonky to
> reimplement similar queries so much in my model definition.
>
> i don't want to use `dynamic` loading, because that relationships strategy
> returns a query object and exposes a slightly different usage pattern.. for
> various compatibility reasons, I need the relationship attribute to represent
> a fully loaded collection. it can be view-only collection - but must be
> limited to a certain number of elements.
good news! I almost never do anything smart anymore as I am old and
tired, but I thought of something cool here! use a window function!
works great and should work with any kind of loader too! has to do
the non-primary mapper thing, since you can't introduce the window
function in the WHERE or ON clause, but that's no big deal, seems to
work!!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
def __repr__(self):
return "B(id=%r, a_id=%r)" % (self.id, self.a_id)
partition = select([
B,
func.row_number().over(order_by=B.id, partition_by=B.a_id).label('index')
]).alias()
partitioned_b = mapper(B, partition, non_primary=True)
A.partitioned_bs = relationship(
partitioned_b,
primaryjoin=and_(partitioned_b.c.a_id == A.id, partitioned_b.c.index < 10)
)
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(
id=i,
bs=[B(id=j + (i * 100)) for j in range(1, 100)]
) for i in range(1, 10)
])
s.commit()
for a1 in s.query(A).options(selectinload(A.partitioned_bs)):
print(a1.partitioned_bs)
>
>
> --
> 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.
--
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.
[sqlalchemy] efficient strategies for partial loads of large relationships ?
I have a handful of relationships where there could be 100s of matching rows, but in most views I only want to see a subset of them. perhaps the most recent 5 items of 500 candidates.. to handle this so far, i've been constructing a join with correlated subquery. it works, but this doesn't seem efficient to write or execute. does anyone have an idea for a better approach? It feels wonky to reimplement similar queries so much in my model definition. i don't want to use `dynamic` loading, because that relationships strategy returns a query object and exposes a slightly different usage pattern.. for various compatibility reasons, I need the relationship attribute to represent a fully loaded collection. it can be view-only collection - but must be limited to a certain number of elements. -- 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.
