how can i remove an entry from relational database using sqlalchemy in python

[Tanjil Hussain]

permission_id and contract_id have a relationship in the database

How can i using remove a 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. (This entry is unique so can only appear once)

I have tried PermissionEntity.query.get(contract_id) and PermissionEntity.query.get(permission_id) but doesnt seem to be working as Its not stored in a permission entity.. My relationship does not have an entity. the table i have provided a picture for has a relationship with permissions table and contracts table..

[Jonathan Vanasco]

`.get()` returns the corresponding row/object based on the primary key https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=get#sqlalchemy.orm.query.Query.get

assuming `PermissionEntity` has a primary key of (permission_id, contact_id), the syntax from the examples would be:

some_object = session.query(VersionedFoo).get((5, 10))

or

my_object = query.get((5, 10))

If you have another primary key, you'd have to filter:

some_object = session.query(PermissionEntity).filter(PermissionEntity.permission_id==2, PermissionEntity.contract_id==2).first()

or

some_object = session.query(PermissionEntity).filter_by(permission_id==2, contract_id==2).first()

[Tanjil Hussain]

Thanks for the response...

I have one table for permissions which has 'PermissionEntity' and another table  for contract which has 'ContractsEntity' .. the table I am currently trying to remove my entry from is another table called permission_contract which is shown below:

this table does not have any entities but has foreign keys which gets data from the two table mentioned above...
What I am trying to do is send a query to this table and delete an entry if it exists and i am doing this by the following:

    result = db.session.query(PermissionEntity).filter(and_([PermissionEntity.contract_id == contract_id, PermissionEntity.permission_id == permission_id]).first()

    for i in result:

    i.remove()

    db.session.commit()

Of course this will not work as permission entity has no relationship with permission_contract..

please advice best approach as im new to this?

thanks

[Jonathan Vanasco]

can you share your schema for these 3 tables?

[Tanjil Hussain]

permission_contract:

permissions: 

contracts:

[Jonathan Vanasco]

Sorry, I meant the SqlAlchemy schema.  I can't attempt to troubleshoot code that I can't see.

[Tanjil Hussain]

Hey, what do you mean by this exactly.. sorry im abit confused and new to this..

[Tanjil Hussain]

Could you please give me an example. sorry im abit confused and new to this..

[Tanjil Hussain]

This is my function but it does not do anything at the moment :

def delete_permission_by_contract_id(contract_id, permission_id):  # noqa: E501

    """Deletes permissions by contract ID

    Returns all permissions related to a specific contract. # noqa: E501

    :param contract_id: ID of contract to retreive permissions.

    :type contract_id: int

    :param permission_id: ID of permission to remove.

    :type permission_id: int

    :rtype: List[Permission]

    """

    # result = db.session.query(PermissionEntity).filter(and_([PermissionEntity.contract_id == contract_id, PermissionEntity.permission_id == permission_id]).first()

    # for i in result:

    # i.remove()

    # db.session.commit()

    # contract = ContractEntity.query.get(contract_id)

    # if contract:

    #     permissions = [

    #         Permission.from_dict(contract.__dict__)

    #         for contract in contract.permissions

    #     ]

    #     permission = PermissionEntity.query.get(permission_id)

    #     if vendors:

    #         return vendors

    #     else:

    #         return {"message": "Contract has no vendors"}, 404

    # else:

    #     return {"message": "Contract id couldn't be found"}, 404

    pass

[Jonathan Vanasco]

What is the code for PermissionEntity, ContractEntity, and the joining table?

it will look like this https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-many

[Tanjil Hussain]

contract entity:

from api.extensions import db

from sqlalchemy.sql import expression

contracts_users = db.Table("contracts_users",

                           db.Column("contract_id", db.Integer, db.ForeignKey(

                               "contracts.id"), primary_key=True),

                           db.Column("user_uin", db.Integer, db.ForeignKey(

                               "users.uin"), primary_key=True)

                           )

contracts_vendors = db.Table("contracts_vendors",

                             db.Column("contract_id", db.Integer, db.ForeignKey(

                                 "contracts.id"), primary_key=True),

                             db.Column("vendor_id", db.Integer, db.ForeignKey(

                                 "vendors.id"), primary_key=True)

                             )

contracts_permissions = db.Table("styles_permissions_contract",

                                 db.Column("permission_id", db.Integer, db.ForeignKey(

                                     "styles_permissions.id"), primary_key=True),

                                 db.Column("contract_id", db.Integer, db.ForeignKey(

                                     "contracts.id"), primary_key=True)

                                 )

class Contract(db.Model):

    __tablename__ = 'contracts'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(255), unique=True, nullable=False)

    image_url = db.Column(db.String(255), nullable=False,

                          server_default="/assets/img/alvest/card-default.png")

    alvest_enabled = db.Column(

        db.Boolean, nullable=False, server_default=expression.false())

    users = db.relationship('User', secondary=contracts_users,

                            backref=db.backref('contracts', lazy=True))

    vendors = db.relationship('Vendor', secondary=contracts_vendors,

                              backref=db.backref('contracts', lazy=True))

    permissions = db.relationship('Permission', secondary=contracts_permissions,

                                  backref=db.backref('contracts', lazy=True))

    def __repr__(self):

        return f"(name: {self.name}, image_url: {self.image_url}," \

            f"alvest_enabled: {self.alvest_enabled}"

permission entity:

from api.extensions import db

from sqlalchemy.sql import expression

class Permission(db.Model):

    __tablename__ = 'styles_permissions'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(255), unique=True, nullable=False)

    description = db.Column(db.String(255), unique=True, nullable=False)

    instruction = db.Column(db.String(255), autoincrement=False)

    url = db.Column(db.Integer, autoincrement=False)

    def __repr__(self):

        return f"'id': '{self.id}', 'name': '{self.name}', 'description': '{self.description}, 'instruction': '{self.instruction}', 'url': '{self.url}'"

the joining table is within the contractentity...

Thanks for this by the way, much appreciated 

[Mo Hus]

Hey Jonathan, any luck? thanks

[Simon King]

It looks like you've got a many-to-many relationship between Contract and Permission, and you want to remove a Permission from a Contract (or vice versa). Is that right?

If so, you can do something like this:

contract = <some-contract>

permission = <some-permission>

contract.permissions.remove(permission)

https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#deleting-rows-from-the-many-to-many-table

(note that accessing "contract.permissions" will load all the permissions for that contract from the database, which might be inefficient depending on your application)

Hope that helps,

Simon

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/aac681c3-1a8f-4f99-9d0c-31ab8350518e%40googlegroups.com.

[Mo Hus]

Hey Simon , permissions has only relationship with permission_contract and contract only has relationship with permission_contract ...I want to be able to delete the relationship from permission_contract ...
For example, in my permissions_contract, I may have permission_id = 1 which is the id taken from permissions table and on the same row have contract_id = 1 which is taken from contracts table, I want to be able to query permissions_contract table and check if the contract id and permission id  passed to it is in the table and if so delete the relationship... please advice and thanks in advance

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mo Hus]

In regards to your question..I want to remove the whole relationship for example.. the whole first row as shown in the picture:

[Jonathan Vanasco]

If I had time to respond yesterday, I would have said the same thing as Simon.

Your database model leverages two separate parts of SQLAlchemy:

* SqlAlchemy ORM (Left side of the docs https://docs.sqlalchemy.org/en/13/ )
* SqlAlchemy Core (Right side of the docs https://docs.sqlalchemy.org/en/13/ )

There is nothing wrong with that setup; the two are often used together and the docs recommend that in many situations!

However... the way you use and want to query the relationship table is more suited to redefining the permissions tables from SQLAlchemy Core objects (which are created by invoking `db.Table()`) into SQLAlchemy ORM classes that inherit from `db.model`.  

It is possible to keep these objects in "Core" and query them as-is, but that will have some tradeoffs:

* your application code will mix ORM and Core, which can cause some maintenance headaches
* changes to ORM and Core are independent of each other and may cause issues like race conditions if they overlap. For example, changes made to the database via Core would not necessarily appear to ORM objects/relationships if they are already loaded. 

There is no right way or wrong way here. Given your familiarity with this library though, I personally suggest keeping everything in the ORM.

Going to the ORM docs, your setup right now is roughly in line with a "Many to Many" setup that leverages an `association_table` (https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many)

However, your association_table is more than just primary keys joining the two sides of the relationship - it has other fields - and you want to be querying it directly.  That is more in line with the "Association Object" pattern (https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object)

Altering your model to implementing the Association Object pattern is pretty straightforward and should be easy to do based on the examples in the docs. That should give you the flexibility you need.