How to perform a sub select in sqlalchemy

Question

I have this SQL that I want to render in sqlalchemy:

SELECT 
    name, 
    hired_on, 
    (SELECT name FROM department WHERE e.department_id = id) AS department,
    (SELECT name FROM roles WHERE e.role_id = role_id) AS role
FROM employee AS e

But am not sure how to go about it. Everything I've looked up talks about joins, which isn't what I want.

Answer 1

By guessing the structure of your tables, and provided that all employees have a department_id and a role_id, you can refactor your raw query like so:

SELECT 
    e.name, 
    e.hired_on, 
    d.name AS department,
    r.name AS role
FROM employee AS e 
JOIN department d ON e.department_id=d.id
JOIN role r ON e.role_id=r.role_id

From there, you can use joins with sqlalchemy:

session.query(Employee.name,
              Employee.hired_on,
              Department.name,
              Role.name)\
  .join(Department,
        Employee.department_id==Department.id)\
  .join(Role,
        Employee.role_id==Role.role_id)\
  .all()

This is definitely not a piece of code that will work if you just copy/paste it, but the idea is there.