SqlAlchemy. Return of previous data after the update

Question

It is necessary to write a query on SQLAlchemy objects to return the previous values after the update.Postgresql

The following table is available:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, server_default=text("nextval('\"public\".users_id_seq'::regclass)"))
    last_name = Column(String(255), nullable=False)
    first_name = Column(String(255), nullable=False)
    email = Column(String(255), nullable=False, unique=True)

I need to return the previous data after the update. The raw query looks like this

update
    users x
        set
            email = '[email protected]'
        from
            (
            select
                *
            from
                users
            where
                id = 1 for update) y
        where
            x.id = y.id
        returning 
            y.*

An attempt to write something like this was unsuccessful

old_user: User = aliased(User, name='old_user')

update_stmt = update(User) \
    .where(user_old.id == User.id) \
    .where(User.id == 1) \
    .values(email = "[email protected]") \
    .returning(old_user)

return session.execute(update_stmt)

An error occurs sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'old_user.id'

But if you try to return a non-alias object, then an object with new values is returned.

Also, an attempt to return(text(public.old_user.*)) causes an error NotImplementedError

Answer 1

You cannot directly do what you are wanting. Once updated the only way to get the old value is having archived it to another table, or to rollback the update. There is however a workaround (not necessary a good one). Postgres allows a CTE to contain DML. So create 2 CTEs, the first retrieves the old data, the second does the update, and finally the main query retrieves the first CTE: (see demo)

with old_row as 
     ( select email   
         from users 
        where id = 1
      ) 
   , new_row as 
     ( update users 
          set email = '[email protected]'
        where id=1
     ) 
select * 
  from old_row;

Sorry, I do not know your obscurification language (SQLAlchemy) so I leave the translation into it to you you.