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.

• Maxim Sloyko: Can't check it anywere, so just a wild guess: does yourobj.c.TS_TEST_ID = 15 do the trick?
• Sarah Vessels: An instance of MyModel doesn't have a c property, but I can access the same thing (I think) through myobj.__mapper__.c, and trying to access myobj.__mapper__.c.TS_TEST_ID fails with an AttributeError.

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.

• Ilja Everilä: It seems Mapper.columntoproperty has been protected (prefixed with _), so this'll require some tweaking. Prolly a call to Mapper.get_property_by_column() instead.

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