Create a Calculated Column in SQLAlchemy Core Table

Question

I have a SQLAlchemy table defined as following:

user = Table('user', MetaData(),
             Column('id', Integer),
             Column('first_name', String),
             Column('last_name', String))

I often need to refer the full_name of a user during query, for example:

sql = (select([
          user.c.id,
          (user.c.first_name + user.c.last_name).label('full_name')
       ]).where(user.c.id == 123))

Since full_name is used in many places, so such kind of code has a lot copies.

I wonder whether there is a way in SQLAlchemy I can create a calculated Column, so that I can conveniently use it just like other normal Column, the SQLAlchemy automatically converts it into (user.c.first_name + user.c.last_name).label('full_name') whenever I refer user.c.full_name

sql = (select([
          user.c.id,
          user.c.full_name
       ]).where(user.c.id == 123))

I searched and found there some solution in SQLAlchemy ORM using column_property or hybrid_property. The difference in my case is that I can only use SQLAlchemy Core.

Answer 1

It is not possible to create calculated columns in sqlalchemy core. You don't need to do this however, all that is required is to save your expression in a variable and then use this in your select statements. If you have many of these to store then you could namespace them by storing them all in a collection. In the example below I've used an SQLAlchemy Properties object for this purpose so it will behave in similar manner to the columns collection.

class Table(sqlalchemy.Table):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.d = sqlalchemy.util._collections.Properties({})

user = Table('user', MetaData(),
             Column('id', Integer),
             Column('first_name', String),
             Column('last_name', String))
user.d.full_name = (user.c.first_name + user.c.last_name).label('full_name')
user.d.backwards_name = (user.c.last_name + user.c.first_name).label('backwards_name')

sql = (select([
          user.c.id,
          user.d.full_name
       ]).where(user.c.id == 123))