please see the patches in
https://github.com/sqlalchemy/sqlalchemy/issues/4461, in 1.2.17 the
recipe will be altered to read:
@event.listens_for(Query, "before_compile", retval=True)
@event.listens_for(Query, "before_compile_update", retval=True)
@event.listens_for(Query, "before_compile_delete", retval=True)
def before_compile(query, context=None):
"""A query compilation rule that will add limiting criteria for every
subclass of HasPrivate"""
until that release, you can also subclass Query to override the
update() and delete() methods so that the Query can be modified in
place.
On Tue, Jan 22, 2019 at 6:24 PM Mike Bayer <[email protected]> wrote:
>
> hi there -
>
> tenant ID that is done on a per-schema basis is handled by the schema
> name translation feature at
> https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating
>
> for a SELECT criteria you would use the filtered query which is at
> https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery and has
> been modified and simplified to use events (note the bitbucket wiki is
> obsolete, it's not supposed to be up). The issue with
> query.update() and query.delete() not being included can be addressed
> once we add event hooks that are analogous to before_compile() event
> hook for Query into the persistence.BulkUpdate and
> persistence.BulkDelete classes. The SQLAlchemy project would welcome
> contributors who want to work on this as it's quite simple to add.
>
>
> On Tue, Jan 22, 2019 at 4:49 PM Daniel Lister <[email protected]> wrote:
> >
> > Hi all,
> >
> > I'm working on a multi tenant web app with SQLAlchemy and I would like to
> > use a tenant_id column on some tables to isolate data per tenant. Ideally,
> > filtering on and adding this tenant_id would be automatic. I found this
> > talk by the creator of Flask that briefly mentions a way to do this with
> > sqlalchemy. This recipe goes into more detail on this approach but also
> > points out some flaws. Primarily that it will not handle update or delete
> > queries. To me this feels like a major flaw, a developer, used to having
> > tenant_id handled automatically, may easily forget when performing an
> > update or delete, thus creating a vulnerability. It proposes to solve the
> > update and delete issue by using before_cursor_execute() event handler.
> > However, as far as I can tell, this approach would involve editing the SQL
> > text directly, defeating much of the point of using SQLAlchemy. Not as much
> > of an issue but inserts are also not handled.
> >
> > I also found this library that tries to solve the problem, however it is
> > described as experimental and hasn't been updated in 5 years. It seems to
> > have the problems of the above solution as well.
> >
> > It seems to me that this is a common pattern and I'm surprised there aren't
> > good, solid solutions out there for SQLAlchemy. This library supports this
> > approach for Django.
> >
> > Ideally I would love to have a session that is scoped to a tenant and
> > automatically applies a tenant_id on every select, update, delete, and
> > insert so that queries can be written without the need to consider tenant
> > at all. Is this feasible? I would greatly appropriate if anyone could help
> > or point me in the right direction. 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 [email protected].
> > To post to this group, send email to [email protected].
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
