Alex Rivera | Logout

sqlalchemy dynamic mapping

Asked 2010-04-04T10:03:19.247
13

I have the following problem:

I have the class:


class Word(object):

    def __init__(self):
        self.id = None
        self.columns = {}

    def __str__(self):
        return "(%s, %s)" % (str(self.id), str(self.columns))

self.columns is a dict which will hold (columnName:columnValue) values. The name of the columns are known at runtime and they are loaded in a wordColumns list, for example


wordColumns = ['english', 'korean', 'romanian']

wordTable = Table('word', metadata,
                  Column('id', Integer, primary_key = True)
                  )
for columnName in wordColumns:
    wordTable.append_column(Column(columnName, String(255), nullable = False))

I even created a explicit mapper properties to "force" the table columns to be mapped on word.columns[columnName], instead of word.columnName, I don't get any error on mapping, but it seems that doesn't work.


mapperProperties = {}
for column in wordColumns:
    mapperProperties['columns[\'%']' % column] = wordTable.columns[column]

mapper(Word, wordTable, mapperProperties)

When I load a word object, SQLAlchemy creates an object which has the word.columns['english'], word.columns['korean'] etc. properties instead of loading them into word.columns dict. So for each column, it creates a new property. Moreover word.columns dictionary doesn't even exists.

The same way, when I try to persist a word, SQLAlchemy expects to find the column values in properties named like word.columns['english'] (string type) instead of the dictionary word.columns.

I have to say that my experience with Python and SQLAlchemy is quite limited, maybe it isn't possible to do what I'm trying to do.

Any help appreciated,

Thanks in advance.

Edit
Report

1 Answer

3

I used nosklo's solution (thanks!) but I already had a primary key (passed in as pk_col) within the column line (first line of csv). So I thought I'd share my modification. I used a ternary.

table = Table(tablename, metadata,
    *((Column(pk_col, Integer, primary_key=True)) if rowname == pk_col else (Column(rowname, String())) for rowname in row.keys()))
table.create()
answered 2012-02-06T07:12:51.180

Your Answer