I've worked your model into a complete test (see attached), it's
against Postgresql as you are using UUID specific functions. The
deletes at the end proceed successfully. Try out this test (warning!
it drops the tables first to start clean, run it on an empty
database!!) and try to see if you can figure out what's different in
your larger application.
On Fri, Sep 29, 2017 at 2:50 AM, Tolstov Sergey <whistler...@gmail.com> wrote:
> I try to delete data, but sqlalchemy tried to do it twice, and rollback
> Base Postgresql
> version 9.6
> sqlalchemy 1.0.14
> psycopg2 2.7.3.1
>
>
> class IdentifiedObject(Base):
> __tablename__ = 'identifiedobject'
> mRID = Column(UUID,
> server_default=sqlalchemy.text("uuid_generate_v4()"), primary_key=True)
> name = Column(String)
> polymorphic_type = Column(String, nullable=False)
>
> __mapper_args__={'polymorphic_identity':__tablename__,'polymorphic_on':polymorphic_type}
> assoc_1 = Table("assoc_1", Base.metadata,
> Column("cars_mRID", None, ForeignKey("cars.mRID")),
> Column("games_mRID", None, ForeignKey("games.mRID")))
> class Cars(IdentifiedObject):
> __tablename__='cars'
> mRID = Column(None, ForeignKey('identifiedobject.mRID'),
> primary_key=True)
> polymorphic_type = Column(String, nullable=False)
>
> __mapper_args__={'polymorphic_identity':__tablename__,'inherit_condition':
> mRID == IdentifiedObject.mRID,'polymorphic_on':polymorphic_type}
> status = Column(String)
> Games = relationship("Games", secondary = "assoc_1",
> back_populates="Cars", primaryjoin="(cars.c.mRID==assoc_1.c.cars_mRID)")
> class Games(IdentifiedObject):
> __tablename__='games'
> mRID = Column(None, ForeignKey('identifiedobject.mRID'),
> primary_key=True)
> polymorphic_type = Column(String, nullable=False)
>
> __mapper_args__={'polymorphic_identity':__tablename__,'inherit_condition':
> mRID == IdentifiedObject.mRID,'polymorphic_on':polymorphic_type}
> status = Column(String)
> Cars = relationship("Cars", secondary = "assoc_1",
> back_populates="Games", primaryjoin="(games.c.mRID==assoc_1.c.games_mRID)")
>
> Base.metadata.create_all(engine)
> Base.prepare(engine, reflect=True)
> session = Session(bind=engine)
> session.add(IdentifiedObject())
> games=Games(Cars=[Cars(),Cars()])
> session.add (games)
> session.commit()
> session.close()
> session.delete(games)
> session.commit()
>
> And now we found a exception
>
>
> 2017-09-29 09:17:44,996 INFO sqlalchemy.engine.base.Engine SELECT
> cars."mRID" AS "cars_mRID", identifiedobject."mRID" AS
> "identifiedobject_mRID", identifiedobject.name AS identifiedobject_name,
> cars.polymorphic_type AS cars_polymorphic_type,
> identifiedobject.polymorphic_type AS identifiedobject_polymorphic_type,
> cars.status AS cars_status
> FROM assoc_1, identifiedobject JOIN cars ON cars."mRID" =
> identifiedobject."mRID"
> WHERE %(param_1)s = assoc_1."games_mRID" AND cars."mRID" =
> assoc_1."cars_mRID"
> 2017-09-29 09:17:44,996 INFO sqlalchemy.engine.base.Engine {'param_1':
> UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a')}
> 2017-09-29 09:17:44,997 INFO sqlalchemy.engine.base.Engine DELETE FROM
> assoc_1 WHERE assoc_1."cars_mRID" = %(cars_mRID)s AND assoc_1."games_mRID" =
> %(games_mRID)s
> 2017-09-29 09:17:44,998 INFO sqlalchemy.engine.base.Engine
> ({'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID':
> UUID('a3135561-e416-45c0-b9f8-aead59ef6b34')}, {'games_mRID':
> UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID':
> UUID('b77b9dc4-65da-45ea-be52-dc53e2bcd74b')})
> 2017-09-29 09:17:44,998 INFO sqlalchemy.engine.base.Engine DELETE FROM
> assoc_1 WHERE assoc_1."cars_mRID" = %(cars_mRID)s AND assoc_1."games_mRID" =
> %(games_mRID)s
> 2017-09-29 09:17:44,999 INFO sqlalchemy.engine.base.Engine
> ({'games_mRID': UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID':
> UUID('a3135561-e416-45c0-b9f8-aead59ef6b34')}, {'games_mRID':
> UUID('7a960989-5e3e-45dc-87c1-1b62ffa3694a'), 'cars_mRID':
> UUID('b77b9dc4-65da-45ea-be52-dc53e2bcd74b')})
> 2017-09-29 09:17:44,999 INFO sqlalchemy.engine.base.Engine ROLLBACK
> Traceback (most recent call last):
> File "testing.py", line 98, in <module>
> session.commit()
>
> Exception data
>
>
> sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'assoc_1'
> expected to delete 2 row(s); Only 0 were matched.
>
> Haven't found on other answers
>
> --
> 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.
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import UUID
Base = declarative_base()
class IdentifiedObject(Base):
__tablename__ = 'identifiedobject'
mRID = Column(
UUID, server_default=sqlalchemy.text("uuid_generate_v4()"),
primary_key=True)
name = Column(String)
polymorphic_type = Column(String, nullable=False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'polymorphic_on': polymorphic_type}
assoc_1 = Table("assoc_1", Base.metadata,
Column("cars_mRID", None, ForeignKey("cars.mRID")),
Column("games_mRID", None, ForeignKey("games.mRID")))
class Cars(IdentifiedObject):
__tablename__ = 'cars'
mRID = Column(None, ForeignKey('identifiedobject.mRID'), primary_key=True)
polymorphic_type = Column(String, nullable=False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'inherit_condition': mRID == IdentifiedObject.mRID,
'polymorphic_on': polymorphic_type}
status = Column(String)
Games = relationship("Games", secondary="assoc_1",
back_populates="Cars",
primaryjoin="(cars.c.mRID==assoc_1.c.cars_mRID)")
class Games(IdentifiedObject):
__tablename__ = 'games'
mRID = Column(None, ForeignKey('identifiedobject.mRID'), primary_key=True)
polymorphic_type = Column(String, nullable=False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'inherit_condition': mRID == IdentifiedObject.mRID,
'polymorphic_on': polymorphic_type}
status = Column(String)
Cars = relationship("Cars", secondary="assoc_1",
back_populates="Games",
primaryjoin="(games.c.mRID==assoc_1.c.games_mRID)")
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e)
session = Session(bind=e)
session.add(IdentifiedObject())
games = Games(Cars=[Cars(), Cars()])
session.add(games)
session.commit()
session.close()
session.delete(games)
session.commit()
