Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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.


-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/9121424d-8298-4018-9e95-1d4e312604e2%40googlegroups.com.

Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Mo Hus]

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

On Friday, May 29, 2020 at 10:21:44 PM UTC+1, Mo Hus wrote:
>
> 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
>
> On Friday, May 29, 2020 at 7:33:22 PM UTC+1, Simon King wrote:
>>
>> 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 = 
>> 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
>>
>> On Fri, May 29, 2020 at 12:38 PM Mo Hus  wrote:
>>
>>> Hey Jonathan, any luck? thanks
>>>
>>> On Thursday, May 28, 2020 at 4:15:38 PM UTC+1, Mo Hus wrote:

 [image: Capture.PNG]

 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..

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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/839cfc7d-e2b0-45d6-89c4-5bb6f4cc06b6%40googlegroups.com.

Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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

On Friday, May 29, 2020 at 7:33:22 PM UTC+1, Simon King wrote:
>
> 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 = 
> 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
>
> On Fri, May 29, 2020 at 12:38 PM Mo Hus > 
> wrote:
>
>> Hey Jonathan, any luck? thanks
>>
>> On Thursday, May 28, 2020 at 4:15:38 PM UTC+1, Mo Hus wrote:
>>>
>>> [image: Capture.PNG]
>>>
>>> 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..
>>>
>>> -- 
>> 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 sqlal...@googlegroups.com .
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/aac681c3-1a8f-4f99-9d0c-31ab8350518e%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b7e07a37-7d38-4783-b0e7-be337f9147b3%40googlegroups.com.

Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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 = 
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

On Fri, May 29, 2020 at 12:38 PM Mo Hus  wrote:

> Hey Jonathan, any luck? thanks
>
> On Thursday, May 28, 2020 at 4:15:38 PM UTC+1, Mo Hus wrote:
>>
>> [image: Capture.PNG]
>>
>> 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..
>>
>> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/aac681c3-1a8f-4f99-9d0c-31ab8350518e%40googlegroups.com
> 
> .
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexe5ZW0stWL0PUyu_mLOTt5XQNctgYmDGyArky6PGhE1Aw%40mail.gmail.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Mo Hus]

Hey Jonathan, any luck? thanks

On Thursday, May 28, 2020 at 4:15:38 PM UTC+1, Mo Hus wrote:
>
> [image: Capture.PNG]
>
> 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..
>
>

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

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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 


On Thursday, May 28, 2020 at 6:35:34 PM UTC+1, Jonathan Vanasco wrote:
>
> 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
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/3c5b168a-0a46-41bf-b920-3ebccdeab8bc%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ff5bec4d-7dec-4c49-a802-8ac3643f9af4%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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


On Thursday, May 28, 2020 at 6:07:05 PM UTC+1, Tanjil Hussain wrote:
>
> Could you please give me an example. sorry im abit confused and new to 
> this..
>
> On Thursday, May 28, 2020 at 6:02:52 PM UTC+1, Tanjil Hussain wrote:
>>
>> Hey, what do you mean by this exactly.. sorry im abit confused and new to 
>> this..
>>
>> On Thursday, May 28, 2020 at 5:55:08 PM UTC+1, Jonathan Vanasco wrote:
>>>
>>> Sorry, I meant the SqlAlchemy schema.  I can't attempt to troubleshoot 
>>> code that I can't see.
>>>
>>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fac851ed-d9d8-4163-a86b-76e2eebd7bd6%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Tanjil Hussain]

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

On Thursday, May 28, 2020 at 6:02:52 PM UTC+1, Tanjil Hussain wrote:
>
> Hey, what do you mean by this exactly.. sorry im abit confused and new to 
> this..
>
> On Thursday, May 28, 2020 at 5:55:08 PM UTC+1, Jonathan Vanasco wrote:
>>
>> Sorry, I meant the SqlAlchemy schema.  I can't attempt to troubleshoot 
>> code that I can't see.
>>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/323208c2-2343-4990-83ee-7adfe26436dd%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Tanjil Hussain]

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

On Thursday, May 28, 2020 at 5:55:08 PM UTC+1, Jonathan Vanasco wrote:
>
> Sorry, I meant the SqlAlchemy schema.  I can't attempt to troubleshoot 
> code that I can't see.
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d4951c0a-02ae-4dcd-8b7d-1c6eb0f6caf1%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/3b37b465-4245-4522-8191-f7a6a899ad7c%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Tanjil Hussain]

permission_contract:

[image: Capture.PNG]
permissions: 

[image: Capture2.PNG] 




contracts:

[image: Capture3.PNG]



-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/a31be69a-a9e9-4545-945b-fa0aab6635a3%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

can you share your schema for these 3 tables?

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f12ee526-1da2-4431-8453-1cd28a6dae03%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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:

[image: Capture.PNG]

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
On Thursday, May 28, 2020 at 4:44:31 PM UTC+1, Jonathan Vanasco wrote:
>
>
> `.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()
>
>
>
>
> On Thursday, May 28, 2020 at 11:15:38 AM UTC-4, Tanjil Hussain wrote:
>>
>> [image: Capture.PNG]
>>
>> 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..
>>
>>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/cc6ea4ce-7033-4fb9-af7b-44daea20fd22%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[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()




On Thursday, May 28, 2020 at 11:15:38 AM UTC-4, Tanjil Hussain wrote:
>
> [image: Capture.PNG]
>
> 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..
>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f095a379-1e2e-42c3-89b1-d739de014086%40googlegroups.com.