date:20170929

Re: [sqlalchemy] SQLAlchemy many-to-many Postgresql double delete

2017-09-29 Thread Mike Bayer

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  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 
> 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] SQLAlchemy many-to-many Postgresql double delete

2017-09-29 Thread Tolstov Sergey

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 
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.

Re: [sqlalchemy] SQLAlchemy many-to-many Postgresql double delete

[sqlalchemy] SQLAlchemy many-to-many Postgresql double delete

2 matches

Site Navigation

Mail list logo

Footer information