Sqlalchemy dynamically create table and mapped class

Question

Given a set of column names and their types, the goal is to
to instantiate a table and the corresponding mapped class.

It is related to question posted here: Dynamic Class Creation in SQLAlchemy.

So far I have the following:

table = Table(tbl, 
              metadata, 
             *(Column(col, ctype, primary_key=pk, index=idx) for  col,  ctype, pk, idx in zip(attrs, types, primary_keys, indexes))
              )

This creates the table object. Now I need to create the corresponding class.

mydict={'__tablename__':tbl}
cls = type(cls_name, (Base,), mydict)

This gives me following error:

ArgumentError: Mapper Mapper|persons_with_coord|t_persons_w_coord could not assemble any primary key columns for mapped table

My question is how do I specify the primary keys as part of the class creation. And after the class is created do I need to call mapper as follows:

mapper(cls, table)

Haleemur Ali Haleemur Ali · Accepted Answer · 2017-02-16T04:32:41

The mydict mapping has to either include a table object or specify that the table needs to be loaded from the database.

This should work

mydict={'__tablename__':tbl, '__table__': table}

This should also work

mydict={'__tablename__':tbl, '__table_args__': ({'autoload':True},)}

the primary keys are already specified when you create table. It shouldn't be necessary to specify them again when creating a declarative, however if it required (say, for a complex key), it should be specified in __table_args__.

The mapper has to be called on a declarative class. However, the newly created declarative doesn't have a metadata object. Adding the metadata will make the dynamically created declarative behave more like a regular declarative, so I would recommend putting this line after class creation

cls.metadata = cls.__table__.metadata

so, the full edits could be:

mydict={'__tablename__':tbl, '__table__': table}
cls = type(cls_name, (Base,), mydict)
cls.metadata = cls.__table__.metadata

Sqlalchemy dynamically create table and mapped class

1 Answers