Re: [sqlalchemy] Selectinload and bindparam
Yeah, new api is great,
eg:
loader_fn = self.lazy_loader_map.get(relation_attribute.prop.lazy,
'selectinload')
self.opts.append(loader_fn(relation_attribute.and_(*relation_filter).options(*related.opts))
or with_loader_criteria and events you mentioned.
Thanks again. Have a nice weekend.
piątek, 10 września 2021 o 15:14:59 UTC+2 Mike Bayer napisał(a):
> great, glad you were able to work that out that's a lot of new API to learn
>
> On Fri, Sep 10, 2021, at 3:39 AM, Tomas Pavlovsky wrote:
>
> Thank you very much,
> It helped me a lot, UserDefinedOption and do_orm_execute, I missed it in
> the documentation
>
> P.S. Thank you for sqlalchemy, it's really helpful and thanks to nested
> options it's really fun to use.
> wtorek, 31 sierpnia 2021 o 16:41:07 UTC+2 Mike Bayer napisał(a):
>
>
>
> this is true, the parameters can't be known to be passed along to a
> secondary query. the approach here would require that you make careful
> use of custom user options and events in order to make it work. the
> general event hook you would use is documented at
> https://docs.sqlalchemy.org/en/14/orm/session_events.html#adding-global-where-on-criteria
>
> .I dont have the resouces to work up a complete example however an
> example of creating custom user options is in the dogpile.cache example at
> https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-caching .
> the user option object you would use is based on :
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/interfaces.py#L792
>
> basically build a UserDefinedOption class that carries along the
> parameters you want. then in do_orm_execute() apply those parameters to
> the query when the option is present. you can consume user defined options
> using the user_defined_options collection:
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=ormexecutestate#sqlalchemy.orm.ORMExecuteState.user_defined_options
>
>
>
>
>
> On Tue, Aug 31, 2021, at 4:46 AM, Tomas Pavlovsky wrote:
>
> Sorry, once again :)
>
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> a = relationship(A)
>
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s =
> select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel)))
> result = session.execute(s, dict(c_global_attr_1=1))
>
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = select(X).options(*selectionload*
> (X.a).options(joinedload(A.b_filtered_rel)))
> or
> s = select(X).options(joinedload(X.a).options(*selectionload*
> (A.b_filtered_rel)))
> result = session.execute(s, dict(c_global_attr_1=1))
> This query will fail, beacuse only params passed to query are related Pks.
> What do you think about ability to pass some arguments to query emitted
> with selectinload?
>
> wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a):
>
> Hello Mike,
>
> Thank you very much for your response.
> I ended up with that, but i wanna ask you about this solution.
>
> class A(Base):
> id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
>
> #descr
> class B(Base):
> id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_relationship = relationship("C", secondary='b_rel_c', lazy='joined',
> uselist=False)
>
> #descr_cat
> class C(Base):
> id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
> c_attr1: int = Column(Integer, nullable=False)
> c_global_attr_1: int = Column(Integer, nullable=False)
>
> class B_rel_C(Base):
> b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
> some_kw: int = Column(Integer)
>
> class CustomSelectClass(Base):
> __table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C,
> B_rel_C.b_id == B.id). \
> join(C, C.id == B_rel_C.c_id).where(
> B.a_id == A.id,
> C.c_global_attr_1 == bindparam('c_global_attr_1')). \
> limit(100).subquery().lateral()
>
> A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
>
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>
> I use this aproach also with another schema like this: X -> A -> A_REL_B
> <- B -> C
>
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s = select(X).options(joinedload(A).options(joinedload(B)))
> result = session.execute(s, dict(c_global_attr_1=1))
>
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = select(X).options(*selectionload*(A).options(joinedload(B)))
> or
> s = select(X).options(joinedload(A).options(*selectionload*(B)))
> result = session.execute(s, dict(c_global_attr_1=1))
Re: [sqlalchemy] Selectinload and bindparam
great, glad you were able to work that out that's a lot of new API to learn
On Fri, Sep 10, 2021, at 3:39 AM, Tomas Pavlovsky wrote:
> Thank you very much,
> It helped me a lot, UserDefinedOption and do_orm_execute, I missed it in the
> documentation
>
> P.S. Thank you for sqlalchemy, it's really helpful and thanks to nested
> options it's really fun to use.
> wtorek, 31 sierpnia 2021 o 16:41:07 UTC+2 Mike Bayer napisał(a):
>> __
>>
>> this is true, the parameters can't be known to be passed along to a
>> secondary query. the approach here would require that you make careful
>> use of custom user options and events in order to make it work. the general
>> event hook you would use is documented at
>> https://docs.sqlalchemy.org/en/14/orm/session_events.html#adding-global-where-on-criteria
>> .I dont have the resouces to work up a complete example however an
>> example of creating custom user options is in the dogpile.cache example at
>> https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-caching .
>> the user option object you would use is based on :
>> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/interfaces.py#L792
>>
>> basically build a UserDefinedOption class that carries along the parameters
>> you want. then in do_orm_execute() apply those parameters to the query when
>> the option is present. you can consume user defined options using the
>> user_defined_options collection:
>> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=ormexecutestate#sqlalchemy.orm.ORMExecuteState.user_defined_options
>>
>>
>>
>>
>>
>> On Tue, Aug 31, 2021, at 4:46 AM, Tomas Pavlovsky wrote:
>>> Sorry, once again :)
>>>
>>> class X(Base):
>>> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
>>> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>>> a = relationship(A)
>>>
>>> s = select(A).where(A.id == 1)
>>> s = s.options(joinedload(A.b_filtered_rel))
>>> or
>>> s =
>>> select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel)))
>>> result = session.execute(s, dict(c_global_attr_1=1))
>>>
>>> s = s.options(*selectionload*(A.b_filtered_rel))
>>> or
>>> s =
>>> select(X).options(*selectionload*(X.a).options(joinedload(A.b_filtered_rel)))
>>>
>>> or
>>> s =
>>> select(X).options(joinedload(X.a).options(*selectionload*(A.b_filtered_rel)))
>>>
>>> result = session.execute(s, dict(c_global_attr_1=1))
>>> This query will fail, beacuse only params passed to query are related Pks.
>>> What do you think about ability to pass some arguments to query emitted
>>> with selectinload?
>>>
>>> wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a):
Hello Mike,
Thank you very much for your response.
I ended up with that, but i wanna ask you about this solution.
class A(Base):
id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
#descr
class B(Base):
id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_relationship = relationship("C", secondary='b_rel_c', lazy='joined',
uselist=False)
#descr_cat
class C(Base):
id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
c_attr1: int = Column(Integer, nullable=False)
c_global_attr_1: int = Column(Integer, nullable=False)
class B_rel_C(Base):
b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
some_kw: int = Column(Integer)
class CustomSelectClass(Base):
__table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C,
B_rel_C.b_id == B.id). \
join(C, C.id == B_rel_C.c_id).where(
B.a_id == A.id,
C.c_global_attr_1 == bindparam('c_global_attr_1')). \
limit(100).subquery().lateral()
A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
class X(Base):
id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
I use this aproach also with another schema like this: X -> A -> A_REL_B
<- B -> C
s = select(A).where(A.id == 1)
s = s.options(joinedload(A.b_filtered_rel))
or
s = select(X).options(joinedload(A).options(joinedload(B)))
result = session.execute(s, dict(c_global_attr_1=1))
s = s.options(*selectionload*(A.b_filtered_rel))
or
s = select(X).options(*selectionload*(A).options(joinedload(B)))
or
s = select(X).options(joinedload(A).options(*selectionload*(B)))
result = session.execute(s, dict(c_global_attr_1=1))
What do you think about ability to pass some arguments to query emitted
with
Re: [sqlalchemy] Selectinload and bindparam
Thank you very much,
It helped me a lot, UserDefinedOption and do_orm_execute, I missed it in
the documentation
P.S. Thank you for sqlalchemy, it's really helpful and thanks to nested
options it's really fun to use.
wtorek, 31 sierpnia 2021 o 16:41:07 UTC+2 Mike Bayer napisał(a):
>
> this is true, the parameters can't be known to be passed along to a
> secondary query. the approach here would require that you make careful
> use of custom user options and events in order to make it work. the
> general event hook you would use is documented at
> https://docs.sqlalchemy.org/en/14/orm/session_events.html#adding-global-where-on-criteria
>
> .I dont have the resouces to work up a complete example however an
> example of creating custom user options is in the dogpile.cache example at
> https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-caching .
> the user option object you would use is based on :
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/interfaces.py#L792
>
> basically build a UserDefinedOption class that carries along the
> parameters you want. then in do_orm_execute() apply those parameters to
> the query when the option is present. you can consume user defined options
> using the user_defined_options collection:
> https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=ormexecutestate#sqlalchemy.orm.ORMExecuteState.user_defined_options
>
>
>
>
>
> On Tue, Aug 31, 2021, at 4:46 AM, Tomas Pavlovsky wrote:
>
> Sorry, once again :)
>
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> a = relationship(A)
>
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s =
> select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel)))
> result = session.execute(s, dict(c_global_attr_1=1))
>
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = select(X).options(*selectionload*
> (X.a).options(joinedload(A.b_filtered_rel)))
> or
> s = select(X).options(joinedload(X.a).options(*selectionload*
> (A.b_filtered_rel)))
> result = session.execute(s, dict(c_global_attr_1=1))
> This query will fail, beacuse only params passed to query are related Pks.
> What do you think about ability to pass some arguments to query emitted
> with selectinload?
>
> wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a):
>
> Hello Mike,
>
> Thank you very much for your response.
> I ended up with that, but i wanna ask you about this solution.
>
> class A(Base):
> id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
>
> #descr
> class B(Base):
> id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_relationship = relationship("C", secondary='b_rel_c', lazy='joined',
> uselist=False)
>
> #descr_cat
> class C(Base):
> id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
> c_attr1: int = Column(Integer, nullable=False)
> c_global_attr_1: int = Column(Integer, nullable=False)
>
> class B_rel_C(Base):
> b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
> some_kw: int = Column(Integer)
>
> class CustomSelectClass(Base):
> __table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C,
> B_rel_C.b_id == B.id). \
> join(C, C.id == B_rel_C.c_id).where(
> B.a_id == A.id,
> C.c_global_attr_1 == bindparam('c_global_attr_1')). \
> limit(100).subquery().lateral()
>
> A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
>
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>
> I use this aproach also with another schema like this: X -> A -> A_REL_B
> <- B -> C
>
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s = select(X).options(joinedload(A).options(joinedload(B)))
> result = session.execute(s, dict(c_global_attr_1=1))
>
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = select(X).options(*selectionload*(A).options(joinedload(B)))
> or
> s = select(X).options(joinedload(A).options(*selectionload*(B)))
> result = session.execute(s, dict(c_global_attr_1=1))
>
> What do you think about ability to pass some arguments to query emitted
> with selectinload
>
> poniedziałek, 30 sierpnia 2021 o 17:50:49 UTC+2 Mike Bayer napisał(a):
>
>
> yes, you would use bindparam("some_name") in conjunction with the "and_()"
> feature, like options(selectinload(MyClass.foo.and_(MyClass.name ==
> bindparam("some_name"
>
>
>
> On Mon, Aug 30, 2021, at 10:55 AM, Tomas Pavlovsky wrote:
>
> Hello,
>
> is it possible in sqlalchemy arguments from db.execute(query, args) to be
>
