SQLAlchemy - set value in model using database column name, not property name

Question

I have a table with the column TS_TEST_ID. I have a SQLAlchemy model with the following property:

id = Column(u'TS_TEST_ID', INTEGER(), primary_key=True, nullable=False)

Is there any way to set id on an instance of my model class when only TS_TEST_ID is known? That is, I only know the name of the column in the database, and I want to somehow map that to id and set the id property on my model. I was hoping just doing MyModel(**{'TS_TEST_ID':1}) would work, but I get the following error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 4, in __init__
  File "C:\Python26\lib\site-packages\sqlalchemy\orm\state.py", line 111,
   in initialize_instance
    return manager.events.original_init(*mixed[1:], **kwargs)
  File "C:\Python26\lib\site-packages\sqlalchemy\ext\declarative.py", line 1377,
   in _declarative_constructor
    (k, cls_.__name__))
TypeError: 'TS_TEST_ID' is an invalid keyword argument for MyModel

I would rather not have to define TS_TEST_ID = Column(u'TS_TEST_ID', INTEGER(), primary_key=True, nullable=False) for every column in my model, either, or even a method called TS_TEST_ID that returns the id property.

Answer 1

This seems more complicated than it needs to be, but it works. I'm hoping there's a more direct way of doing this...

@staticmethod
def fromFieldHash(h):
    row = MyModel()
    cols = list(row.__table__._columns)
    for k, v in h.iteritems():
        col = find(lambda c: c.key == k, cols)
        # See http://www.sqlalchemy.org/trac/wiki/06Migration#AnImportantExpressionLanguageGotcha
        if col is not None:
            prop = row.__mapper__._columntoproperty[col].key
            setattr(row, prop, v)
    return row

The find method I'm using is from Cleanest Python find-in-list function.

Answer 2

If one would like __init__ to handle both attributes and columns, though having a dedicated classmethod factory seems like a cleaner approach, the following implementation could be used as an abstract base:

from sqlalchemy import inspect
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


def _create_col_lookup(mapper):
    return {c.name: k for k, c in mapper.columns.items()}


class ColBase(Base):

    __abstract__ = True

    def __init__(self, *args, **kwargs):
        # Create a lookup of column name -> attr key
        ctoa = _create_col_lookup(inspect(self).mapper)

        # Process intersecting keys
        for k in ctoa.keys() & kwargs.keys():
            setattr(self, ctoa[k], kwargs.pop(k))

        super().__init__(*args, **kwargs)

Using the above the following asserts pass:

class Foo(ColBase):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    bar = Column('Baz 1')

f = Foo(**{'Baz 1': 1})
assert f.bar == 1

f = Foo(**{'bar': 1})
assert f.bar == 1