[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python
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
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.
Re: [sqlalchemy] Cascade child updates onto the parent
Perfect. That's exactly what I ended up doing. I added events
(after_insert/update/delete) for each backref.
For each has-many relationship (through a secondary table) I had to
consider the fact that the parent model would exist in session.dirty but
not trigger the "onupdate" action on the column. So I added a generic
before_update/delete event on my models' base class which is basically just
target.updated_at = dt.now().
On Thursday, May 28, 2020 at 11:06:28 AM UTC-5, Mike Bayer wrote:
>
>
>
> On Wed, May 27, 2020, at 3:57 PM, Colton Allen wrote:
>
> Hello,
>
> I'm trying to automate a backref update. Basically, when a child model is
> inserted or updated I want the parent model's "updated_at" column to
> mutate. The value should be the approximate time the user-child-model was
> updated. The updated_at value would not have to match the
> created_at/updated_at value on the child. It would just need to mutate to
> a new time.
>
> class UserModel(db.Model):
> updated_at = db.Column(db.DateTime, default=db.now, onupdate=datetime.
> now)
>
>
> class UserChildModel(db.Model):
> user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=
> False)
> user = db.relationship('UserModel', backref='children')
>
> user = UserModel()
> save(user)
> print(user.updated_at) # x
>
> child = UserChildModel(user_id=user.id)
> save(child)
> print(user.updated_at) # y (value changed)
>
> Hopefully this pseudocode is sufficient.
>
> I'm wondering if there is an option I can specify on the orm.relationship
> factory. Or will I need to define an event?
>
>
> that could certainly be based on an event from the SQLAlchemy side. a
> very straightforward one would be the before_insert / before_update /
> after_insert / after_update suite of events, I would emit an UPDATE
> statement against the parent table using the foreign key on the child row
> that's being inserted/updated. Another approach would be a DB trigger.
>
> the mapper level events are detailed at
> https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_insert#sqlalchemy.orm.events.MapperEvents.before_insert
>
> the "connection" right there is where you'd run your update, like:
>
> connection.execute(update(parent).values(updated_at=datetime.now()).where(
> parent.id == inserted.parent_id))
>
>
>
>
>
> Thanks!
>
>
> --
> 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/490bfcd2-4ebd-4df3-98a8-516caeacbd6a%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/555b8c57-85cb-48fa-9e29-491796877fd6%40googlegroups.com.
[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python
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
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
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
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
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.
Re: [sqlalchemy] Cascade child updates onto the parent
On Wed, May 27, 2020, at 3:57 PM, Colton Allen wrote:
> Hello,
>
> I'm trying to automate a backref update. Basically, when a child model is
> inserted or updated I want the parent model's "updated_at" column to mutate.
> The value should be the approximate time the user-child-model was updated.
> The updated_at value would not have to match the created_at/updated_at value
> on the child. It would just need to mutate to a new time.
>
> class UserModel(db.Model):
> updated_at = db.Column(db.DateTime, default=db.now, onupdate=datetime.now)
>
>
> class UserChildModel(db.Model):
> user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
> user = db.relationship('UserModel', backref='children')
>
> user = UserModel()
> save(user)
> print(user.updated_at) # x
>
> child = UserChildModel(user_id=user.id)
> save(child)
> print(user.updated_at) # y (value changed)
>
> Hopefully this pseudocode is sufficient.
>
> I'm wondering if there is an option I can specify on the orm.relationship
> factory. Or will I need to define an event?
that could certainly be based on an event from the SQLAlchemy side. a very
straightforward one would be the before_insert / before_update / after_insert /
after_update suite of events, I would emit an UPDATE statement against the
parent table using the foreign key on the child row that's being
inserted/updated. Another approach would be a DB trigger.
the mapper level events are detailed at
https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_insert#sqlalchemy.orm.events.MapperEvents.before_insert
the "connection" right there is where you'd run your update, like:
connection.execute(update(parent).values(updated_at=datetime.now()).where(parent.id
== inserted.parent_id))
>
> Thanks!
>
> --
> 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/490bfcd2-4ebd-4df3-98a8-516caeacbd6a%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/57bd5e2e-f5e4-49de-9668-e48051364f17%40www.fastmail.com.
[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python
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
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
`.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.
[sqlalchemy] how can i remove an entry from relational database using sqlalchemy in python
[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/8bffee78-5174-444c-aacb-91ba2a0832d1%40googlegroups.com.
