I’m looking to replicate a SQL query I’ve been using in pgAdmin to join multiple tables and download the resulting table into a CSV file. I’ve been successful with pgAdmin and want to achieve the same result using SQLAlchemy, as my application interacts with the database through SQLAlchemy.

In pgAdmin, I use a query like this:

SELECT * FROM visits
LEFT JOIN patients ON visits.patient_id = patients.patient_id
LEFT JOIN [many more left joins here]
WHERE visits.visit_id = 1;

To accomplish the same using SQLAlchemy, I attempted the following code:

def get_all_data_for_visit(self, visit_id: int):
    try:
        stmt = select(Visit, Patient).outerjoin(Patient, and_(Visit.patient_id == Patient.patient_id, Visit.visit_id == visit_id))
        result = self.db.execute(stmt).all()
        if result:
            for row in result:
                print(row.Patient.patient_id, row.Visit.visit_type)
            return result
        else:
            return None
    except OperationalError as e:
        self.show_error_msg()

However, this code returns a patients object and a visits object, which is not the desired outcome. I want to iterate over the objects and print all their attributes (e.g., row.Visit.visit_type) into the CSV. I can’t simply iterate over all existing objects because some might be None, but I still want the results (null in this case) in the table. With my SQL-query this works. With sqlalchemy I get an AttributeError.

I read related questions, but I could not find a solution to my specific question.

Thank you for your help!