how to dynamically create SQLAlchemy columns

Question

I have a csv file with first line as fields and remaining lines as data. With this file I would like to create a table. Since the fields are quite long and may vary, I want to dynamically create it. After several tries and searches, I figured I could maybe generate a Class from factory and then assign the attributes to the new class could generate dynamic columns. But it fails. And based on the error, I tried some other methods, all failed. Please help. I'm considering whether it's doable for such an idea.

Reader = csv.reader(open('Book1.csv', 'rb'), delimiter=',', quotechar='|')
TableItem = Reader.next()[0:]
def Factory(*args, **kwargs):
    args=args[0]
    def init(self, *iargs, **ikwargs):
        #setattr(self,__tablename__,ikwargs[__tablename__])
        for k,v in kwargs.items():
            setattr(self, k, v)
        for i in range(len(iargs)):
            setattr(self, args[i], iargs[i])
        for k,v in ikwargs.items():
            setattr(self, k, v)

    name = kwargs.pop("name", "myFactory")
    kwargs.update(dict((k, None) for k in args))
    return type(name, (object,), {'__init__': init})

LIS=Factory(TableItem)
class newLIS(LIS,Base):
    __tablename__='testonly'
    pass
mytest=[]
for row in Reader:
    mytest.append(newLIS(row))

The error is like:

sqlalchemy.exc.ArgumentError: Mapper Mapper|newLIS|testonly could not assemble
any primary key columns for mapped table 'testonly'

partially solved after finding this http://stackoverflow.com/questions/2574105/sqlalchemy-dynamic-mapping — Nicolas, Jan 13 '11 at 09:08

score 13 · Accepted Answer · edited May 23 '17 at 12:26

I'm the author of the related answer:

sqlalchemy dynamic mapping

I have this answer to another question, which I think is even more related - it could even be a duplicate:

Database on the fly with scripting languages

As you can see, to make it you need to create a table object and map it against your class. Also, mapped classes in sqlalchemy need a primary key on the table, so you have to add one. Are you having any other problem besides that? If so, can you paste your updated code?

how to dynamically create SQLAlchemy columns

1 Answers1