CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
George Pamfilis
George Pamfilis

Reputation: 1487

How to set the value of a column in sqlalchemy based on two other columns of the same model?

i am building a web application. i have the following model.

class Staff(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    store_id = db.Column(db.Integer, db.ForeignKey('store.id'))
    first_name = db.Column(db.String(64))
    last_name = db.Column(db.String(64))
    username = db.Column(db.String(64))
    cell_phone = db.Column(db.Integer)
    email = db.Column(db.String(64))
    position = db.Column(db.String(64))
    # add average salary value based on payroll

i want the username to be first_name+last_name with the user typing only his first and last name in the form.

i know how to use @db.event.listens_for(Staff, "after_insert") etc and update it like that. But is there a way to define something within the model so it does it automatically without a triger? `

Upvotes: 3

Views: 5421

Answers (1)

krassowski
krassowski

Reputation: 15379

You should get familiar with property decorators.

For SQLAlchemy there is hybrid_property extension which I highly recommend to learn and use. Take a look at the example:

from sqlalchemy.ext.hybrid import hybrid_property

class Staff(db.Model):
    first_name = db.Column(db.String(64))
    last_name = db.Column(db.String(64))

    @hybrid_property
    def username(self):
        return self.first_name + self.last_name

Documentations shows exactly such an usage for hybrid properties: SQL Expressions as Mapped Attributes: Using a Hybrid. Using a hybrid property you can easily use username to query your models and you do not need to store the data twice in your database. With a few additional lines you should be able to create update constructs in the upcoming SQLAlchemy v1.2.

Edit: To state it explicitly - you can use column_property too (as stated in linked documentation):

username = column_property(first_name + last_name)

Which may look better but is less powerful (has no update statement among others).

PS. I bet this is a duplicate but I cannot find an identical question.

Upvotes: 9

Related Questions