SQLAlchemy delete many to many row

Question

Im using a postgresql database with sqlalchemy(new to that) and python.I have two tables that have a relation over a many to many table...

I want to remove the relation between bot and a wallet/whale. If it possible also the whale if there is no other bot left in the relationship table.

Here the models:

WhaleBot = Table('whale_bot', Base.metadata,
    Column('id', Integer, primary_key=True, nullable=False),
    Column('whale_id', Integer, ForeignKey('whales.id'), nullable=False),
    Column('bot_id', Integer, ForeignKey('bots.id'), nullable=False),
    Column('created_at', DateTime(timezone=True), server_default=func.now(), nullable=False),
    Column('updated_at', DateTime(timezone=True), default=func.now(), onupdate=func.now(), nullable=False),
    UniqueConstraint('whale_id', 'bot_id', name='uix_1')

class Whale(Base):
    __tablename__ = 'whales'

    id = Column(Integer, primary_key=True, nullable=False)
    wallet = Column(String, unique=True, nullable=False)
    is_contract = Column(Boolean, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=func.now(), onupdate=func.now(), nullable=False)

    bots = relationship('Bot', secondary=WhaleBot, cascade="all, delete", backref='Whale')

class Bot(Base):
    __tablename__ = 'bots'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=func.now(), onupdate=func.now(), nullable=False)

    whales = relationship('Whale', secondary=WhaleBot, cascade="all, delete", backref='Bot')
)

Here the code what I try so far:

def delete_wallet(self, wallet, botId):
        try:
            walletId = self.dbEngine.session.query(Whale.id).filter(Whale.wallet == wallet).scalar()
            toDelete =self.dbEngine.session.query(WhaleBot).filter(WhaleBot.c.bot_id == botId).filter(WhaleBot.c.whale_id == walletId).first()
            print(toDelete.id)
            #WhaleBot.query().filter(id == toDelete.id).delete()
            #self.dbEngine.session.query(WhaleBot).query.filter(id == toDelete.id).delete()
            #self.dbEngine.session.query(WhaleBot).filter_by(id == toDelete.id).delete()
            # toDelete = self.dbEngine.session.query(Whale).filter(Whale.wallet == wallet).scalar()
            #self.dbEngine.session.delete(WhaleBot).filter(WhaleBot.c.id == toDelete)
            obj = self.dbEngine.WhaleBot.filter_by(id == toDelete.id).one()
            #obj = User.query.filter_by(id=123).one()
            self.dbEngine.session.delete(obj)

            self.dbEngine.session.commit()
            return True, "Deleted"
        except Exception as e:
            exc_type, exc_obj, exc_tb = sys.exc_info()
            fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
            with open("errorlog.txt",'a') as f:
                f.write("delete error: "+dt.now().strftime('%Y-%m-%d %H:%M:%S')+" "+str(e)+ "\n"+
                str(fname)+" Line: "+str(exc_tb.tb_lineno)+ "\n")
            return False, "Something wrong"

I let the comments in so you can see I tried a lot ...

Here some cases that Itried with the errors that occures ... maybe you can tell me where I was wrong, so that I understand sqlalchemy - but also just a solution would be nice :) Case 1

walletId = self.dbEngine.session.query(Whale.id).filter(Whale.wallet == wallet).scalar()
toDelete =self.dbEngine.session.query(WhaleBot).filter(WhaleBot.c.bot_id == botId).filter(WhaleBot.c.whale_id == walletId).first()
obj = self.dbEngine.WhaleBot.filter_by(id == toDelete.id).one()
self.dbEngine.session.delete(obj)
            

Error Case 1:

'DBEngine' object has no attribute 'WhaleBot'

Case 2 (found at the internet):

walletId = self.dbEngine.session.query(Whale.id).filter(Whale.wallet == wallet).scalar()
toDelete =self.dbEngine.session.query(WhaleBot).filter(WhaleBot.c.bot_id ==botId).filter(WhaleBot.c.whale_id == walletId).first()
self.dbEngine.session.query(WhaleBot).query.filter(id == toDelete.id).delete()

Error Case 2:

'Query' object has no attribute 'query'

Case 3:

walletId = self.dbEngine.session.query(Whale.id).filter(Whale.wallet == wallet).scalar()
toDelete =self.dbEngine.session.query(WhaleBot).filter(WhaleBot.c.bot_id ==botId).filter(WhaleBot.c.whale_id == walletId).first()
self.dbEngine.session.query(WhaleBot).filter_by(WhaleBot.c.id == toDelete.id).delete()

Error Case 3:

'NoneType' object has no attribute 'class_'

The point is also like I said I'm new to SQLalchemy and don't know where to search (my code /declerations or the way I call the functions ...)

Yep and many other ways i tried... but yeah enough exceptions xD

Answer 1

You can remove the relationship like this:

whale = session.query(Whale).filter(Whale.wallet == wallet).one()
bot = session.query(Bot).get(botId)
whale.bots.remove(bot)
session.commit()

or the other way around:

whale = session.query(Whale).filter(Whale.wallet == wallet).one()
bot = session.query(Bot).get(botId)
whale.bots.remove(bot)
session.commit()

See the docs on removing many-to-may relationships here.