3

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? `

George Pamfilis
  • 1,397
  • 2
  • 19
  • 37

1 Answers1

7

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.

krassowski
  • 13,598
  • 4
  • 60
  • 92
  • if the username a `db.Column(db.String(64))` in your answer? can i also write it out as a column? – George Pamfilis Aug 13 '17 at 15:46
  • The `username` is not a column. It won't be stored in database (which is a good thing) but will *behave* like a column. For example having previously created `Staff(first_name='John', last_name='Smith')` you can query it with `Staff.query.filter_by(username='John Smith').one()`. – krassowski Aug 13 '17 at 16:14
  • And the maximal difference of `username` is 128; you could use validators to restrain the sumaric length of two columns. Refer to this discussion: https://stackoverflow.com/a/18580815/6646912 – krassowski Aug 13 '17 at 16:20
  • can i create a relationship to the username in another Model called Payroll for example? its a value i want to use in other models. it could be named full name for example instead of username. – George Pamfilis Aug 13 '17 at 16:20
  • Not to my knowledge. But you can use still use composite foreign keys for this. Take a look at this: https://stackoverflow.com/q/7504753/6646912 you can use foreign keys created in this manner to create a relationship. If you need help creating such a relation, this deserves a separate question. – krassowski Aug 13 '17 at 17:06