sqlalchemy use variable for column definition

Question

Let's say I have many models that all use the same type of column. e.g.

class PurchaseOrder(Model):
    unitprice = Column(Numeric(10,2))
    ...other columns

class SalesOrder(Model):
    salesprice = Column(Numeric(10,2))
    ...other columns

I would like to define some variable like;

currencyamount = Column(Numeric(10,2))

Then, if the definition ever changes I would only have one place to fix it. I would expect the model definition to look something like this now.

class PurchaseOrder(Model):
    unitprice = currencyamount
    ...other columns

If I try this I get an error along the lines of "Column object 'unitprice' already assigned to Table PurchaseOrder."

How can I go about doing this?

Ian Wilson · Accepted Answer · 2021-10-31 02:01:28Z

I usually use a function and then call it to generate that column. Another nice thing about this is you can add args to customize the column config.

def currencyamount_col():
    return Column(Numeric(10,2))

#...

class PurchaseOrder(Model):
    unitprice = currencyamount_col()
    # ...other columns

Here is a date column factory I use:


def dt_column(default_now=True, onupdate_now=False, **override_col_kwargs):
    dt_kwargs = {
        "timezone": True
    }
    dt = DateTime(**dt_kwargs)
    col_kwargs = {}
    if default_now:
        col_kwargs['default'] = utc_now
    if onupdate_now:
        col_kwargs['onupdate'] = utc_now
    col_kwargs.update(override_col_kwargs)
    return Column(dt, **col_kwargs)

riptusk331 · Accepted Answer · 2021-10-31 03:22:55Z

I think the cleanest way is to just create an abstract Order base class that contains your common attribute definition, and then subclass that for your different order types.

SQLAlchemy allows you to create a base class that will not be represented in the database via the __abstract__ indicator. You can define common attributes on this class. Read more here

Something like this should work for you:

class Order(Model):
    __abstract__ = True
    unitprice = Column(Numeric(10,2))

class PurchaseOrder(Order):
    ...other columns

class SalesOrder(Order):
    ...other columns

The unitprice column will be inherited by both subclasses.

MusicAndCode · Accepted Answer · 2021-10-31 02:03:44Z

0

Your object is shared around the app. It's the same reference that every class uses. Maybe you should try to use a factory method:

class PurchaseOrder(Model):
    unitprice = get_currency_column()
    ...other columns

get_currency_column():
    return Column(Numeric(10,2))

But to be frank, I would be surprised that the column definition changed that often.

answered Oct 31, 2021 at 2:03

MusicAndCode

9188 silver badges23 bronze badges

1 Comment

Mark Langenhoven Over a year ago

This is exactly what I was looking for. Thank you! This is a bit of a strange case. Each customer will be able to modify their copy of the database to their needs, so things like currency will vary from country to country.

Collectives™ on Stack Overflow

sqlalchemy use variable for column definition

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related