On 10/22/2016 08:08 AM, Nikola Radovanovic wrote:
Hi all,
I have a question regarding what will be the best possible way to
resolve following scenario:
1. lets say I have table(s) in 'public' schema and multiple 'private'
schemes with same table layout
2. each private schema is for separate customer, and public contains
some data everyone may access
3. clients are dynamic category: i.e some might be added or removed
during runtime
There are various relationships (one-to-one, one-to-many and
many-to-many) for tables/objects in:
1. same schema and
2. private and public schemes (but not among privates)
Basically, I created (virtual) base class for each 'private' object and
'generator' class that can create Python class for specific client along
with proper relationships towards 'public' table(s) and 'private' tables
reside in same schema. Each class is within its own python file. There
is also a Base class (from declarative_base()) all objects in my model
inherits. When adding a client, I use /setattr/ to add relationship
property to class
this is a common problem and people will often reach for doing it this
way first, however this is a complicated approach that you're correct
isn't going to scale arbitrarily. Python classes and mappings and Table
objects and all of that take up a lot of memory which for a few hundred
/ thousand customers in a process will really cause a lot of bloat.
Fortunately, we have a different approach for the "multi-tenancy"
problem, this has been a recipe-based thing up through 1.0 and in 1.1 we
have an official featured version of it. The only requirement is that
you only deal with one "customer" at a time, at the very least on a
per-query basis but hopefully more on a per-transaction or per-session
basis. you just create a "placeholder" schema name that you put in for
all the models that represent customer-centric, and at SQL generation
time you swap in that customer's schema name. The example in
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#multi-tenancy-schema-translation-for-table-objects
shows how to do this, if you're stuck on an older version there's an
event / search-and-replace based approach that's pretty much just as easy.
So, I am wandering:
1. is this good way to go
2. I have few classes inherits from db.Model (FLASK app) - will it be
good idea to switch them also to use same Base object
3. since I don't know in advance number/names of the clients and there
are generated Python classes - how does this affects usage of
metadata to create tables. I noticed troubles with i.e. many-to-many
when client from public schema try to set backref; I got error about
multiple definitions in ORM (like it tries to add same relationship
multiple times)
Thank You all in advance
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[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.
