Re: [sqlalchemy] Cascade child updates onto the parent

[Colton Allen]

Perfect.  That's exactly what I ended up doing.  I added events 
(after_insert/update/delete) for each backref.

For each has-many relationship (through a secondary table) I had to 
consider the fact that the parent model would exist in session.dirty but 
not trigger the "onupdate" action on the column.  So I added a generic 
before_update/delete event on my models' base class which is basically just 
target.updated_at = dt.now().

On Thursday, May 28, 2020 at 11:06:28 AM UTC-5, Mike Bayer wrote:
>
>
>
> On Wed, May 27, 2020, at 3:57 PM, Colton Allen wrote:
>
> Hello,
>
> I'm trying to automate a backref update.  Basically, when a child model is 
> inserted or updated I want the parent model's "updated_at" column to 
> mutate.  The value should be the approximate time the user-child-model was 
> updated.  The updated_at value would not have to match the 
> created_at/updated_at value on the child.  It would just need to mutate to 
> a new time.
>
> class UserModel(db.Model):
> updated_at = db.Column(db.DateTime, default=db.now, onupdate=datetime.
> now)
>
>
> class UserChildModel(db.Model):
> user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=
> False)
> user = db.relationship('UserModel', backref='children')
>
> user = UserModel()
> save(user)
> print(user.updated_at) # x
>
> child = UserChildModel(user_id=user.id)
> save(child)
> print(user.updated_at) # y (value changed)
>
> Hopefully this pseudocode is sufficient.
>
> I'm wondering if there is an option I can specify on the orm.relationship 
> factory.  Or will I need to define an event?
>
>
> that could certainly be based on an event from the SQLAlchemy side. a 
> very straightforward one would be the before_insert / before_update / 
> after_insert / after_update suite of events, I would emit an UPDATE 
> statement against the parent table using the foreign key on the child row 
> that's being inserted/updated.  Another approach would be a DB trigger.
>
> the mapper level events are detailed at 
> https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_insert#sqlalchemy.orm.events.MapperEvents.before_insert
>
> the "connection" right there is where you'd run your update, like:
>
> connection.execute(update(parent).values(updated_at=datetime.now()).where(
> parent.id == inserted.parent_id))
>
>
>
>
>
> Thanks!
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/490bfcd2-4ebd-4df3-98a8-516caeacbd6a%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/555b8c57-85cb-48fa-9e29-491796877fd6%40googlegroups.com.

Re: [sqlalchemy] Cascade child updates onto the parent

[Mike Bayer]

On Wed, May 27, 2020, at 3:57 PM, Colton Allen wrote:
> Hello,
> 
> I'm trying to automate a backref update. Basically, when a child model is 
> inserted or updated I want the parent model's "updated_at" column to mutate. 
> The value should be the approximate time the user-child-model was updated. 
> The updated_at value would not have to match the created_at/updated_at value 
> on the child. It would just need to mutate to a new time.
> 
> class UserModel(db.Model):
>  updated_at = db.Column(db.DateTime, default=db.now, onupdate=datetime.now)
> 
> 
> class UserChildModel(db.Model):
>  user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
>  user = db.relationship('UserModel', backref='children')
> 
> user = UserModel()
> save(user)
> print(user.updated_at) # x
> 
> child = UserChildModel(user_id=user.id)
> save(child)
> print(user.updated_at) # y (value changed)
> 
> Hopefully this pseudocode is sufficient.
> 
> I'm wondering if there is an option I can specify on the orm.relationship 
> factory. Or will I need to define an event?

that could certainly be based on an event from the SQLAlchemy side. a very 
straightforward one would be the before_insert / before_update / after_insert / 
after_update suite of events, I would emit an UPDATE statement against the 
parent table using the foreign key on the child row that's being 
inserted/updated. Another approach would be a DB trigger.

the mapper level events are detailed at 
https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_insert#sqlalchemy.orm.events.MapperEvents.before_insert

the "connection" right there is where you'd run your update, like:

connection.execute(update(parent).values(updated_at=datetime.now()).where(parent.id
 == inserted.parent_id))




> 
> Thanks!
> 

> --
>  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/490bfcd2-4ebd-4df3-98a8-516caeacbd6a%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/57bd5e2e-f5e4-49de-9668-e48051364f17%40www.fastmail.com.

[sqlalchemy] Cascade child updates onto the parent

[Colton Allen]

Hello,

I'm trying to automate a backref update.  Basically, when a child model is 
inserted or updated I want the parent model's "updated_at" column to 
mutate.  The value should be the approximate time the user-child-model was 
updated.  The updated_at value would not have to match the 
created_at/updated_at value on the child.  It would just need to mutate to 
a new time.

class UserModel(db.Model):
updated_at = db.Column(db.DateTime, default=db.now, onupdate=datetime.
now)


class UserChildModel(db.Model):
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False
)
user = db.relationship('UserModel', backref='children')

user = UserModel()
save(user)
print(user.updated_at) # x

child = UserChildModel(user_id=user.id)
save(child)
print(user.updated_at) # y (value changed)

Hopefully this pseudocode is sufficient.

I'm wondering if there is an option I can specify on the orm.relationship 
factory.  Or will I need to define an event?

Thanks!

-- 
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/490bfcd2-4ebd-4df3-98a8-516caeacbd6a%40googlegroups.com.

Re: [sqlalchemy] Cascade relationship from child to parent

[Mike Bayer]

On Fri, Sep 14, 2018 at 4:46 PM, Edans Sandes  wrote:
> Hello,
>
> I got stuck on a problem related to relatioships and cascades. In the docs,
> we have the following explanation:
>
> Cascades:
>
>
>
> Mappers support the concept of configurable cascade behavior on
> relationship() constructs. This refers to how operations performed on a
> “parent” object relative to a particular Session should be propagated to
> items referred to by that relationship (e.g. “child” objects), and is
> affected by the relationship.cascade option.
>
>
>
> It seems to me that the cascade attribute works only in the parent->child
> direction. Right now, I'm trying to figure out how to create one child with
> its parent_id, followed by an automatic refresh of its parent children list
> (i.e. without appending the child to the parent children list). Maybe it is
> not a good practice to use parent ids in ORM, but I'm wondering if it is
> possible to do it so. Sometimes we only want to create a child to a
> parent_id, without loading the parent object.
>
>
> Let me show the source code that reproduce my problem:
>
> from sqlalchemy import Table, Column, Integer, String, ForeignKey
> from sqlalchemy.orm import relationship, backref, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import create_engine
>
> Base = declarative_base()
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parent'
> parent_id = Column(Integer, primary_key=True)
> children = relationship("Child", back_populates="parent", cascade="all")
>
> class Child(Base):
> __tablename__ = 'child'
> child_id = Column(Integer, primary_key=True)
> name = Column(String)
> parent_id = Column(Integer, ForeignKey('parent.parent_id'))
> parent = relationship("Parent", back_populates="children",
> cascade="all")
>
> engine = create_engine('sqlite:///:memory:', echo=False)
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> Base.metadata.create_all(engine)
>
> s = Session()
>
> p = Parent()
> c1 = Child(name="C1")
> p.children.append(c1)
>
> s.add(p)
> s.flush()
>
> print([x.name for x in p.children])  # ['C1']
>
> c2 = Child(parent_id=1, name="C2")
> s.add(c2)
> s.flush()
>
> print([x.name for x in p.children])  # ['C1']
>
> s.refresh(c2.parent) # This solves my problem, but I would like to know how
> to do it automatically without an explicit refresh
>
> print([x.name for x in p.children])  # ['C1','C2']
>
> s.close()
>
>
>
> In this code, when I create child "C2" pointing it to parent_id=1, the
> parent object (children relationship) is not refreshed automatically. So, it
> only prints the first child: C1. After the refresh, I see both children C1
> and C2.

This FAQ entry should cover everything, note there's an event-based
recipe at the bottom as well:

http://docs.sqlalchemy.org/en/latest/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7




> Is there any option that refresh the parent authomatically? Is there
> anything that I'm missing?
>
>
> Thanks!
> Edans Sandes
>
> --
> 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] Cascade relationship from child to parent

[Edans Sandes]

Hello, 

I got stuck on a problem related to relatioships and cascades. In the docs 
, we have the 
following explanation:

Cascades:

 

*Mappers support the concept of configurable cascade behavior 
on relationship() 

 constructs. This 
refers to how operations performed on a “parent” object relative to a 
particular Session 

 should 
be propagated to items referred to by that relationship (e.g. “child” 
objects), and is affected by the relationship.cascade 

 option.*



It seems to me that the cascade attribute works only in the parent->child 
direction. Right now, I'm trying to figure out how to create one child with 
its parent_id, followed by an automatic refresh of its parent children list 
(i.e. without appending the child to the parent children list). Maybe it is 
not a good practice to use parent ids in ORM, but I'm wondering if it is 
possible to do it so. Sometimes we only want to create a child to a 
parent_id, without loading the parent object.


Let me show the source code that reproduce my problem:

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()

Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'
parent_id = Column(Integer, primary_key=True)
children = relationship("Child", back_populates="parent", cascade="all")

class Child(Base):
__tablename__ = 'child'
child_id = Column(Integer, primary_key=True)
name = Column(String)
parent_id = Column(Integer, ForeignKey('parent.parent_id'))
parent = relationship("Parent", back_populates="children", 
cascade="all")

engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
Base.metadata.create_all(engine)

s = Session()

p = Parent()
c1 = Child(name="C1")
p.children.append(c1)

s.add(p)
s.flush()

print([x.name for x in p.children])  # ['C1']

c2 = Child(parent_id=1, name="C2")
s.add(c2)
s.flush()

print([x.name for x in p.children])  # ['C1']

s.refresh(c2.parent) # This solves my problem, but I would like to know how 
to do it automatically without an explicit refresh

print([x.name for x in p.children])  # ['C1','C2']

s.close()



In this code, when I create child "C2" pointing it to parent_id=1, the 
parent object (children relationship) is not refreshed automatically. So, 
it only prints the first child: C1. After the refresh, I see both children 
C1 and C2.
Is there any option that refresh the parent authomatically? Is there 
anything that I'm missing?


Thanks!
Edans Sandes

-- 
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] Cascade delete-orphan: reattach child to another parent

[Serhii Mozghovyi]

Many thanks!

On Wednesday, March 28, 2018 at 6:42:16 PM UTC+3, Mike Bayer wrote:
>
> the backrefs intentionally don't keep fanning deep into object graph 
> for this kind of thing, so if you want it to go one hop further you 
> can add an event to do that directly: 
>
> from sqlalchemy import event 
> from sqlalchemy.orm import attributes 
>
>
> @event.listens_for(Address.user, "set") 
> def _intercept_set(target, value, oldvalue, initiator): 
>
> if isinstance(oldvalue, User) and "address" in oldvalue.__dict__: 
> attributes.set_committed_value(oldvalue, "address", None) 
>
>
> the "set_committed_value" is to avoid triggering any new events which 
> will cause a recursion overflow. 
>
>
>
> On Wed, Mar 28, 2018 at 10:49 AM, Serhii Mozghovyi  > wrote: 
> > Is it possible to make child objects (many-to-one side) delete itself 
> from 
> > the old parent's collection when it is added to a different parent? 
> > See the file attached. The old parent remains unaware that he doesn't 
> have 
> > this child anymore. 
> > 
> > P.S. session.expire() is an obvious solution but too heavy. I expect 
> some 
> > event-based collection synchronization, much like backref 
> (back_populates) 
> > connected collections. 
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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] Cascade delete-orphan: reattach child to another parent

[Mike Bayer]

the backrefs intentionally don't keep fanning deep into object graph
for this kind of thing, so if you want it to go one hop further you
can add an event to do that directly:

from sqlalchemy import event
from sqlalchemy.orm import attributes


@event.listens_for(Address.user, "set")
def _intercept_set(target, value, oldvalue, initiator):

if isinstance(oldvalue, User) and "address" in oldvalue.__dict__:
attributes.set_committed_value(oldvalue, "address", None)


the "set_committed_value" is to avoid triggering any new events which
will cause a recursion overflow.



On Wed, Mar 28, 2018 at 10:49 AM, Serhii Mozghovyi  wrote:
> Is it possible to make child objects (many-to-one side) delete itself from
> the old parent's collection when it is added to a different parent?
> See the file attached. The old parent remains unaware that he doesn't have
> this child anymore.
>
> P.S. session.expire() is an obvious solution but too heavy. I expect some
> event-based collection synchronization, much like backref (back_populates)
> connected collections.
>
> --
> 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] Cascade delete-orphan: reattach child to another parent

[Serhii Mozghovyi]

Is it possible to make child objects (many-to-one side) delete itself from 
the old parent's collection when it is added to a different parent? 
See the file attached. The old parent remains unaware that he doesn't have 
this child anymore.

P.S. session.expire() is an obvious solution but too heavy. I expect some 
event-based collection synchronization, much like backref (back_populates) 
connected collections.

-- 
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.
from sqlalchemy import create_engine, MetaData, Column, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('mysql://user@localhost/probe')

Base = declarative_base(bind=engine)

Session = sessionmaker(bind=engine)
session = Session()


class User(Base):
__tablename__ = 'user'
name = Column(String(10), primary_key=True)
address = relationship('Address', uselist=False, cascade='all,delete-orphan', back_populates='user')


class Address(Base):
__tablename__ = 'address'
addr = Column(String(20))
username = Column(ForeignKey(User.name), primary_key=True)
user = relationship('User', back_populates='address')


if __name__ == '__main__':
joe, jef = session.query(User).all()
if joe.name != 'joe':
joe, jef = jef, joe

assert joe.address is not None
assert jef.address is None

jef.address = joe.address

# HERE IS THE PROBLEM: it's expected to become None
assert joe.address is not None
session.flush()
assert joe.address is not None  # not even flush() helps

# Here's how sample objects were created:
# Base.metadata.create_all()
# joe = User(name='joe')
# jef = User(name='jef')
# session.add_all((joe, jef))
# joe.address = Address(addr='joe str')
# session.commit()

[sqlalchemy] Cascade Delete, session confusion

[Dmitry Berman]

Hi all,

I just started using SQLA, and I am confused by some cascade delete 
behaviour I am seeing. Please see the code and tests below which show that 
the session is seeing table rows in one area and not in the other:

CODE:

print Product1Mod3
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
product_name = Column(String(250), unique=True)
vendor_id = Column(Integer, ForeignKey('vendors.id'), nullable=False)

vendor = relationship('Vendor', backref = backref('products', 
order_by=id, cascade=all, delete-orphan))

def __init__(self, product_name, vendor_id):
self.product_name = product_name
self.vendor_id = vendor_id

def __repr__(self):
return 'Product: %r Product ID: %r Vendor ID: %r' % 
(self.product_name, self.id, self.vendor_id)


class Module(Base):
__tablename__ = 'modules'
id = Column(Integer, primary_key=True)
module_name = Column(String(250), unique=True)
product_id = Column(Integer, ForeignKey('products.id'), nullable=False)

product = relationship('Product', backref = backref('modules', 
order_by=id, cascade=all, delete-orphan))

def __init__(self, module_name, product_id):
self.module_name = module_name
self.product_id = product_id


def __repr__(self):
return 'Module: %r Module ID: %r Product ID: %r' % 
(self.module_name, self.id ,self.product_id)

TESTING:

msg('Module Tests')
Product2Mod1 = Module('Product2Mod1',1)
Product2Mod2 = Module('Product2Mod2',1)
Product1Mod1 = Module('Product1Mod1',2)
Product1Mod2 = Module('Product1Mod2',2)
Product1Mod3 = Module('Product1Mod3',2)
db_session.add(Product2Mod1)
db_session.add(Product2Mod2)
db_session.add(Product1Mod1)
db_session.add(Product1Mod2)
db_session.add(Product1Mod3)
db_session.commit()
msg(Product2Mod1 Product:)
print Product2Mod1.product

msg('delete tests')

print Query to show all products: \n
print Product.query.all()

print \nQuery to show all modules: \n
print Module.query.all()

print \ndeleting product 1: db_session.delete(Product1) -- 
db_session.commit()
db_session.delete(Product1)
db_session.commit()

print \nQuery to check for changes with products and modules, shows that 
the modules and product are gone:\n
print Product.query.all()
print Module.query.all()

print \nThe modules below belong to the deleted product, they should have 
disappeared, but do not: -- NOT SURE WHY THIS IS HAPPENING
print Product1Mod1
print Product1Mod2
print Product1Mod3

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Cascade Delete, session confusion

[Michael Bayer]

On Mar 9, 2014, at 3:18 PM, Dmitry Berman dmikha...@gmail.com wrote:

 
 print \nQuery to check for changes with products and modules, shows that the 
 modules and product are gone:\n
 print Product.query.all()
 print Module.query.all()
 
 print \nThe modules below belong to the deleted product, they should have 
 disappeared, but do not: -- NOT SURE WHY THIS IS HAPPENING
 print Product1Mod1
 print Product1Mod2
 print Product1Mod3


not sure what you're expecting there, are you expecting that the Product1Mod1 
symbol would be modified within your interpreter to be None?  Python can't do 
that under normal circumstances.  A variable always points to the thing 
that it was assigned to, there's no (normal, non-hacky) mechanism by which 
variables change into None without explicitly being reassigned.

Those product objects represent what used to be in those rows.  They have a 
deleted flag you can see:

 from sqlalchemy import inspect
 inspect(deleted_product).deleted
True

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Cascade Delete, session confusion

[Dmitry Berman]

This makes a lot of sense, I just didn't realize how it worked... 

*When I did the following:*
print inspect(Product1Mod1).deleted
print inspect(Product1Mod2).deleted
print inspect(Product1Mod3).deleted

*Instead of just:*
print Product1Mod1 
print Product1Mod2 
print Product1Mod3 

This returned:
True
True
True

And this makes total sense now. Thanks.


On Sunday, March 9, 2014 7:22:49 PM UTC-4, Michael Bayer wrote:


 On Mar 9, 2014, at 3:18 PM, Dmitry Berman dmik...@gmail.com javascript: 
 wrote: 

  
  print \nQuery to check for changes with products and modules, shows 
 that the modules and product are gone:\n 
  print Product.query.all() 
  print Module.query.all() 
  
  print \nThe modules below belong to the deleted product, they should 
 have disappeared, but do not: -- NOT SURE WHY THIS IS HAPPENING 
  print Product1Mod1 
  print Product1Mod2 
  print Product1Mod3 


 not sure what you’re expecting there, are you expecting that the 
 “Product1Mod1” symbol would be modified within your interpreter to be None? 
  Python can’t do that under print inspect(Product1Mod1).deleted
 print inspect(Product1Mod2).deleted
 print inspect(Product1Mod3).deletednormal circumstances.  A variable 
 always points to the thing that it was assigned to, there’s no (normal, 
 non-hacky) mechanism by which variables change into “None” without 
 explicitly being reassigned. 

 Those product objects represent what used to be in those rows.  They have 
 a “deleted” flag you can see: 

  from sqlalchemy import inspect 
  inspect(deleted_product).deleted 
 True 



-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] cascade delete in relationship and session.execute(table.update())

[Wu-bin Zhen]

I really appreciate your help! It works great without any problem.
I tried session.refresh(storeobject) and I was wondering why it didn't
work, now I learned the difference from your method.

Again, thank you very much, and have a great weekend.


On Tue, Jan 3, 2012 at 10:56 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Jan 2, 2012, at 4:06 PM, Wubin wrote:

 
  class Product(PolymorphicClass): #there are different types of the
  product
 __tablename__ = products
 id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
 storeId = Column(store_id, Integer, ForeignKey(store.id),
  key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
 cascade=all,
  delete))
 
  class Store(object):
 __tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
 name = Column(name, String(50), unique=True, nullable=False)
 
  I tried to use query object to update the storeId column in the
  Product class, like:
 
 session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
  newStoreId})
 
  but the sqlalchemy rejected this with the Only update via a single
  table query is currently supported message.

 This would indicate that PolymorphicClass is mapped to a table as well.
  A DELETE or UPDATE statement, in standard SQL, doesn't support more than
 one table being affected at the same time (only MySQL has an extended
 syntax that supports this but it's not supported by the ORM).   There's
 also a syntax that supports only one table being updated, but multiple
 tables in the FROM clause which on Postgresql is UPDATE..FROM, and
 SQLAlchemy now supports that too, but again the ORM doesn't yet have
 support for that to be integrated.


  So then I decided to use
  session.execute(Product.__table__.values().where()) to update the
  table and it works fine.

 OK


  But in the final step deleting old store, I
  tried to delete the store object(now the store has no product after
  the update), and the store object is deleted...but with the products
  that previously belong to this store.

 
  I guess the cascade delete in the relationship does not notice if I
  use session.execute() to update the table. So my question is...(1) Is
  there anyway to tell the relationship hey now those products no
  longer belong to you, and you shouldn't delete them when you are to
  deleted?

 yeah just expire the collection:

 session.expire(storeobject, ['name_of_products_collection'])

  (2) Is there any trick, even the polymorphic class can use
  the query object to update table, without getting Only update via a
  single table query error? I still prefer to use session.query()
  instead of session.execute()...

 Right now you can only pass in the base class, I took a look since we do
 support UPDATE..FROM for supporting DBs, the controversial part here is
 that an UPDATE against the child table which then refers to the base table
 would need WHERE criterion to join the two together, which introduces
 tricky decisionmaking.   But one possibility is to just leave that up to
 the user in this case.

 I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
 possibility.


 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] cascade delete in relationship and session.execute(table.update())

[Michael Bayer]

On Jan 2, 2012, at 4:06 PM, Wubin wrote:

 
 class Product(PolymorphicClass): #there are different types of the
 product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
   name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
 key=storeId)
   store = relationship(Store,
   uselist=False,
   backref=backref(_products, collection_class=set, 
 cascade=all,
 delete))
 
 class Store(object):
__tablename__ = stores
   id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
 
 I tried to use query object to update the storeId column in the
 Product class, like:
 session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
 newStoreId})
 
 but the sqlalchemy rejected this with the Only update via a single
 table query is currently supported message.

This would indicate that PolymorphicClass is mapped to a table as well.A 
DELETE or UPDATE statement, in standard SQL, doesn't support more than one 
table being affected at the same time (only MySQL has an extended syntax that 
supports this but it's not supported by the ORM).   There's also a syntax that 
supports only one table being updated, but multiple tables in the FROM clause 
which on Postgresql is UPDATE..FROM, and SQLAlchemy now supports that too, 
but again the ORM doesn't yet have support for that to be integrated.


 So then I decided to use
 session.execute(Product.__table__.values().where()) to update the
 table and it works fine.

OK


 But in the final step deleting old store, I
 tried to delete the store object(now the store has no product after
 the update), and the store object is deleted...but with the products
 that previously belong to this store.

 
 I guess the cascade delete in the relationship does not notice if I
 use session.execute() to update the table. So my question is...(1) Is
 there anyway to tell the relationship hey now those products no
 longer belong to you, and you shouldn't delete them when you are to
 deleted?

yeah just expire the collection:

session.expire(storeobject, ['name_of_products_collection'])

 (2) Is there any trick, even the polymorphic class can use
 the query object to update table, without getting Only update via a
 single table query error? I still prefer to use session.query()
 instead of session.execute()...

Right now you can only pass in the base class, I took a look since we do 
support UPDATE..FROM for supporting DBs, the controversial part here is that an 
UPDATE against the child table which then refers to the base table would need 
WHERE criterion to join the two together, which introduces tricky 
decisionmaking.   But one possibility is to just leave that up to the user in 
this case.

I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this 
possibility.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] cascade delete in relationship and session.execute(table.update())

[Wubin]

Hi,

Currently I am trying to implement a feature that transfers all
products in a store to another store, and then destroy the old store
which contains no product. The sqlalchemy version is 0.6.8.

class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set, 
cascade=all,
delete))

class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)

I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})

but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message. So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine. But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.

I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted? (2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...

Thank you very much, and happy new year!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Cascade in many-to-many relationships: when orphans are not orphans

[Benjamin Sims]

Thanks for the help with my query the other day - as ever response was swift
and bang on.

I'm now trying to set up another m:n relationship in ORM correctly.
Pseudocode:

parentA.children.append(child1)
parentA.children.append(child2)

parentB.children.append(child2)

session.delete(parentA)

At this stage, I would like child1 to be deleted and child2 to survive.

However, if I use (cascade = all), then both children will be deleted when
ParentA is. I hoped that delete-orphan would be applicable in this
situation, but that requires that single-parent be True, which I understand
it cannot for a true many-to-many.

So I guess what I am asking is - is it possible for child objects which have
still have remaining parents to survive, while deleting those with no
parents left?

Thanks,
Ben

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Cascade in many-to-many relationships: when orphans are not orphans

[Michael Bayer]

On Aug 25, 2011, at 7:06 PM, Benjamin Sims wrote:

 Thanks for the help with my query the other day - as ever response was swift 
 and bang on.
 
 I'm now trying to set up another m:n relationship in ORM correctly. 
 Pseudocode:
 
 parentA.children.append(child1)
 parentA.children.append(child2)
 
 parentB.children.append(child2)
 
 session.delete(parentA)
 
 At this stage, I would like child1 to be deleted and child2 to survive. 
 
 However, if I use (cascade = all), then both children will be deleted when 
 ParentA is. I hoped that delete-orphan would be applicable in this situation, 
 but that requires that single-parent be True, which I understand it cannot 
 for a true many-to-many.
 
 So I guess what I am asking is - is it possible for child objects which have 
 still have remaining parents to survive, while deleting those with no parents 
 left?

That's not something supported by delete, delete-orphan cascade and its why 
the single_parent=True flag is required - so that users aren't misled into 
thinking it can work that way.

You'd need to roll this using attribute events most likely.   The potential 
expense is that you may have to emit SQL in order to load the full collection 
of parents for each child in order to detect the orphan condition (which is 
why SQLA doesn't support this automatically, it would be extremely inefficient 
implemented generically).


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Cascade Deletes

[Michael Bayer]

On Jul 25, 2011, at 9:47 AM, Aviv Giladi wrote:

 I can't seem to make cascade deletes work in sqlalchemy.
 
 I have a parent class (called Rating), a sub class (Subrating) and a
 third class called SubRatingProperty.
 
 There is a one-to-one relationship between Rating and SubRating - each
 Rating can only have one specific SubRating object. Next, the
 SubRatingProperty refers to a row in a table with fixed values. There
 are 3 SubRatingProperty entries - property1, property2 and property3.
 The SubRating class can have one or more of either property1,
 property2 and property3, therefore the relationship is many-to-many (a
 SubRatingProperty can have more than one properties, and for example
 property1 can be assigned to more than one SubRatingProperty's).
 
 Here is the code that defines all of this:
 
 subrating_subratingproperty_association =
 Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
 ForeignKey('subratings.id')),
Column('subrating_property_id',
 Integer, ForeignKey('subrating_properties.id')))
 
 class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
 
 secondary=subrating_subratingproperty_association,
backref=subrating_properties)
 
 class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
 
 class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
 uselist=False))
 Everything works fine, but I can't figure out how to do cascade
 deletes. I am deleting Rating objects, and when I do, I would like the
 according SubRating object to be deleted, as well as all the entries
 in the association table. So deleting Rating1 would delete its
 SubRating, as well as all the connection between the SubRating and
 SubRatingProperty's.
 
 I have tried adding cascade=all to the relationship call,

you have two relationships() here to build the full chain so you'd need 
cascade='all, delete-orphan' on both Rating.subrating as well as 
SubRating.subrating_properties (use the backref() function instead of a string 
to establish the cascade rule on that end.

 and I also
 tried adding ondelete=cascade) to the ForeignKey call.

if all the involved FOREIGN KEYs are generated with ON DELETE CASCADE as this 
would accomplish, as long as you are not on SQLIte or MySQL MyISAM the deletes 
will be unconditional.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Cascade Deletes

[Aviv Giladi]

I can't seem to make cascade deletes work in sqlalchemy.

I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.

There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).

Here is the code that defines all of this:

subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))

class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
 
secondary=subrating_subratingproperty_association,
backref=subrating_properties)

class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)

class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.

I have tried adding cascade=all to the relationship call, and I also
tried adding ondelete=cascade) to the ForeignKey call. Nothing
seemed to have worked.

How do I set up this cascade deletes business?

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Cascade option, what does all mean?

[Eloff]

Hi,

I see cascade='all, delete, delete-orphan' in the tutorial, but I
thought I read in the docs elsewhere (can't seem to find the place
atm) that all includes merge, delete, and others so that cascade='all,
delete-orphan' should be equivalent?

Is this correct?

Thanks,
-Dan
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] cascade delete-orphan

[GustaV]

Hi all,
I try to set up a many-to-many relation with an association object.

But I want something not usual: I want the child object deleted when
not owned by any parent anymore.
This is for a messages/recipients relation: the message is useless
when everybody removed it from its mailbox!

I tried that, but it doesn't work:

class Parent(meta.DeclarativeBase):
id = Column(types.Integer, primary_key=True)

class Child(meta.DeclarativeBase):
id = Column(types.Integer, primary_key=True)

class Assoc(meta.DeclarativeBase):
p_id = Column(types.Integer,
  ForeignKey(Parent.id))
c_id = Column(types.Integer,
  ForeignKey(Parent.id))

parent = relation(Parent,
  backref=backref('children',
cascade='all, delete-orphan'))
child = relation(Child,
 backref='parents',
 cascade='delete-orphan')

I expect child = relation(Child, backref='parents', cascade='delete-
orphan') to forward deletes to child when it is an orphan. But it
looks like it forward the delete even if it is not an orphan yet...

It that configuration:

   p1 = Parent()
   p2 = Parent()
   c = Child()
   assoc1 = Assoc(parent=p1, child=c)
   assoc2 = Assoc(parent=p2, child=c)

p1.children = [ ] will lead to:
- delete assoc1 (ok)
- delete c (not ok)
- update assoc2.c_id = null (not ok)

So why is it not really a delete-orphan? :)

Thanks

GustaV
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] cascade defaults

[az]

hi
i have cascade= option of relation switched automaticaly between all 
and other values, and nothing. so i used cascade=None as nothing, 
hoping it will get the default behaviour. the result was some query 
got 6x slower (actualy thats why i went hunting it)...

nope, it happens that source-wise, exactly cascade=False triggers the 
default behavior (whatever that is) and nothing else. Not really 
intuituve, well, fine, i'll fix me code, but the question still 
remains: 
 why cascade=None or cascade='' or whatever empty thing makes queries 
(sqlite) sooo slow. 
i've compared the sql of the query - it's same. 
something in the schema went wrong? or result-processing? or what? is 
it worth chasing or there is a known reason?

and a related suggestion: why not use symbols e.g. some singleton 
called DefaultValue, instead of any hard-to-guess default values (be 
them False, None, '', whatever)? the actual default values are mostly 
set up later, so the 
  if something is DefaultValue: something = actual-default-value
is there anyway.

ciao
svilen

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] cascade='all, delete-orphan' causing error about unsaved, pending instances

[Andreas Jung]

I am building a media database using SA where the model basically maps

Medium --1:N-- Versions --1:N-- Files

My code for creating new Medium instances based on an import script 
basically is doing the following:


f = File(...)
v = Version()
v.files.append(f)
m = Medium()
m.versions.append(v)
session.save(m)

This works perfectly for the import however I have to deal with File orphans
(caused by some business logic of the media database).

To get rid of orphans I added relation(..., cascade='all, delete-orphan')
to the mapper definitions. However running the import with this change
causes the following error:


 File lib/python/mediendb/misc/import_medien.py, line 86, in 
import_medien

   TH(import_medium, fullname)
 File 
/local2/HRS2/HEAD.Zope28/HaufeCMS/Products/HaufeCMS/Transactions.py, line 
29, in __call__

   return TH.__call__(self, f, *args, **kw)
 File 
/local2/HRS2/HEAD.Zope28/HaufeCMS/lib/python/Haufe/Transactions/TransactionHandler.py, 
line 94, in __call__

   else: tmgr.get().commit() # old: transaction.get().commit()
 File 
/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py, 
line 390, in commit

   self._saveCommitishError() # This raises!
 File 
/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py, 
line 388, in commit

   self._commitResources()
 File 
/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py, 
line 433, in _commitResources

   rm.commit(self)
 File build/bdist.linux-x86_64/egg/z3c/sqlalchemy/base.py, line 151, in 
commit
 File build/bdist.linux-i686/egg/sqlalchemy/orm/session.py, line 302, in 
flush
 File build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py, line 200, 
in flush
 File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 290, in 
_is_orphan
FlushError: instance z3c.sqlalchemy.mapper._mapped_files object at 
0xb3610eac is an unsaved, pending instance and is an orphan (is not 
attached to any parent '_mapped_versions' instance via that classes' 
'files' attribute)


Why does this cascade rule causes this error?

Andreas

--
ZOPYX Ltd.  Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK

E-Publishing, Python, Zope  Plone development, Consulting


pgpJz2ilK4tra.pgp
Description: PGP signature

[sqlalchemy] Cascade-Delete causes AssertionError (Tries to blank-out primary key ...)

[Nebur]

The example below raises an:
sqlalchemy.exceptions.AssertionError: Dependency rule tried to blank-
out primary key column 'userdata.user_id' on instance
'[EMAIL PROTECTED]'

The code creates 2 objects having a 1:1 relation with cascade-delete.
The ForeignKey is declared as a primary key. This seems to cause the
Error.
Versions: Python 2.4, SA 0.3.1, SA 0.3.4


class User(object):
pass

class Userdata(object):
def __init__(self, user):
self.user_id = user.id

if __name__==__main__:
db = create_engine(mysql://[EMAIL PROTECTED]/test_cascade)
session = create_session()
metadata = BoundMetaData(db)

t_user = Table(user,metadata,
Column(id,Integer,primary_key=True),
)
t_userdata = Table(userdata,metadata,

Column(user_id,Integer,ForeignKey(user.id),primary_key=True),
)
metadata.create_all()
mapper(User, t_user)
mapper(Userdata, t_userdata, properties = {
myuser:relation(User,backref=backref(meta,cascade=delete))
})

# create 1 instance of each object:
user1 = User()
session.save(user1)
session.flush()
data1 = Userdata(user1)
session.save(data1)
session.flush()

# now delete the user,
# expecting the cascade to delete userdata,too:
session.delete(user1)
session.flush() #AssertionError: Dependency rule tried to blank-
out ...


I can workaround this error by using a separate primary key in table
userdata:
t_userdata = Table(userdata,metadata,
Column(id,Integer,primary_key=True),
Column(user_id,Integer,ForeignKey(user.id)),
)
and everything works fine.
I'm wondering whether this is an SA bug, or a bad table design ?
Thanks and regards,
 Ruben


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Cascade delete-orphan: child deleted althought still referenced ?

[Nebur]

I create a child table and a parent table. The latter holds a relation
to the child with delete-orphan cascade.
When I delete _the first_ of my 2 parents, the child is immediately
deleted, too.
***
This is my first attempt to use delete-orphan. I don't dare to report
it as a bug,
for the case I misunderstand the SA API.

The complete code:


from sqlalchemy import *

class Child(object):
def __init__(self, name):
self.childname = name
class Parent(object):
def __init__(self, name):
self.parentname = name

db = create_engine(mysql://[EMAIL PROTECTED]/test_cascade)
db.echo = True
session = create_session()

metadata = BoundMetaData(db)
t_parent = Table(parent,metadata,
Column(id,Integer,primary_key=True),
Column(parentname,String()),
Column(child_id,Integer,ForeignKey(child.id)),
mysql_engine=InnoDB,
)
t_child = Table(child,metadata,
Column(id,Integer,primary_key=True),
Column(childname,String()),
mysql_engine=InnoDB,
)
metadata.create_all()
mapper(Child, t_child)
mapper(Parent, t_parent, properties={
mychild:relation(Child,cascade=delete-orphan),
})

# create a child + 2 parents:
aChild = Child(aChild);session.save(aChild)
aParent1 = Parent(aParent1); aParent1.mychild=aChild;
session.save(aParent1)
aParent2 = Parent(aParent2); aParent2.mychild=aChild;
session.save(aParent2)
session.flush()

# it doesn't matter whether I create a new session here, or continue
with the old one

# delete first parent:
session.delete(aParent1)

# With InnoDB, the next flush raises Foreign Key constraint failure
# because aChild is deleted - while aParent2 ist still referencing it !
session.flush() #fails

# with MyISAM, I can still go ahead ...
print session.query(Child).get_by(childname=aChild) # the child is
deleted
# ... and try to delete second parent:
session.delete(aParent2) #
# next flush raises an error because aChild is not attached to session
anymore
session.flush()

It this a Bug, or did I misuse the ORM... ?
Regards
 Ruben

-
Versions: sqlalchemy 0.3.3 and 0.3.1 and MySQL5.


--~--~-~--~~~---~--~~
 You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---