Dynamically creating sqlAlchemy Metaclass based on

Question

We have several clients in a ERP system. Each client has it's own database. The databases are identical in terms of schema.

Do not ask me why, but the ERP db does not have formally defined PKs, and so it is not possible to reflect the database... In stead we found that declaring a Metaclass, with a table declaration, detailing PK, and autoload works. An example:

class Customers(Base):

    __table__ = Table('Customers', Base.metadata,
                      Column('UniqueNo', Integer, primary_key=True),
                      schema = 'databaseName.schema',
                      autoload = True

A quick not on the schema = part. The schema is the same for each database, but the naming of the schema (as well as the db name itself) is different. Defining the schema in the Metaclass like this, enables us to query accross the databases, being able to

When creating the code structure, the simplest way to do the Metaclass declaration is manually. Having one .py file for each database, and doing the same Metaclass declaration within each file, changing only the schema, and adding a suffix to the class name to avoid naming confusion. Like so:

client1.py

class Customers_1(Base):

    __table__ = Table('Customers', Base.metadata,
                      Column('UniqueNo', Integer, primary_key=True),
                      schema = 'Dbclient1.client1Schema',
                      autoload = True

client2.py

class Customers_2(Base):

    __table__ = Table('Customers', Base.metadata,
                      Column('UniqueNo', Integer, primary_key=True),
                      schema = 'Dbclient2.client2Schema',
                      autoload = True

Doing it this way works, but our hope is that we could reduce the amount of code by dynamically creating the Metaclasses based on only one ERPTables.py file. Example:

ERPTables.py 

class Customers(Base):

    __table__ = Table('Customers', Base.metadata,
                      Column('UniqueNo', Integer, primary_key=True),
                      autoload = True

This leads us down the road of Metaclasses, which is unfamiliar territory. I have gotten to the point where I am able to dynamically creating the metaclass declaration. But: registering the declaration is where my understanding falls short. I have come this far:

from sqlalchemy import Table
import ERPTables
import inspect

def iterate_ERPTables_Tables():
    for table in inspect.getmembers(ERPTables):
        if isinstance(table[1], Table) :
            return table[0], table[1]


dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}

 tables = [iterate_TableTest_Tables()]

 for key in dbSchemas:

    for table in tables:

          cls = type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]})        

          break

This code works! The only problem being that the SA Metaclass gets named cls. Hence the break. Without it we are trying to declare several Metaclasses with the same class name.

I have tried several approaches to resolve this, like attempts to use the unfamiliar metaclass perspective:

dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}

 tables = [iterate_TableTest_Tables()]

 for key in dbSchemas:

    for table in tables:

          type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]}).__new__        

To full blown hack work arounds:

dbSchemas = {'_1': 'Dbclient1.client1Schema', '_2': 'Dbclient2.client2Schema'}

 tables = [iterate_TableTest_Tables()]

 for key in dbSchemas:

    for table in tables:

          exec("%s = %s" % (table[0] + key, type(table[0] + key, (Base, ), {'__tablename__': table[0], '__table__': table[1]})))

But all of my attemtps have been unsuccsessful and so, being at the end of the rope, I turn to SO in the hope that someone can show me how to solv this!

PS: In case anyone is wondering, I have not, as of yet, solved how to inject the schema from the dbSchemas dictionnary into the Metaclass. I am hoping to find a way, but one problem at a time!

Answer 1

If you want to export the reference to the classes, you can add them to globals():

globals()[table[0] + key] = type(...)