CODEWITHSUNDEEP
pythonsqlalchemymany-to-many
Alberto Megía
Alberto Megía

Reputation: 2255

Avoid retrieving object to delete in many to many rel SQLAlchemy

Is there a way to avoid retrieving an object to delete in many-to-many relationship?

assign = Table('manytomany', Base.metadata,
    Column('pid', String(...), ForeignKey('parent.pid')),
    Column('cid', String(...), ForeignKey('child.cid'))
)

class Parent():
    ....
    childs = relationship("Child", secondary = assign, backref = 'parent')

I know I can do this:

obj = session.query(Table).get(pk_id)
session.delete(obj)

But I would like have only one database access, something like:

session.query(Table).filter_by(id = pk_id).delete()

I got an error because of the many-to-many rel:

'Cannot delete or update a parent row: a foreign key constraint fails...'

Is it possible? Thx a lot

Upvotes: 4

Views: 947

Answers (1)

dieterg
dieterg

Reputation: 123

Using session.query() will always retrieve the object's data from the database first. To avoid that you will have to use the table object associated with your ORM object directly.

session.execute(Parent.__table__.delete().where(Parent.id==pk_id))

This will issue a single DELETE sql statement to the database removing the parent record. (Parent.id is a synonym for Parent.__table__.c.id)

To resolve the foreign key error you will have to delete the records in the assign table first.

session.execute(assign.delete().where(assign.c.pid==pk_id))

Upvotes: 2

Related Questions