During a recent "take-home exam" for a job interview, I was confronted with the common challenge of extracting information via SQLAlchemy, a popular ORM layer for Python, and converting it to JSON. The most popular answer on Stack Overflow involves writing some code to do a conversion that ought to be present natively in the SQLAlchemy library but apparently not.

After looking through the various answers, I was dissatisfied with all of them, so I fired up a Python REPL and started digging into SQLAlchemy itself from the outside. I dislike this sort of "empirical" software development but I dislike a raft of code when there must be a simpler solution.

There was.

Inside the REPL I was able to see that every row has a reference to its own table, and that the table has its own collection of columns, which give every cell its identity. Here, I've got a single row from the sample database in x, and I'm querying Python about it:

list(x.__table__.columns)
[Column('id', Integer(), table=<grouped_findings>, primary_key=True, nullable=False),
Column('grouping_type', Text(), table=<grouped_findings>), ...]
>>> list(x.__table__.columns)[0]
Column('id', Integer(), table=<grouped_findings>, primary_key=True, nullable=False)
>>> dir(list(x.__table__.columns)[0])
['__add__', '__and__', '__annotations__', '__bool__', '__class__', '__class_getitem__',
# ... Many lines elided ...
'server_default', 'server_onupdate', 'shares_lineage', 'startswith', 'stringify_dialect',
'supports_execution', 'system', 'table', 'timetuple', 'type', 'unique', 'unique_params', 
'uses_inspection']

Huh. What's that type field there? I expected it to be a string, but it's not:

>>> list(x.__table__.columns)[0].type
Integer()
>>> dir(list(x.__table__.columns)[0].type)
['Comparator', '__annotations__', '__class__', '__class_getitem__', '__delattr__', '__dict__',
# ... Many lines elided ...
'hashable', 'literal_processor', 'python_type', 'render_bind_cast', 'render_literal_cast', 'result_processor', 'should_evaluate_none', 'sort_key_function', 'with_variant']
>>> list(x.__table__.columns)[0].type.python_type
<class 'int'>

It's a constructor. It's literally the "How do we convert this thing we got from the database into a native Python object?" function. Digging further, the

Which means that converting a SQLAlchemy row into a Python dict suitable for conversion to JSON is just two lines of code:

def row2dict(row):
    return {
        col.name: col.type.python_type(getattr(row, col.name))
        for col in row.__table__.columns
    }

That's it. That's the entirety of the conversion. All of the answers on Stack Overflow are over-engineered silliness.