Reputation: 391
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
Upvotes: 0
Views: 93
Reputation: 55933
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.
Upvotes: 1