[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] Re: SQLAlchemy join/has query with example code

[Chris Simpson]

Thanks Mike, the assurance it's the right idea was what I wanted to check.

All sorted much appreciated.

On Sun, 7 Feb 2021, 21:49 Mike Bayer,  wrote:

>
>
> On Sat, Feb 6, 2021, at 8:56 AM, Chris Simpson wrote:
>
> After posting, I have arrived at *a* solution (which might be awful)
> Please let me know if this is a bad approach or I'm following the api
> correctly:
>
> I have converted this SQL query:
>
> SELECT COUNT(*)
> FROM person
> JOIN subscription ON
> person.id = subscription.person_id
> JOIN plan ON
> subscription.sku_uuid = plan.uuid
> JOIN plan_requirements ON
> plan.id = plan_requirements.plan_id
> WHERE plan_requirements.subscription = 1
>
> Into the following SQLAlchemy query:
>
> database.session.query(Person)\
> .join(Subscription)\
> .join(Plan, Subscription.sku_uuid==Plan.uuid)\
> .join(PlanRequirements, Plan.id==PlanRequirements.plan_id)\
> .filter(PlanRequirements.subscription==1).all()
>
>
> seems to be the right idea except you aren't emitting the "COUNT(*)" part
> of it, not sure if that's what you wanted.
>
>
> Kind regards,
>
> Chris
> On Saturday, 6 February 2021 at 13:42:54 UTC Chris Simpson wrote:
>
> Hello,
>
> I'm trying to convert this working SQL query: (SQLAlchemy models are below)
>
> SELECT COUNT(*)
> FROM person
> JOIN subscription ON
> person.id = subscription.person_id
> JOIN plan ON
> subscription.sku_uuid = plan.uuid
> JOIN plan_requirements ON
> plan.id = plan_requirements.plan_id
> WHERE plan_requirements.subscription = 1
>
> Into a SQLAlchemy query. so far from reading the docs
> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html>,  I have the
> following:
>
> database.session.query(Person).join(Subscription).filter(Subscription.plan.has()
> ).all()
>
> With the objective: Show me all people who have at least one plan with the
> plan_requirements.subscription set to 1 (meaning true).
>
> Do I need to somehow keep chaining my joins?
>
> My SQLAlchemy Models are: (full code is also linked at end)
>
> class Person(database.Model):
> __tablename__ = "person"
> id = database.Column(database.Integer(), primary_key=True)
> uuid = database.Column(database.String(), default=uuid_string)
> given_name = database.Column(database.String())
> family_name = database.Column(database.String())
> subscriptions = relationship("Subscription", back_populates="person")
>
> class Plan(database.Model):
> __tablename__ = "plan"
> id = database.Column(database.Integer(), primary_key=True)
> uuid = database.Column(database.String(), default=uuid_string)
> requirements = relationship(
> "PlanRequirements", uselist=False, back_populates="plan"
> )
>
>
> class PlanRequirements(database.Model):
> __tablename__ = "plan_requirements"
> id = database.Column(database.Integer(), primary_key=True)
> plan_id = database.Column(database.Integer(), ForeignKey("plan.id"))
> plan = relationship("Plan", back_populates="requirements")
> instant_payment = database.Column(database.Boolean(), default=False)
> subscription = database.Column(database.Boolean(), default=False)
>
> Full source code of models:
> https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40
> <https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40>
>
> Much appreciated if someone can point me in the right directly. I'm
> confident with the SQL quiery, just not how to convert that to the ORM.
>
>
> --
> 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/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com?utm_medium=email_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 f

[sqlalchemy] Re: SQLAlchemy join/has query with example code

[Chris Simpson]

After posting, I have arrived at *a* solution (which might be awful) Please 
let me know if this is a bad approach or I'm following the api correctly:

I have converted this SQL query:

SELECT COUNT(*)
FROM person
JOIN subscription ON
person.id = subscription.person_id
JOIN plan ON 
subscription.sku_uuid = plan.uuid
JOIN plan_requirements ON
plan.id = plan_requirements.plan_id
WHERE plan_requirements.subscription = 1

Into the following SQLAlchemy query: 

database.session.query(Person)\
.join(Subscription)\
.join(Plan, Subscription.sku_uuid==Plan.uuid)\
.join(PlanRequirements, Plan.id==PlanRequirements.plan_id)\
.filter(PlanRequirements.subscription==1).all()

Kind regards,

Chris

On Saturday, 6 February 2021 at 13:42:54 UTC Chris Simpson wrote:

> Hello,
>
> I'm trying to convert this working SQL query: (SQLAlchemy models are below)
>
> SELECT COUNT(*)
> FROM person
> JOIN subscription ON
> person.id = subscription.person_id
> JOIN plan ON 
> subscription.sku_uuid = plan.uuid
> JOIN plan_requirements ON
> plan.id = plan_requirements.plan_id
> WHERE plan_requirements.subscription = 1
>
> Into a SQLAlchemy query. so far from reading the docs 
> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html>,  I have the 
> following:
>
> database.session.query(Person).join(Subscription).filter(Subscription.plan.has()
>  
> ).all()
>
> With the objective: Show me all people who have at least one plan with the 
> plan_requirements.subscription set to 1 (meaning true).
>
> Do I need to somehow keep chaining my joins?
>
> My SQLAlchemy Models are: (full code is also linked at end)
>
> class Person(database.Model):
> __tablename__ = "person"
> id = database.Column(database.Integer(), primary_key=True)
> uuid = database.Column(database.String(), default=uuid_string)
> given_name = database.Column(database.String())
> family_name = database.Column(database.String())
> subscriptions = relationship("Subscription", back_populates="person")
>
> class Plan(database.Model):
> __tablename__ = "plan"
> id = database.Column(database.Integer(), primary_key=True)
> uuid = database.Column(database.String(), default=uuid_string)
> requirements = relationship(
> "PlanRequirements", uselist=False, back_populates="plan"
> )
>
>
> class PlanRequirements(database.Model):
> __tablename__ = "plan_requirements"
> id = database.Column(database.Integer(), primary_key=True)
> plan_id = database.Column(database.Integer(), ForeignKey("plan.id"))
> plan = relationship("Plan", back_populates="requirements")
> instant_payment = database.Column(database.Boolean(), default=False)
> subscription = database.Column(database.Boolean(), default=False)
>
> Full source code of models: 
> https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40 
>  
> <https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40>
>
> Much appreciated if someone can point me in the right directly. I'm 
> confident with the SQL quiery, just not how to convert that to the ORM.
>
>

-- 
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/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com.

[sqlalchemy] SQLAlchemy join/has query with example code

[Chris Simpson]

Hello,

I'm trying to convert this working SQL query: (SQLAlchemy models are below)

SELECT COUNT(*)
FROM person
JOIN subscription ON
person.id = subscription.person_id
JOIN plan ON 
subscription.sku_uuid = plan.uuid
JOIN plan_requirements ON
plan.id = plan_requirements.plan_id
WHERE plan_requirements.subscription = 1

Into a SQLAlchemy query. so far from reading the docs 
,  I have the 
following:

database.session.query(Person).join(Subscription).filter(Subscription.plan.has()
 
).all()

With the objective: Show me all people who have at least one plan with the 
plan_requirements.subscription set to 1 (meaning true).

Do I need to somehow keep chaining my joins?

My SQLAlchemy Models are: (full code is also linked at end)

class Person(database.Model):
__tablename__ = "person"
id = database.Column(database.Integer(), primary_key=True)
uuid = database.Column(database.String(), default=uuid_string)
given_name = database.Column(database.String())
family_name = database.Column(database.String())
subscriptions = relationship("Subscription", back_populates="person")

class Plan(database.Model):
__tablename__ = "plan"
id = database.Column(database.Integer(), primary_key=True)
uuid = database.Column(database.String(), default=uuid_string)
requirements = relationship(
"PlanRequirements", uselist=False, back_populates="plan"
)


class PlanRequirements(database.Model):
__tablename__ = "plan_requirements"
id = database.Column(database.Integer(), primary_key=True)
plan_id = database.Column(database.Integer(), ForeignKey("plan.id"))
plan = relationship("Plan", back_populates="requirements")
instant_payment = database.Column(database.Boolean(), default=False)
subscription = database.Column(database.Boolean(), default=False)

Full source code of models: 
https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40  


Much appreciated if someone can point me in the right directly. I'm 
confident with the SQL quiery, just not how to convert that to the ORM.

-- 
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/8ae57449-73b5-4c2a-81e8-aec971a42ff2n%40googlegroups.com.