SQLAlchemy: Dynamically loading tables from a list

Question

I am trying to create a program that loads in over 100 tables from a database so that I can change all appearances of a user's user id.

Rather than map all of the tables individually, I decided to use a loop to map each of the tables using an array of objects. This way, the table definitions can be stored in a config file and later updated.

Here is my code so far:

def init_model(engine):
    """Call me before using any of the tables or classes in the model"""
    meta.Session.configure(bind=engine)
    meta.engine = engine

class Table:
    tableID = ''
    primaryKey = ''
    pkType = sa.types.String()
    class mappedClass(object):
        pass


WIW_TBL = Table()
LOCATIONS_TBL = Table()

WIW_TBL.tableID = "wiw_tbl" 
WIW_TBL.primaryKey = "PORTAL_USERID"
WIW_TBL.pkType = sa.types.String()

LOCATIONS_TBL.tableID = "locations_tbl"
LOCATIONS_TBL.primaryKey = "LOCATION_CODE"
LOCATIONS_TBL.pkType = sa.types.Integer()

tableList = ([WIW_TBL, LOCATIONS_TBL])

for i in tableList:

    i.tableID = sa.Table(i.tableID.upper(), meta.metadata,
    sa.Column(i.primaryKey, i.pkType, primary_key=True),
    autoload=True,
    autoload_with=engine) 

    orm.mapper(i.mappedClass, i.tableID)

The error that this code returns is:

sqlalchemy.exc.ArgumentError: Class '<class 'changeofname.model.mappedClass'>' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper.  clear_mappers() will remove *all* current mappers from all classes.

I cant use clear_mappers as it wipes all of the classes and the entity_name scheme doesn't seem to apply here.

It seems that every object wants to use the same class, although they all should have their own instance of it.

Does anyone have any ideas?

van · Accepted Answer · 2011-09-29 13:50:46Z

1

Well, in your case it *is the same Class you try to map to different Tables. To solve this, create a class dynamically for each Table:

class Table(object):
    tableID = ''
    primaryKey = ''
    pkType = sa.types.String()
    def __init__(self):
        self.mappedClass = type('TempClass', (object,), {})

But I would prefer slightly cleaner version:

class Table2(object):
    def __init__(self, table_id, pk_name, pk_type):
        self.tableID = table_id
        self.primaryKey = pk_name
        self.pkType = pk_type
        self.mappedClass = type('Class_' + self.tableID, (object,), {})
# ...
WIW_TBL = Table2("wiw_tbl", "PORTAL_USERID", sa.types.String())
LOCATIONS_TBL = Table2("locations_tbl", "LOCATION_CODE", sa.types.Integer())

answered Sep 29, 2011 at 13:50

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RonnyKnoxville Over a year ago

Perfect, thats exactly what I needed! I had been trying type() earlier on but to no success. Thanks for that, very much appreciated! Although for some reason it doesnt pass it through to the view, but Ill figure it out :D

van Over a year ago

In order for classes to be seen on import, you should add them to globals(). For example: after the self.mappedClass = type('Class_' + self.tableID, (object,), {}) line add globals()['Class_' + self.tableID] = self.mappedClass line. In this case the view will be able to import class Class_locations_tbl and Class_wiw_tbl

Collectives™ on Stack Overflow

SQLAlchemy: Dynamically loading tables from a list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related