How to extend sqlalchemy Column

Question

I am using SQLAlchemy's ORM to model a database in an application I am writing.

One of the things I need to do regularly is to scrape some Excel sheets for data. These sheets do not have columns that are named properly for easy import into a database. In order to encapsulate information with my model, I created an extension to sqlalchemy.schema.Column. Here are the relevant bits of the definition:

from sqlalchemy.schema import Column


class ExcelColumn(Column):

    def __init__(self, *args, excel_column_name=None, value_map=None, **kwargs):
        super().__init__(*args, **kwargs)
        self._excel_column_name = None
        self._value_map = None
        self.excel_column_name = excel_column_name
        self.value_map = value_map

    @property
    def excel_column_name(self):
        if self._excel_column_name is None:
            return self.name
        else:
            return self._excel_column_name

    @excel_column_name.setter
    def excel_column_name(self, n):
        self._excel_column_name = n

    @property
    def value_map(self):
        return (lambda x: x) if self._value_map is None else self._value_map

    @value_map.setter
    def value_map(self, fn):
        if callable(fn) or fn is None:
            self._value_map = fn
        else:
            raise ValueError('ExcelColumn.value_map must be callable.')

Now, I can define tables using the ExcelColumn instead of Column.

class TableA(Base):
    column_a = ExcelColumn(Integer, excel_column_name='Column for [a]')
    # not updated from Excel
    updated_on = Column(DateTime(), default=datetime.datetime.now)

This works exactly as I intended it, and my import function is able to pick out the exact columns to extract from the Excel sheet.

>> TableA.__mapper__.c['column_a'].excel_column_name
'Column for [a]'

The problem I am running into is that if I create a mixin class with some common columns for some tables, the excel columns lose information.

class Mixin():
    common_column = ExcelColumn(Integer, excel_column_name='Common Column "name"')


class TableB(Mixin, Base):
    column_b = ExcelColumn(Integer, excel_column_name='Coulmn of [b]')

Now when I inspect TableB, I see that it has lost information from common_column.

>> TableB.__mapper__.c['common_column'].excel_column_name
'common_column'

How can I extend Column so that information is not lost when using a mixin class?

Thank you - I used a modified form of this implementation to develop a custom column + mixin that does serial anonymization — Jake
– Jake, Commented Jul 1, 2022 at 15:32

hoogamaphone · Accepted Answer · 2017-05-05 13:59:48Z

1

After digging through the source code, I found that it is necessary to override the copy method of Column. Add the following method to the ExcelColumn class:

def copy(self, **kwargs):
    c = super().copy(**kwargs)
    c._excel_column_name = self._excel_column_name
    c._value_map = self._value_map
    return c

answered May 5, 2017 at 13:59

hoogamaphone

1,10210 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extend sqlalchemy Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related