[sqlalchemy] Re: Cascade Deletes
Il 09/08/11 02.42, Aviv Giladi ha scritto:
Dear Stefano,
First of all, thank you. Allow me to express how much I appreciate
your time and assistance - you and your colleagues are making the
technology world this much better by being so attentive and helpful!
Thank you.
Therefore I chose the second option - adding the cascade="all,
delete-orphan" to the backref call. This definitely got rid of the
problem, however, now the cascade isn't working - when I delete a
Rating object, its ProductsRating and the according ProductsProperty
persist. It is all illustrated in the attached script.
It is the right behaviour, for 'cascade' setting not for your app :)
The 'cascade' is setted up on the OneToMany side, in ProductsRating.
In your case, you have:
Rating -< ProductsRating
You can cascade from ProductsRating to Rating (symbol '<' identify
'Many' side) not the contrary, unless you use 'single_parent=True'.
It is the same behaviour of ONUPDATE and ONDELETE of SQL, the difference
is that in SQL you put the setting in FK ('One' side of relationship),
in SQLA you put the setting in 'Many' side of relationship. In both case
you can propagate updates and deletes from 'Many' side to 'One' side of
relationships.
Regards,
Stefano.
--
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] Re: Cascade Deletes
Il 08/08/11 22.53, Aviv Giladi ha scritto:
Dear Stefano,
I have been able to create a reproducing project. I am sending it to
you directly because I was unable to figure out how to attach the test
file to the message board. I will post our findings there once we are
done.
The included script includes all my models. I have 1 rating object,
called Rating, 2 subratings - ProductsRating and
CustomerServiceRating, and a subrating property - ProductsProperty,
that is assigned to ProductsRating.
When you run the code I am attaching as is, you will encounter my
issue (InterfaceError exception).
However, the odd thing is that the following 2 adjustments to the
script fix the problem:
1) deleting the three lines 126, 127 and 128 causes the script to work
just fine
2) instead of querying the session for the ProductsProperty, creating
it with its c'tor makes the script works as well.
I am extremely confused by this. Any help you could give me would be
beyond appreciated.
Thank you so much,
Aviv
Hi Aviv.
You made a mistake using 'cascade' and your code produces 2 warnings:
SAWarning: On Rating.products_rating, delete-orphan cascade is not
supported on a many-to-many or many-to-one relationship when
single_parent is not set. Set single_parent=True on the relationship().
SAWarning: On Rating.customer_service_rating, delete-orphan cascade is
not supported on a many-to-many or many-to-one relationship when
single_parent is not set. Set single_parent=True on the relationship().
The problem is 'cascade' setting in the following relationships:
products_rating = relationship("ProductsRating", cascade="all,
delete-orphan", backref=backref("rating", uselist=False))
customer_service_rating = relationship("CustomerServiceRating",
cascade="all, delete-orphan", backref=backref("rating", uselist=False))
you cannot set 'cascade' in ManyToMany and ManyToOne relationships, to
do that you must specify 'single_parent=True'.
Check the docs to understand the usage of 'single_parent'.
Otherwise use 'cascade' as follow:
products_rating = relationship("ProductsRating",
backref=backref("rating", cascade="all, delete-orphan", uselist=False))
customer_service_rating = relationship("CustomerServiceRating",
backref=backref("rating", cascade="all, delete-orphan", uselist=False))
I don't know the meaning of you model for that reason you must choose
the right solution.
I attached your script. I fixed it and SQLA doesn't give me any
InterfaceError.
HINT: when you use the declarative SQLA you don't need to define
__init__ function in your entities. The default __init__ is often enough.
It is needed when you want a custom behaviour.
Regards,
Stefano.
PS: you are using Pyramid! Great project!
PS2: I send this message in ML to help other users in the future.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
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.
import transaction
from sqlalchemy import create_engine
from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
import datetime
import random
import string
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
#describes a business
class Business(Base):
__tablename__ = 'tbl_businesses'
id = Column(Integer, primary_key=True)
short_name = Column(Unicode(255), unique=True)
name = Column(Unicode(255))
city = Column(Unicode(64))
state = Column(Unicode(8))
ratings = relationship("Rating", backref="business")
def __init__(self, short_name, name, city, state):
self.short_name = short_name
self.name = name
self.city = city
self.state = state
#desribes a user in the system
class User(Base):
__tablename__ = 'tbl_users'
id = Column(Integer, primary_key=True)
first_name = Column(Unicode(32))
last_name = Column(Unicode(32))
email = Column(Unicode(64), unique=True)
gender = Column(Boolean)
is_valid = Column(Boolean)
created = Column(DateTime, default=datetime.datetime.utcnow)
ratings = relationship("Rating", backref="user")
def __init__(self, first_name, last_name, email, gender):
self.first_name = first_name
self.last_name = last_name
self.email = email
if gender == "male":
self.gender = True
else:
self.gender = False
self.is_valid = False
#describes a ty
[sqlalchemy] Re: Cascade Deletes
Stefano, Thank you again for your time. I am sorry for not posting my actual code - it is sensitive, therefore I am trying to reproduce the same issue with different code. In my __init__ function of my Rating I am setting 3 members that are objects like so: self.member = member These members are objects that have a relationship with Rating. When I remove these assignments, everything works fine. Does this ring any bells? On Aug 7, 1:15 pm, Stefano Fontanelli wrote: > Il 07/08/11 20.08, Aviv Giladi ha scritto: > > > I see. > > > Where can I start looking for such an error? What could possibly be > > causing this? > > I'm sorry but I cannot help you without your code... not a similar code > but THE code which produce that error. > > Regards, > Stefano. > > -- > Ing. Stefano Fontanelli > Asidev S.r.l. > Via Osteria Bianca, 108/A 50053 Empoli (Firenze) > Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 > E-mail: s.fontane...@asidev.com Web:www.asidev.com > Skype: stefanofontanelli -- 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] Re: Cascade Deletes
Il 07/08/11 20.08, Aviv Giladi ha scritto: I see. Where can I start looking for such an error? What could possibly be causing this? I'm sorry but I cannot help you without your code... not a similar code but THE code which produce that error. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web: www.asidev.com Skype: stefanofontanelli -- 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] Re: Cascade Deletes
I see. Where can I start looking for such an error? What could possibly be causing this? On Aug 7, 6:51 am, Stefano Fontanelli wrote: > Il 07/08/11 00.35, Aviv Giladi ha scritto: > > > Everything works great when I create and assign all 3 subratings to > > the rating object before I add it to the session. > > However, I need to be able to create a Rating that only has 1 or 2 > > subratings, and the other subratings absent. > > When I do that, SQLAlchemy tells me: > > InterfaceError: (InterfaceError) Error binding parameter 0 - probably > > unsupported type. u'SELECT SubRating2.id AS subrating2_id \nFROM > > subratings2 \nWHERE subrating2.id = ?' (,) > > > The above error is when I set Ratings's subrating1 and subrating3, but > > not subrating2. > > How do I avoid this error? > > I think the error is somewhere else in your application code. > The model works as you can check in the attached test. > > Regards, > Stefano. > > -- > Ing. Stefano Fontanelli > Asidev S.r.l. > Via Osteria Bianca, 108/A 50053 Empoli (Firenze) > Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 > E-mail: s.fontane...@asidev.com Web:www.asidev.com > Skype: stefanofontanelli > > test.py > 7KViewDownload -- 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] Re: Cascade Deletes
Il 07/08/11 00.35, Aviv Giladi ha scritto:
Everything works great when I create and assign all 3 subratings to
the rating object before I add it to the session.
However, I need to be able to create a Rating that only has 1 or 2
subratings, and the other subratings absent.
When I do that, SQLAlchemy tells me:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'SELECT SubRating2.id AS subrating2_id \nFROM
subratings2 \nWHERE subrating2.id = ?' (,)
The above error is when I set Ratings's subrating1 and subrating3, but
not subrating2.
How do I avoid this error?
I think the error is somewhere else in your application code.
The model works as you can check in the attached test.
Regards,
Stefano.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
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.
from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class SubRating1(Base):
__tablename__ = 'subratings1'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating2(Base):
__tablename__ = 'subratings2'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating3(Base):
__tablename__ = 'subratings3'
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)
subrating1_id = Column(Integer, ForeignKey('subratings1.id'))
subrating1 = relationship("SubRating1",
backref=backref("rating",
cascade="all, delete-orphan",
uselist=False))
subrating2_id = Column(Integer, ForeignKey('subratings2.id'))
subrating2 = relationship("SubRating2",
backref=backref("rating",
cascade="all, delete-orphan",
uselist=False))
subrating3_id = Column(Integer, ForeignKey('subratings3.id'))
subrating3 = relationship("SubRating3",
backref=backref("rating",
cascade="all, delete-orphan",
uselist=False))
if __name__ == '__main__':
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = scoped_session(sessionmaker())
session.configure(bind=engine)
rating = Rating(name=u'My First Rating')
session.add(rating)
session.flush()
assert rating.subrating1 == None
assert rating.subrating2 == None
assert rating.subrating3 == None
session.commit()
subrating1 = SubRating1(name=u'My First SubRating1')
rating.subrating1 = subrating1
assert rating.subrating1 == subrating1
assert rating.subrating2 == None
assert rating.subrating3 == None
assert subrating1.rating == rating
session.flush()
session.commit()
subrating3 = SubRating3(name=u'My First SubRating3')
rating.subrating3 = subrating3
assert rating.subrating1 == subrating1
assert subrating1.rating == rating
assert rating.subrating2 == None
assert rating.subrating3 == subrating3
assert subrating3.rating == rating
session.flush()
session.commit()
"""
$ python test.py
2011-08-07 15:48:54,697 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subratings3")
2011-08-07 15:48:54,697 INFO sqlalchemy.engine.base.Engine ()
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subratings2")
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine ()
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subratings1")
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine ()
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("ratings")
2011-08-07 15:48:54,698 INFO sqlalchemy.engine.base.Engine ()
2011-08-07 15:48:54,699 INFO sqlalchemy.engine.base.Engine
CREATE TABLE subratings3 (
id INTEGER NOT NULL,
name VARCHAR(32),
PRIMARY KEY (id),
UNIQUE (name)
)
2011-08-07 15:48:
[sqlalchemy] Re: Cascade Deletes
Stefano,
Thanks! Your script helped me narrow down the problem.
My Rating object has multiple Subrating objects. So in my real code, I
have something like:
class SubRating1(Base):
__tablename__ = 'subratings1'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating2(Base):
__tablename__ = 'subratings2'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating3(Base):
__tablename__ = 'subratings3'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
And then my Rating looks like:
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating1_id = Column(Integer, ForeignKey('subratings1.id'))
subrating1 = relationship("SubRating1", backref=backref("rating",
cascade="all, delete-orphan", uselist=False))
subrating2_id = Column(Integer, ForeignKey('subratings2.id'))
subrating2 = relationship("SubRating2", backref=backref("rating",
cascade="all, delete-orphan", uselist=False))
subrating3_id = Column(Integer, ForeignKey('subratings3.id'))
subrating3 = relationship("SubRating3", backref=backref("rating",
cascade="all, delete-orphan", uselist=False))
Everything works great when I create and assign all 3 subratings to
the rating object before I add it to the session.
However, I need to be able to create a Rating that only has 1 or 2
subratings, and the other subratings absent.
When I do that, SQLAlchemy tells me:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'SELECT SubRating2.id AS subrating2_id \nFROM
subratings2 \nWHERE subrating2.id = ?' (,)
The above error is when I set Ratings's subrating1 and subrating3, but
not subrating2.
How do I avoid this error?
On Aug 6, 6:16 am, Stefano Fontanelli wrote:
> Il 06/08/11 00.32, Aviv Giladi ha scritto:
>
> > Hi Stefano,
> > I create and add a Rating and Subrating (both end up in the DB no
> > problem).
> > Then, I call session.delete(rating_obj) and commit it. I look at the
> > DB, and the Rating is gone, but the SubRating is still there.
> > The DB shows that the Rating has the correct Subrating's ID..
>
> Hi Aviv,
> I attached the code you sent me.
>
> I move 'cascade' as I told you and everything works. See the log that I
> pasted at the bottom of the script.
>
> --
> Ing. Stefano Fontanelli
> Asidev S.r.l.
> Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
> Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
> E-mail: s.fontane...@asidev.com Web:www.asidev.com
> Skype: stefanofontanelli
>
> test.py
> 7KViewDownload
--
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] Re: Cascade Deletes
Il 06/08/11 00.32, Aviv Giladi ha scritto:
Hi Stefano,
I create and add a Rating and Subrating (both end up in the DB no
problem).
Then, I call session.delete(rating_obj) and commit it. I look at the
DB, and the Rating is gone, but the SubRating is still there.
The DB shows that the Rating has the correct Subrating's ID..
Hi Aviv,
I attached the code you sent me.
I move 'cascade' as I told you and everything works. See the log that I
pasted at the bottom of the script.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
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.
from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
subrating_subratingproperty = 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,
backref="subrating_properties")
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",
cascade="all, delete-orphan",
backref=backref("rating", uselist=False))
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
if __name__ == '__main__':
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = scoped_session(sessionmaker())
session.configure(bind=engine)
subrating = SubRating(name=u'My First Subrating')
rating = Rating(name=u'My First Rating', subrating=subrating)
session.add(rating)
session.flush()
assert rating.subrating != None
assert subrating.rating != None
session.commit()
assert rating.subrating == subrating
assert subrating.rating == rating
rating = session.query(Rating).first()
session.delete(rating)
session.flush()
session.commit()
assert session.query(Rating).all() == []
assert session.query(SubRating).all() == []
"""
$ python test.py
2011-08-06 12:13:02,959 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subratings")
2011-08-06 12:13:02,959 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subrating_properties")
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("ratings")
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("subrating_subratingproperty_association")
2011-08-06 12:13:02,960 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,961 INFO sqlalchemy.engine.base.Engine
CREATE TABLE subratings (
id INTEGER NOT NULL,
name VARCHAR(32),
PRIMARY KEY (id),
UNIQUE (name)
)
2011-08-06 12:13:02,961 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,961 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-06 12:13:02,962 INFO sqlalchemy.engine.base.Engine
CREATE TABLE subrating_properties (
id INTEGER NOT NULL,
name VARCHAR(32),
PRIMARY KEY (id),
UNIQUE (name)
)
2011-08-06 12:13:02,962 INFO sqlalchemy.engine.base.Engine ()
2011-08-06 12:13:02,962 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-06 12:13:02,962 INFO sqlalchemy.engine.base.Engine
CREATE TABLE ratings (
id INTEGER NOT NULL,
name VARCHAR(32),
subrating_id INTEGER,
PRIMARY KEY (id),
UNIQUE (name),
FOREIG
[sqlalchemy] Re: Cascade Deletes
Hi Stefano,
Thanks! The code is just like 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",
cascade="all, delete-orphan", uselist=False))
I create and add a Rating and Subrating (both end up in the DB no
problem).
Then, I call session.delete(rating_obj) and commit it. I look at the
DB, and the Rating is gone, but the SubRating is still there.
The DB shows that the Rating has the correct Subrating's ID..
On Aug 5, 11:45 am, Stefano Fontanelli
wrote:
> Il 05/08/11 20.38, Aviv Giladi ha scritto:
>
> > Hey Stefano,
>
> > I tried that, but when I did, this is the error I got while inserting
> > a new rating:
>
> > InterfaceError: (InterfaceError) Error binding parameter 0 - probably
> > unsupported type. u'SELECT subratings.id AS subratings_id \nFROM
> > subratings \nWHERE subratings.id = ?' (,)
>
> I need the whole code to help you :)
> I think it is not related with cascade set.
>
> Regards,
> Stefano.
>
> --
> Ing. Stefano Fontanelli
> Asidev S.r.l.
> Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
> Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
> E-mail: s.fontane...@asidev.com Web:www.asidev.com
> Skype: stefanofontanelli
--
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] Re: Cascade Deletes
Il 05/08/11 20.38, Aviv Giladi ha scritto: Hey Stefano, I tried that, but when I did, this is the error I got while inserting a new rating: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT subratings.id AS subratings_id \nFROM subratings \nWHERE subratings.id = ?' (,) I need the whole code to help you :) I think it is not related with cascade set. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web: www.asidev.com Skype: stefanofontanelli -- 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] Re: Cascade Deletes
Hey Stefano,
I tried that, but when I did, this is the error I got while inserting
a new rating:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'SELECT subratings.id AS subratings_id \nFROM
subratings \nWHERE subratings.id = ?' (,)
On Aug 5, 9:46 am, Stefano Fontanelli wrote:
> Il 04/08/11 21.27, Aviv Giladi ha scritto:
>
> > Hey,
>
> > Tried adding cascade to Rating's backref call like so:
>
> > subrating = relationship("SubRating", backref=backref("rating",
> > cascade="all, delete-orphan"
> > uselist=False))
>
> > This unfortunately doesn't work - when I delete a Rating, the
> > according Subratings are NOT removed.
> > What am I doing wrong? (Testing with SQLite)
>
> Are you sure about the position of 'cascade' keyword?
> I think the right way to do that could be:
>
> subrating = relationship("SubRating",
> cascade="all, delete-orphan",
> backref=backref("rating", uselist=False))
>
> Regards,
> Stefano.
--
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] Re: Cascade Deletes
Il 04/08/11 21.27, Aviv Giladi ha scritto:
Hey,
Tried adding cascade to Rating's backref call like so:
subrating = relationship("SubRating", backref=backref("rating",
cascade="all, delete-orphan"
uselist=False))
This unfortunately doesn't work - when I delete a Rating, the
according Subratings are NOT removed.
What am I doing wrong? (Testing with SQLite)
Are you sure about the position of 'cascade' keyword?
I think the right way to do that could be:
subrating = relationship("SubRating",
cascade="all, delete-orphan",
backref=backref("rating", uselist=False))
Regards,
Stefano.
--
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] Re: Cascade Deletes
Hey,
Tried adding cascade to Rating's backref call like so:
subrating = relationship("SubRating", backref=backref("rating",
cascade="all, delete-orphan"
uselist=False))
This unfortunately doesn't work - when I delete a Rating, the
according Subratings are NOT removed.
What am I doing wrong? (Testing with SQLite)
On Jul 31, 9:01 am, Stefano Fontanelli
wrote:
> Il 30/07/11 23.24, Aviv Giladi ha scritto:
>
> > Sorry, but I am really confused.
> > Are you guys saying that on SQLite for example, cascade deletes don't
> > work at all? Or do they work, but are less efficient?
>
> ONUPDATE/ONDELETE cascade -> SQL expression: on SQLite and MySQL MyISAM
> doesn't work at all.
>
> Michael told you that you can reach the same result using the SQLA's
> relationship option:
>
> cascade='all, delete-orphan'
>
> This approach is less efficient because delete actions are performed by
> mapper at the application layer instead of the database: relationship
> collection objects must be loaded into memory then deleted.
>
> Regards,
> Stefano.
>
> --
> Ing. Stefano Fontanelli
> Asidev S.r.l.
> Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
> Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
> E-mail: s.fontane...@asidev.com Web:www.asidev.com
> Skype: stefanofontanelli
--
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] Re: Cascade Deletes
Il 30/07/11 23.24, Aviv Giladi ha scritto: Sorry, but I am really confused. Are you guys saying that on SQLite for example, cascade deletes don't work at all? Or do they work, but are less efficient? ONUPDATE/ONDELETE cascade -> SQL expression: on SQLite and MySQL MyISAM doesn't work at all. Michael told you that you can reach the same result using the SQLA's relationship option: cascade='all, delete-orphan' This approach is less efficient because delete actions are performed by mapper at the application layer instead of the database: relationship collection objects must be loaded into memory then deleted. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web: www.asidev.com Skype: stefanofontanelli -- 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] Re: Cascade Deletes
Sorry, but I am really confused. Are you guys saying that on SQLite for example, cascade deletes don't work at all? Or do they work, but are less efficient? Thanks again! On Jul 30, 11:08 am, Michael Bayer wrote: > SQLAlchemy's "cascade='all, delete-orphan'" implements the same CASCADE > functionality as ONDELETE does, in Python. It is just less efficient since > collections need to be fully loaded into memory for them to be processed. > > On Jul 30, 2011, at 1:49 PM, Aviv Giladi wrote: > > > > > > > > > Thank you for your response. > > > In that case, how do you manage these kinds of situations in SQLite > > and other engines in MySQL? > > Do you manually delete the children as well? > > > On Jul 28, 10:35 am, Stefano Fontanelli > > wrote: > >> Il 28/07/11 01.15, Aviv Giladi ha scritto: > > >>> Hi, > > >>> I am actually using both MySQL and SQLite (one on the dev machine, one > >>> on the server). > >>> Does that make a difference? > > >> ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. > >> You must change your database to test them. > >> In MySQL you can create your database and tables as InnoDB. > > >> Regards, > >> Stefano. > > > -- > > 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 > > athttp://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] Re: Cascade Deletes
SQLAlchemy's "cascade='all, delete-orphan'" implements the same CASCADE functionality as ONDELETE does, in Python. It is just less efficient since collections need to be fully loaded into memory for them to be processed. On Jul 30, 2011, at 1:49 PM, Aviv Giladi wrote: > Thank you for your response. > > In that case, how do you manage these kinds of situations in SQLite > and other engines in MySQL? > Do you manually delete the children as well? > > On Jul 28, 10:35 am, Stefano Fontanelli > wrote: >> Il 28/07/11 01.15, Aviv Giladi ha scritto: >> >>> Hi, >> >>> I am actually using both MySQL and SQLite (one on the dev machine, one >>> on the server). >>> Does that make a difference? >> >> ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. >> You must change your database to test them. >> In MySQL you can create your database and tables as InnoDB. >> >> Regards, >> Stefano. > > -- > 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.
[sqlalchemy] Re: Cascade Deletes
Thank you for your response. In that case, how do you manage these kinds of situations in SQLite and other engines in MySQL? Do you manually delete the children as well? On Jul 28, 10:35 am, Stefano Fontanelli wrote: > Il 28/07/11 01.15, Aviv Giladi ha scritto: > > > Hi, > > > I am actually using both MySQL and SQLite (one on the dev machine, one > > on the server). > > Does that make a difference? > > ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. > You must change your database to test them. > In MySQL you can create your database and tables as InnoDB. > > Regards, > Stefano. -- 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] Re: Cascade Deletes
Il 28/07/11 01.15, Aviv Giladi ha scritto: Hi, I am actually using both MySQL and SQLite (one on the dev machine, one on the server). Does that make a difference? ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. You must change your database to test them. In MySQL you can create your database and tables as InnoDB. Regards, Stefano. -- 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] Re: Cascade Deletes
Hi,
I am actually using both MySQL and SQLite (one on the dev machine, one
on the server).
Does that make a difference?
On Jul 27, 12:26 pm, Michael Bayer wrote:
> 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.
