sqlalchemy dynamic mapping

Question

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.

Answer 1

It seems that you can just use the attributes directly instead of using the columns dict.

Consider the following setup:

from sqlalchemy import Table, Column, Integer, Unicode, MetaData, create_engine
from sqlalchemy.orm import mapper, create_session

class Word(object):
    pass

wordColumns = ['english', 'korean', 'romanian']
e = create_engine('sqlite://')
metadata = MetaData(bind=e)

t = Table('words', metadata, Column('id', Integer, primary_key=True),
    *(Column(wordCol, Unicode(255)) for wordCol in wordColumns))
metadata.create_all()
mapper(Word, t)
session = create_session(bind=e, autocommit=False, autoflush=True)

With that empty class you can do:

w = Word()
w.english = u'name'
w.korean = u'이름'
w.romanian = u'nume'

session.add(w)
session.commit()

And when you want to access the data:

w = session.query(Word).filter_by(english=u'name').one()
print w.romanian

That's the whole sqlalchemy's ORM point, instead of using a tuple or dict to access the data, you use attribute-like access on your own class.

So I was wondering for reasons you'd like to use a dict. Perhaps it's because you have strings with the language names. Well, for that you could use python's getattr and setattr instead, as you would on any python object:

language = 'korean'
print getattr(w, language)

That should solve all of your issues.

---

That said, if you still want to use dict-like access to the columns, it is also possible. You just have to implement a dict-like object. I will now provide code to do this, even though I think it's absolutely unnecessary clutter, since attribute access is so clean. If your issue is already solved by using the method above, don't use the code below this point.

You could do it on the Word class:

class Word(object):
    def __getitem__(self, item): 
        return getattr(self, item)
    def __setitem__(self, item, value):
        return setattr(self, item, value)

The rest of the setup works as above. Then you could use it like this:

w = Word()
w['english'] = u'name'

If you want a columns attribute then you need a dict-like

class AttributeDict(DictMixin):
    def __init__(self, obj):
        self._obj = obj
    def __getitem__(self, item):
        return getattr(self._obj, item)
    def __setitem__(self, item, value):
        return setattr(self._obj, item, value)

class Word(object):
    def __init__(self):
        self.columns = AttributeDict(self)

Then you could use as you intended:

w = Word()
w.columns['english'] = u'name' 

I think you'll agree that all this is unnecessarly complicated with no added benefit.

Answer 2

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()