Model Creation by SQLAlchemy database reflection

Question

I am currently working on a pyramid system that uses sqlalchemy.

This system will include a model (let's call it Base) that is stored in a database table. This model should be extensible by the user on runtime. Basically, the user should be able to subclass the Base and create a new model (let's call this one 'Child'). Childs should be stored in another database table.

All examples available seem to handle database reflection on a predefined model. What would be the best way to generate complete model classes via database reflection?

score 4 · Answer 1 · answered Nov 14 '11 at 17:58

This doesn't seem to have much to do with "database reflection", but rather dynamic table creation. This is a pretty dangerous operation and generally frowned upon.

You should try to think about how to model the possible structure your users would want to add to the Base and design your schema around that. Sometimes these flexible structures can benefit a lot from vertical tables when you don't know what the columns may be.

Don't forget that there's an entire class of data storage systems out there that provide more flexible support for "schemaless" models. Something like Mongo or ZODB might make more sense here.

score 2 · Answer 2 · answered Nov 17 '11 at 13:44

Most of this concept works fine for me, but I fail to bind an existing table to a newly created class, which uses multi-table inheritance. Here is some code to make things more clear:

Base.metadata.reflect(bind=Session.bind)
table = Base.metadata.tables['anExistingTable']
Extension = type('Extension', (BaseClass,), {})
orm.mapper(Extension, table, inherits=orm.class_mapper(BaseClass),
    polymorphic_identity='extension')

This results in the following error:

ArgumentError: Class '<class 'Extension'>' 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.

Do you have any idea why there is a primary mapper defined on a class that has just been created?

score 1 · Answer 3 · edited May 23 '17 at 10:27

1

Just define a generator method

def mapClass(class_name, table_name):
    # Allows to generate previously undefined mapped classes, remapping when necessary
    #For security reason this will only map a class 
    #when class is not previously declared
    #or if declared, when is a MappableClass subclass, 
    #this way we prevent to map any class.
    #Even when not able to return a mapped class it will return corresponding class_name class
    #if so, we'll get an error when accessing non existing MappableClass members
    if not globals.has_key(class_name):
        cls=type(class_name, (MappableClass,), {} )
        globals[class_name]=cls
    else:
        cls=globals[class_name]
    if issubclass(cls,MappableClass):
        tab_obj=Table(table_name,meta,autoload=True)
        mapper(cls, tab_obj)
    return cls

or inherit a generator class as here I added the generator method as static in the answer's class, so I can use both previously declared child classes or new dinamically-created, ones.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 17 '11 at 14:55

Kelbethor

31
5

Hey, thanks for the answer. But do you know how to get the concept of multi table inheritance (see below) into that? – Fynn Nov 17 '11 at 15:01
Well... I don't know how sqlalchemy deals with table inheritance... Is it storing inheritance information in the database? (Sorry asking instead of answering) If not, you need to store it and retreive it at class generation. Anyway, you can add inheritance mapper arguments to mapClass method – Kelbethor Nov 17 '11 at 15:06
Yes, the polymorphic type is stored in the database ([see here](http://www.sqlalchemy.org/docs/orm/inheritance.html#joined-table-inheritance)). But I struggle to find a solution to the combination of database reflection and inheritance. – Fynn Nov 17 '11 at 15:10
I'm no sqlalchemy expert but can you give a simplified idea on what you need to do? – Kelbethor Nov 17 '11 at 15:28
I just solved the problem and posted my solution below. Thanks for your help, though :) – Fynn Nov 17 '11 at 15:57
I thought you wanted to relfect inheritances automatically from all tables... I think sqlalchemy is not saving this to db (or I dont know where to look) Well, a workaround could be create a table to store who inherits who... – Kelbethor Nov 17 '11 at 16:54

score 1 · Accepted Answer · answered Nov 17 '11 at 15:56

I just solved the problem described above by using the following code snippet:

table = Table('anExistingTable', Base.metadata, autoload=True, autoload_with=Session.bind)
Extension = type('Extension', (BaseClass,), {
    '__table__' : table,
    '__mapper_args__' : {
        'inherits': BaseClass,
        'polymorphic_identity': 'extension'
    }
})

However, I don't know why the first try didn't work out...

Model Creation by SQLAlchemy database reflection

4 Answers4