Re: [sqlalchemy] SQLAlchemy 1.3.23 turn off before_compile with **kwargs?

[Mike Bayer]

the before_compile event receives the Query object at the point it starts to 
turn it into a SQL construct.  When this happens, it's in response to having 
said something like "query.all()".   So there's no **kwarg that's specific to 
an operation like that.

instead, when you want to send "messages" to event handlers like 
before_compile(), you have to do it by altering the state of the Query itself.  
  Your suggestion to say "query.filter_by(archived=True)" is actually such a 
state change, which would be altering the "_criterion" attribute to include the 
"archived=True" criteria, but to consume this attribute to search for this 
specific criteria would be tedious and possibly error prone.

instead, you can stick with a simpler way to change the state of the Query in a 
way that you can detect inside your event handler easily.   Probably the 
simplest is to use the query.execution_options() method, where you can add 
arbitrary keys and values that can be read later on:

query = query.execution_options(include_archived=True)

in your handler you can check it just like thisL

def filter_archived(query):
if not query.get_execution_options().get("include_archived", False):
# modify the query


in SQLAlchemy 1.4 which is to be released any day now, there's a much more 
comprehensive event  / API for this kind of alteration, see the example at 
https://docs.sqlalchemy.org/en/14/_modules/examples/extending_query/filter_public.html
 which is the latest version of this pattern, which includes the use of 
execution_options to affect the state of the event handler.



On Mon, Mar 1, 2021, at 6:18 PM, Chris Simpson wrote:
> Hello
> 
> I'd like to understand how to turn off a before_compile listener (e.g. soft 
> delete, to include deleted items).
> 
> For example, 
> 
> I've adapted the example from the docs: 
> https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile
>  
> 
> To use the field 'archived' , which works as expected.
> 
> @event.listens_for(Query, "before_compile", retval=True, bake_ok=True)
> def filter_archived(query):
> for desc in query.column_descriptions:
> if desc["type"] is Person:
> entity = desc["entity"]
> query = query.filter(entity.archived == 0)
> return query
> 
> 
> I've tried things such as:
> 
> Person.query.filter_by(archived=True).all()
> 
> But I don't understand yet where I should put such kwargs to override the 
> before_compile events listener
> 
> Is the following the right path?
> 
> @event.listens_for(Query, "before_compile", retval=True, bake_ok=True)
> def filter_archived(query, **kwargs):
> for desc in query.column_descriptions:
> if kwargs["include_archived"] is not True and desc["type"] is Person:
> entity = desc["entity"]
> query = query.filter(entity.archived == 0)
> return query
> 
> 
> Kind regards
> Chris
> 

> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d9ca992a-9075-4c1b-b4a0-32179f48b5bdn%40googlegroups.com
>  
> .

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fb0d02a5-9c0e-4d06-bb35-cc3d448cc3f5%40www.fastmail.com.

[sqlalchemy] SQLAlchemy 1.3.23 turn off before_compile with **kwargs?

[Chris Simpson]

Hello

I'd like to understand how to turn off a before_compile listener (e.g. soft 
delete, to include deleted items).

For example, 

I've adapted the example from the docs: 
https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile
  


To use the field 'archived' , which works as expected.

@event.listens_for(Query, "before_compile", retval=True, bake_ok=True)
def filter_archived(query):
for desc in query.column_descriptions:
if desc["type"] is Person:
entity = desc["entity"]
query = query.filter(entity.archived == 0)
return query


I've tried things such as:

Person.query.filter_by(archived=True).all()

But I don't understand yet where I should put such kwargs to override the 
before_compile events listener

Is the following the right path?

@event.listens_for(Query, "before_compile", retval=True, bake_ok=True)
def filter_archived(query, **kwargs):
for desc in query.column_descriptions:
if kwargs["include_archived"] is not True and desc["type"] is 
Person:
entity = desc["entity"]
query = query.filter(entity.archived == 0)
return query


Kind regards
Chris

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d9ca992a-9075-4c1b-b4a0-32179f48b5bdn%40googlegroups.com.

Re: [sqlalchemy] relationship query_class in SQLAlchemy 1.4.0b3

['Jonathan Vanasco' via sqlalchemy]

"is it better to think of rebuilding medium+ projects for 2.0 while 
maintaining existing codebases for 1.3? In other words, how much will 2.0 
be backward compatible with 1.3?"

I am saying the following as a general user, and not a past contributor to 
this project:

As per the Release Status system 
(https://www.sqlalchemy.org/download.html#relstatus) when 1.4 becomes the 
official "Current Release", 1.3 will drop to "Maintenance" status.  I 
believe we can expect that, when 2.0 becomes the "Current Release", 1.4 
will drop to "Maintenance" and 1.3 will drop to "EOL".

IMHO, while I might prioritize some migration work based on the size of a 
project, if any given project is expected to be undergoing active 
development or be deployed in 2022 and beyond, they should start planning 
for the "2.0" style migration in their sprints. I can't stress this enough, 
my metric would be active-use and active-development, not the size of the 
codebase.

Personally, I would prioritize adapting projects to deploy on 1.4 as the 
ASAP first step -- there are a few small backwards incompatibilities 
between 1.4 and 1.3.  I still run everything on 1.3, but we test and 
develop against 1.4 -- using comments. docstrings to note what changes will 
be required in 1.4 -- or "switch" blocks so CI can run against both 
versions.  

I strongly recommend doing all new work in the 2.0 style, and start 
scheduling the 2.0 migration into sprints. Building anything against 1.3 
right now is really doing nothing but assuming technical debt, and it's 
going to be much easier (and much less work!) planning for this change 
now.  I would not want to be in a situation where one or more projects 
require an EOL version, and there are critical features/bugfixes in the 
newer branch.

You're likely to get a good chunk of time out of 1.4, but I would not 
target 1.3 at this point.


On Monday, March 1, 2021 at 9:45:55 AM UTC-5 aa@gmail.com wrote:

> yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of 
>> Flask-SQLAlchemy.The Query and "dynamic" loaders are staying around 
>> largely so that Flask can come on board, however the patterns in F-S are 
>> pretty much the ones I want to get away from. 
>
>
> 2.0's spirit is one where the act of creating a SELECT statement is a 
>> standalone thing that is separate from being attached to any specific class 
>> (really all of SQLAlchemy was like this, but F-S has everyone doing the 
>> Model.query thing that I've always found to be more misleading than 
>> helpful), but SELECT statements are now also disconnected from any kind of 
>> "engine" or "Session" when constructed.
>
>  
>
> as for with_parent(), with_parent is what the dynamic loader actually uses 
>> to create the query.  so this is a matter of code organization.
>> F-S would have you say:
>>
>  
>
> user = User.query.filter_by(name='name').first()
>> address = user.addresses.filter_by(email='email').first()
>>
>  
>
> noting above, there's no "Session" anywhere.  where is it?   Here's a 
>> Hacker News comment lamenting the real world implications of this: 
>> https://news.ycombinator.com/item?id=26183936  
>>
>  
>
> SQLAlchemy 2.0 would have you say instead:
>>
>  
>
> with Session(engine) as session:
>> user = session.execute(
>>   select(User).filter_by(name='name')
>> ).scalars().first()
>>
>>address = session.execute(
>>select(Address).where(with_parent(user, 
>> Address.user)).filter_by(email='email')
>>).scalars().first()
>>
>  
>
> Noting above, a web framework integration may still wish to provide the 
>> "session" to data-oriented methods and manage its scope, but IMO it should 
>> be an explicit object passed around.  The database connection / transaction 
>> shouldn't be made to appear to be inside the ORM model object, since that's 
>> not what's actually going on.
>
>
> The newer design indeed provides a clearer view of the session.
>
> If you look at any commentary anywhere about SQLAlchemy, the top 
>> complaints are:
>
>
>> 1. too magical, too implicit
>
>
>> 2. what's wrong with just writing SQL?
>
>
>> SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user 
>> *is* writing SQL, they're running it into an execute() method, and they are 
>> managing the scope of connectivity and transactions in an obvious way.   
>> People don't necessarily want bloat and verbosity but they do want to see 
>> explicitness when the computer is being told to do something, especially 
>> running a SQL query.  We're trying to hit that balance as closely as 
>> possible.
>
>
>> The above style also has in mind compatibility with asyncio, which we now 
>> support.  With asyncio, it's very important that the boundary where IO 
>> occurs is very obvious.  Hence the Session.execute() method now becomes the 
>> place where users have to "yield".  With the older Query interface, the 
>> "yields" would be all over the place and kind of arbirary, since some Query 
>> 

[sqlalchemy] Demo: Authorization Using Python & SQLAlchemy

[Stephie Glaser]

Authorization is an unstructured problem. Writing code to decide who can do 
what in your app can cover a broad set of cases. The most structure that 
typically gets applied to this problem area is a set of if statements and 
roles, but in reality, there are a lot more patterns and structure that we 
can apply. 

Oso is an open source system for building authorization into applications. 
It's a bit like SQLAlchemy in that it provides a structured approached to 
authorization, much like SQLAlchemy does for data modeling and access. Oso 
cofounder/CTO, Sam Scott,  gave a talk providing a mental model for 
authorization and showing how to apply it using Oso, Python and SQLAlchemy 
- watch the recording here. 


-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fefaa8ee-80d0-498f-b2a7-a2fb78264938n%40googlegroups.com.

Re: [sqlalchemy] relationship query_class in SQLAlchemy 1.4.0b3

[Mike Bayer]

On Mon, Mar 1, 2021, at 9:45 AM, Ahmed wrote:
>> 
> I'm not authorized to talk on behalf of F-S but IMO, these options could be 
> milestones applied in parallel toward migration to 2.0. However, a question 
> arises here, that you might have already seen, which is: given the major leap 
> in how SQLAlchemy 2.0 is designed, is it better to think of rebuilding 
> medium+ projects for 2.0 while maintaining existing codebases for 1.3? In 
> other words, how much will 2.0 be backward compatible with 1.3?

I'm hoping this is thoroughly addressed in the new documentation in particular 
https://docs.sqlalchemy.org/en/14/changelog/migration_14.html and 
https://docs.sqlalchemy.org/en/14/changelog/migration_20.html 





> 
>  A. 
> 
> On Fri, Feb 26, 2021, 5:18 PM Mike Bayer  wrote:
>> __
>> 
>> 
>> On Fri, Feb 26, 2021, at 8:04 AM, Ahmed wrote:
>>> Hi Mike - Thank you for your insights. Actually, this is part of upgrading 
>>> Flask-SQLAlchemy library dependency to 1.4.0b3 and eventually 2.0. The 
>>> snippet above is extracted from a test case that didn't pass against 
>>> 1.4.0b3.
>>> 
>>> I've checked sqlalchemy.orm.with_parent 
>>> 
>>>  (Python function, in Query API) documentation entry, however, it's not 
>>> clear to me how with_parent construct can fit in the implementation instead 
>>> of Query. I guess it would require a major change in how the library 
>>> (Flask-SQLAlchemy) is currently designed as it functionally extends 
>>> sqlalchemy.orm.Query and pass the extended class to relationship and other 
>>> constructs as well.
>> 
>> yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of 
>> Flask-SQLAlchemy.The Query and "dynamic" loaders are staying around 
>> largely so that Flask can come on board, however the patterns in F-S are 
>> pretty much the ones I want to get away from.
>> 
>> 2.0's spirit is one where the act of creating a SELECT statement is a 
>> standalone thing that is separate from being attached to any specific class 
>> (really all of SQLAlchemy was like this, but F-S has everyone doing the 
>> Model.query thing that I've always found to be more misleading than 
>> helpful), but SELECT statements are now also disconnected from any kind of 
>> "engine" or "Session" when constructed.
>> 
>> as for with_parent(), with_parent is what the dynamic loader actually uses 
>> to create the query.  so this is a matter of code organization.
>> 
>> F-S would have you say:
>> 
>> user = User.query.filter_by(name='name').first()
>> address = user.addresses.filter_by(email='email').first()
>> 
>> noting above, there's no "Session" anywhere.  where is it?   Here's a Hacker 
>> News comment lamenting the real world implications of this: 
>> https://news.ycombinator.com/item?id=26183936  
>> 
>> SQLAlchemy 2.0 would have you say instead:
>> 
>> with Session(engine) as session:
>> user = session.execute(
>>   select(User).filter_by(name='name')
>> ).scalars().first()
>>
>>address = session.execute(
>>select(Address).where(with_parent(user, 
>> Address.user)).filter_by(email='email')
>>).scalars().first()
>> 
>> Noting above, a web framework integration may still wish to provide the 
>> "session" to data-oriented methods and manage its scope, but IMO it should 
>> be an explicit object passed around.  The database connection / transaction 
>> shouldn't be made to appear to be inside the ORM model object, since that's 
>> not what's actually going on.
>> 
>> If you look at any commentary anywhere about SQLAlchemy, the top complaints 
>> are:
>> 
>> 1. too magical, too implicit
>> 
>> 2. what's wrong with just writing SQL?
>> 
>> SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user *is* 
>> writing SQL, they're running it into an execute() method, and they are 
>> managing the scope of connectivity and transactions in an obvious way.   
>> People don't necessarily want bloat and verbosity but they do want to see 
>> explicitness when the computer is being told to do something, especially 
>> running a SQL query.  We're trying to hit that balance as closely as 
>> possible.
>> 
>> The above style also has in mind compatibility with asyncio, which we now 
>> support.  With asyncio, it's very important that the boundary where IO 
>> occurs is very obvious.  Hence the Session.execute() method now becomes the 
>> place where users have to "yield".  With the older Query interface, the 
>> "yields" would be all over the place and kind of arbirary, since some Query 
>> methods decide to execute at one point or another.   
>> 
>> Flask-SQLAlchemy therefore has to decide where it wants to go with this 
>> direction, and there are options, including sticking with the legacy query / 
>> dynamic loader, perhaps vendoring a new interface that behaves in the 
>> flask-sqlalchemy style but uses 2.0-style patterns under the hood, or it can 

Re: [sqlalchemy] relationship query_class in SQLAlchemy 1.4.0b3

[Ahmed]

>
> yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of
> Flask-SQLAlchemy.The Query and "dynamic" loaders are staying around
> largely so that Flask can come on board, however the patterns in F-S are
> pretty much the ones I want to get away from.


2.0's spirit is one where the act of creating a SELECT statement is a
> standalone thing that is separate from being attached to any specific class
> (really all of SQLAlchemy was like this, but F-S has everyone doing the
> Model.query thing that I've always found to be more misleading than
> helpful), but SELECT statements are now also disconnected from any kind of
> "engine" or "Session" when constructed.



as for with_parent(), with_parent is what the dynamic loader actually uses
> to create the query.  so this is a matter of code organization.
> F-S would have you say:
>


user = User.query.filter_by(name='name').first()
> address = user.addresses.filter_by(email='email').first()
>


noting above, there's no "Session" anywhere.  where is it?   Here's a
> Hacker News comment lamenting the real world implications of this:
> https://news.ycombinator.com/item?id=26183936
>


SQLAlchemy 2.0 would have you say instead:
>


with Session(engine) as session:
> user = session.execute(
>   select(User).filter_by(name='name')
> ).scalars().first()
>
>address = session.execute(
>select(Address).where(with_parent(user,
> Address.user)).filter_by(email='email')
>).scalars().first()
>


Noting above, a web framework integration may still wish to provide the
> "session" to data-oriented methods and manage its scope, but IMO it should
> be an explicit object passed around.  The database connection / transaction
> shouldn't be made to appear to be inside the ORM model object, since that's
> not what's actually going on.


The newer design indeed provides a clearer view of the session.

If you look at any commentary anywhere about SQLAlchemy, the top complaints
> are:


> 1. too magical, too implicit


> 2. what's wrong with just writing SQL?


> SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user
> *is* writing SQL, they're running it into an execute() method, and they are
> managing the scope of connectivity and transactions in an obvious way.
> People don't necessarily want bloat and verbosity but they do want to see
> explicitness when the computer is being told to do something, especially
> running a SQL query.  We're trying to hit that balance as closely as
> possible.


> The above style also has in mind compatibility with asyncio, which we now
> support.  With asyncio, it's very important that the boundary where IO
> occurs is very obvious.  Hence the Session.execute() method now becomes the
> place where users have to "yield".  With the older Query interface, the
> "yields" would be all over the place and kind of arbirary, since some Query
> methods decide to execute at one point or another.


> Flask-SQLAlchemy therefore has to decide where it wants to go with this
> direction, and there are options, including sticking with the legacy query
> / dynamic loader, perhaps vendoring a new interface that behaves in the
> flask-sqlalchemy style but uses 2.0-style patterns under the hood, or it
> can go along with the 2.0 model for future releases.   From
> SQLAlchemy's point of view, the Query was always not well thought out and
> was inconsistent with how Core worked, and I've wanted for years to resolve
> that problem.


I'm not authorized to talk on behalf of F-S but IMO, these options could be
milestones applied in parallel toward migration to 2.0. However, a question
arises here, that you might have already seen, which is: given the major
leap in how SQLAlchemy 2.0 is designed, is it better to think of rebuilding
medium+ projects for 2.0 while maintaining existing codebases for 1.3? In
other words, how much will 2.0 be backward compatible with 1.3?

 A.

On Fri, Feb 26, 2021, 5:18 PM Mike Bayer  wrote:

>
>
> On Fri, Feb 26, 2021, at 8:04 AM, Ahmed wrote:
>
> Hi Mike - Thank you for your insights. Actually, this is part of upgrading
> Flask-SQLAlchemy library dependency to 1.4.0b3 and eventually 2.0. The
> snippet above is extracted from a test case that didn't pass against
> 1.4.0b3.
>
> I've checked sqlalchemy.orm.with_parent
> 
>  (Python
> function, in Query API) documentation entry, however, it's not clear to me
> how with_parent construct can fit in the implementation instead of Query.
> I guess it would require a major change in how the library
> (Flask-SQLAlchemy) is currently designed as it functionally extends
> sqlalchemy.orm.Query and pass the extended class to relationship and
> other constructs as well.
>
>
> yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of
> Flask-SQLAlchemy.The Query and "dynamic" loaders are staying around
> largely so that Flask