Flask SQLAlchemy query with concatenated columns

Question

I have a models like this:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(64), index=True)
    last_name = db.Column(db.String(64), index=True)
    
    def full_name(self):
        return '%s %s' % (self.first_name, self.last_name)

I want to get the full_name method in a query, I try it like it:

user = db.session.query(User.full_name()).all()

But I get this error message:

TypeError: full_name() missing 1 required positional argument: 'self'

Then I try to call the function without brackets:

user = db.session.query(User.full_name).all()

Then I got this error message:

sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<function User.full_name at 0x7f265960aae8>'

So, what is the correct way to query full_name() method in the User model..?

Ilja Everilä · Accepted Answer · 2022-02-09 17:23:20Z

There are a number of ways to accomplish this. Firstly, a hybrid attribute, a property that defines full_name on an instance and on the class (for querying).

This example is pure SQLAlchemy, but the hybrid attributes should be the same in Flask-SQLAlchemy.

import sqlalchemy as sa
from sqlalchemy.ext import hybrid

class User(Base):
    __tablename__ = 'users' 
                                                                                                                                                                        
    id = sa.Column(sa.Integer, primary_key=True)
    first_name = sa.Column(sa.String)
    last_name = sa.Column(sa.String)

    @hybrid.hybrid_property
    def full_name(self):  
        return self.first_name + ' ' + self.last_name

users = session.query(User).filter_by(full_name='Joan Doe').all()

Credit to Ilja for pointing out that this can be done using a single method in this case. This works because SQLAlchemy maps the + operator to the database's CONCAT function. If this weren't the case, an additional property decorated with hybrid.expression would be necessary to implement the behaviour at the class level.

SQLAlchemy inserts the necessary expressions into SQL statements as required, for example:

SELECT users.id, users.first_name, users.last_name 
FROM users 
WHERE users.first_name || ? || users.last_name = ?

Another way to achieve this is by defining a column_property:

from sqlalchemy import orm

class User(Base):
    ...
    full_name = orm.column_property(first_name + " " + last_name)

This inserts an expression into all queries on the model, so

query = sa.select(User)

will generate this SQL:

SELECT users.first_name || ? || users.last_name AS anon_1,
       users.id, 
       users.first_name, 
       users.last_name 
FROM users

Finally, there are computed columns. These are conceptually similar to column properties, but implemented on the database side (provided the database supports the required syntax). The database may or may not physically store the computed value; it may be possible to condition this behaviour by passing the persisted boolean keyword argument to Computed.

class User(Base):
    ...
    full_name = sa.Column(sa.String, sa.Computed(first_name + ' ' + last_name))

In this case a single method would do as well :) (the one used for expression would work for both, I think it's even the example in docs).

Dušan Maďar · Accepted Answer · 2019-09-08 14:57:12Z

1

You can use @classmethod.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(64), index=True)
    last_name = db.Column(db.String(64), index=True)

    @classmethod
    def full_name_filter(cls, fname, lname):
        return (cls.first_name == fname, cls.last_name == lname)

Then

user = db.session.query(User).filter(*User.full_name_filter("first", "last")).all()

edited Sep 8, 2019 at 14:57

answered Sep 8, 2019 at 14:11

Dušan Maďar

10k6 gold badges58 silver badges72 bronze badges

1 Comment

Tri Over a year ago

Hi, thanks for your participation to answers my question, but I still got the same error: TypeError: full_name() missing 1 required positional argument: 'self' and for more informations, I query it in different module, not in the class itself.

Collectives™ on Stack Overflow

Flask SQLAlchemy query with concatenated columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related