how can i query a relational database using sqlalchemy in python

Question

I have the following data base:

enter image description here

permission_id and contract_id have a relationship in the database

How can i using python remove the entry for example.. if permission_id = 2 and contract_id = 2 exists in the same entry as shown on line one in database, i want to be able to remove it from my database.

I have tried PermissionEntity.query.get(contract_id) and PermissionEntity.query.get(permission_id) but doesnt seem to be working

please help

Correction to initial question:

could you let me know how i would remove this relationship if it was to be stored somewhere else?... at the moment I've realised its not stored in permission entity its a seperare table which has a relationship with contracts from a contract table and permisions from permission table (two separate tables).. in other words , access the relationship table and remove as I do not have an entity for it

• T Hus: In short I am looking to find out how can I find if a set of entries exist. so if permission_id and contract id is the same as what i send from my front end and if so , remove it from database

Answer 1

Assuming you're instantiating SQLAlchemy as db then this will work:

result = db.session.query(
 PermissionEntity
 ).filter(and_([PermissionEntity.contract_id==2, PermissionEntity.permission_id==2]
 ).all()
"""
result is a list object due to all()...if you only want to remove the
first instance, then use first() and you do not need to iterate through a
list like below.
"""
for i in result:
 i.remove()
db.session.commit()

• T Hus: This instance can only occur once so would I need to use for i in result , as the relationship is unique and cannot appear twice in the database
• RyanH: use first() then. In SQLAlchemy, all() will always return a list object even if there's no result.
• T Hus: great thanks so much dude!, so if my table has contract id =2 in a different field for example entry 2 in database and permission id = 2 in the first entry .. the above will bring up no results right as its no in the same entry
• RyanH: Correct, the row has to meet both criteria. Please mark this as an answer if it helps. Thanks!
• RyanH: @THus don't forget to accept this as the answer if it helped. Thanks