Re: [sqlalchemy] event updating sibling objects: commit() vs. flush()

[Gord Thompson]

Thanks, Mike. I was tempted to try and find a way to get at the session 
from within the event handler but thought that it might be a "Bad Idea"™ 
given that we were only given the `connection` to play with.

Your suggestion works fine in 1.4. (As I mentioned in one of the dev 
meetings, I'm now answering with1.4 solutions to questions unless they are 
specifically asking about 1.3. It's my way of helping to mitigate the 
"Internet Infinite Memory Effect" where people copy/paste code from old 
answers and wind up using techniques that are outdated.)

Cheers!

On Friday, February 5, 2021 at 8:20:16 PM UTC-7 Mike Bayer wrote:

>
>
> On Fri, Feb 5, 2021, at 6:37 PM, Gord Thompson wrote:
>
> Hi all. While trying to get more fluent with ORM I looked into a solution 
> for this Stack Overflow question 
> . For a many-to-one 
> relationship only one child object can have `is_default=True`. If we add a 
> new child object or update an existing one with `is_default=True` we need 
> to set the previous "default" child object to `is_default=False`.
>
> I put together the following and it seems to work fine.
>
> # https://stackoverflow.com/q/66027263/2144390
> import sqlalchemy as db
> from sqlalchemy import event
> from sqlalchemy.orm import declarative_base, relationship
>
> connection_uri = "sqlite://"
> engine = db.create_engine(
> connection_uri,
> future=True,
> echo=True,
> )
>
> Base = declarative_base()
>
>
> class Address(Base):
> __tablename__ = "address"
> id = db.Column(db.Integer, primary_key=True)
> user_id = db.Column(db.Integer, db.ForeignKey("user.id"), 
> nullable=False)
> address = db.Column(db.String, nullable=False)
> is_default = db.Column(db.Boolean, nullable=False, default=False)
> user = relationship("User", back_populates="addresses")
>
> def __repr__(self):
> return (
> f" f", is_default={self.is_default})>"
> )
>
>
> class User(Base):
> __tablename__ = "user"
> id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String)
> addresses = relationship("Address", back_populates="user")
>
> def __repr__(self):
> return f""
>
>
> Base.metadata.create_all(engine)
>
>
> def _remove_other_default_addrs(mapper, connection, target):
> if target.is_default:
> connection.execute(
> db.update(Address)
> .where(Address.user_id == target.user_id)
> .where(Address.id != target.id)
> .where(Address.is_default)
> .values(is_default=False)
> )
>
>
> @event.listens_for(Address, "after_insert")
> def receive_after_update(mapper, connection, target):
> _remove_other_default_addrs(mapper, connection, target)
>
>
> @event.listens_for(Address, "after_update")
> def receive_after_update(mapper, connection, target):
> _remove_other_default_addrs(mapper, connection, target)
>
>
> with db.orm.Session(engine, future=True) as session:
> gord = User(name="Gord")
> gord_old_addr = Address(user=gord, address="123 Old Ave", 
> is_default=True)
> session.add_all([gord, gord_old_addr])
> session.commit()
> print(gord_old_addr)
> gord_new_addr = Address(user=gord, address="567 New Blvd", 
> is_default=True)
> session.add(gord_new_addr)
> session.commit()
> print("==> session committed")
> print(f"==> gord_old_addr.is_default is {gord_old_addr.is_default}")
> # ==> gord_old_addr.is_default is False
> 
> However, I noticed that if I flushed the session instead of committing it, 
> the old child object remains unchanged
>
> print("==> session flushed")
> print(f"==> gord_old_addr.is_default is {gord_old_addr.is_default}")
> # ==> gord_old_addr.is_default is True
>
> Is there something I can add to my event listeners (or maybe the session 
> configuration) to ensure that the potentially affected objects get 
> refreshed without actually committing?
>
>
> so this is very much like the thing that the ORM does with bulk updates, 
> and in fact, you might be able to use the bulk update feature to do this.   
> the reason being that bulk update includes the behavior where it will go 
> into the session and locate matching objects, and expire the attributes 
> that were updated.to do this, it has either the 'fetch" or "evaluate" 
> strategy, and if you were using 1.4 with PostgreSQL, the "fetch" strategy 
> will actually use RETURNING to get the matched primary key back when it 
> does that UPDATE, the looks up that object in the identity map by key and 
> expires it.
>
> You could in theory do this yourself in your _remove_other_default_attrs 
> method. But, it is likely safe to break one of the rules thats in the 
> docs regarding after_insert/after_update and actually use the ORM session 
> version of the method.  this is much nicer in 1.4, but if you are in 1.3, 
> you can do it like this:
>
> def 

[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 
> ,  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/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.