0

I have a raw sql join statement that I'm executing through session.execute, however the result isn't providing me with column names, just the column values. How would I get my column names so that I can assemble it into a dictionary value for JSON dumping?

def get(self, pow_id):
    try:
        results = db.session.execute(''
                                    'SELECT * FROM POW p JOIN completed_course cc1 on p.student_id = cc1.student_id '
                                    'JOIN term_course tc on cc1.term_course_id = tc.id '
                                    'JOIN completed_course cc on tc.id = cc.term_course_id '
                                    'LEFT JOIN specialization_course sc on tc.course_id = sc.course_id '
                                    'AND sc.specialization_id = p.specialization_id '
                                    'WHERE p.id = :val;', {'val': pow_id})
        for row in results:
            print(row)
        return {}, 200
    except Exception as e:
        raise Exception("")
RuSs
  • 769
  • 2
  • 12
  • 27
  • Does this question right here on SO help you? (https://stackoverflow.com/questions/6455560/how-to-get-column-names-from-sqlalchemy-result-declarative-syntax) – JustLudo Nov 01 '21 at 07:40
  • @JustLudo no these solutions are for sql alchemy's declarative syntax. It doesn't work on a RAW sql call. I'm trying to convert it to a declarative but there are problems there too. – RuSs Nov 01 '21 at 14:10
  • So, when executing a raw SQL it should return with a crossover between a mapping and a dictionary right? So technically if you know the columns you should be able to fetch them by just getting them like so: row['your_column_name_here']. If you don't know the columns, try to debug the code and inspect the result-set. EDIT: Alternatively you can try to use the object-like syntax like so: row.c.your_column_name_here . Also .... the return should be a RowProxy, which has a "keys" method (https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.RowProxy). – JustLudo Nov 01 '21 at 15:15
  • @JustLudo that's the problem, it's not returning columns in the result set. Only values. – RuSs Nov 01 '21 at 15:17
  • Is the result a RowProxy? If not, there might be something else going on. Do note, this is done by heart from memory of over 5 years ago, so I might be missing something obvious as well ..... – JustLudo Nov 01 '21 at 15:19

0 Answers0