CODEWITHSUNDEEP
pythonsqlalchemy
Christian P.
Christian P.

Reputation: 4884

Using SQLAlchemy mappings with variant tables

I have a database that we use for all of our customers. Each customer has a set of tables, postfixed with their customer ID:

It is set up with "manual" SQL statements, but I am undertaking an effort to port it to SQLAlchemy for easier maintainability.

I would prefer to use the declarative syntax, but is there any way I can dynamically set the __tablename__ value of a model? And what happens if I need to access multiple customers in a threaded environment (we will most likely use different processes, but I'd rather cover it up front just in case).

Besides the tablename itself, all relationships (straightforward single key foreign keys) would also have to be mapped correctly between tables.

Upvotes: 0

Views: 155

Answers (1)

donkopotamus
donkopotamus

Reputation: 23236

One approach is to factory up all of the classes you require for a customer. Something like the following ... (this is just a sketch!)

class customer_classes:
    # create the base class
    base = declarative_base(metadata=metadata)

    def __init__(self, metadata, customer_id):

        class analytics(self.base):
            __tablename__ = 'analytics_{}'.format(customer_id)
            __mapper_args__ = ...

        class embeds(self.base):
            __tablename__ = 'analytics_{}'.format(customer_id)
            __mapper_args__ = ...

        class downloads(self.base):
            __tablename__ = 'analytics_{}'.format(customer_id)
            __mapper_args__ = ...

        # now lets assign all these classes to attributes
        import inspect
        defined_classes = [cls for cls in locals().values()
                           if inspect.isclass(cls) and issubclass(cls, base)]
        self.__dict__.update({cls.__name__: cls for cls in defined_classes})

# now we can create a set of classes for customer 123
customer_model = customer_classes(123)
session.query(customer_model. analytics).all()

# you can just as easily work with another customer at the same time in the same session
another_model = customer_classes(456)
session.query(another_model.analytics).all()

Upvotes: 2

Related Questions