How to convert SQLAlchemy row object to a Python dict?

Advanced scenario and potential issues

SQLAlchemy Core: Not an ORM but Core

When you are operating with SQLAlchemy Core (not ORM), you can access the row data in this way:

result = connection.execute(query)
for row in result:
    # A column walk in the park 🚶‍♂️
    print(row['column_name'])


And if you are using a modern version of SQLAlchemy (1.4 or above), you can convert it directly to a dictionary:

for row in result:
    row_dict = dict(row)
    # Easy peasy lemon squeezy 🍋


Handling hybrid properties and relationships

Your SQLAlchemy models might be having hybrid properties or relationships, dealing with that:

from sqlalchemy.inspection import inspect

def enhanced_obj_to_dict(obj):
    data = obj_to_dict(obj)
    inspect_manager = inspect(obj.__class__)
    relationships = inspect_manager.relationships

    # Iterating over a relationship is more fun than real relationships! 🥳
    for rel in relationships:
        value = getattr(obj, rel.key)
        if value is None:
            data[rel.key] = None
        elif isinstance(value, list):
            data[rel.key] = [related_to_dict(child) for child in value]
        else:
            data[rel.key] = obj_to_dict(value)
    return data


Taking care to deliver every nugget of data from related tables accurately in your shiny new dictionary.

Escape from pitfalls in SQLAlchemy

Beware of missing or expired attributes which could lead to Hogwarts-like mystery-errors during dictionary conversion. Always make sure your SQLAlchemy session is active and all required data is loaded and accounted for. Just don't call it lazy, it has had a long day!

Optimizing performance with SQLAlchemy

Always remember, with great amount of data comes greater responsibility (and wait times). When dealing with a large dataset, take into account the cost of converting each row and check if you need all columns. If not, consider using precious tools like lazy-loading or load_only for speed. You don't want to keep your users waiting, do you?

Handling SQLAlchemy like a total Ninja

Tips to handle all data types, even the weird ones!

When SQLAlchemy model provisionally includes custom types or hybrid properties:

def safe_convert(data_type, value):
    try:
        return data_type(value)
    except (ValueError, TypeError):
        return None

row_dict = {col.name: safe_convert(col.type.python_type, getattr(row, col.name))
            for col in row.__table__.columns}


This technique cooks a more robust dictionary conversion that withstands unanticipated data types.

SQLAlchemy Core: When dealing with raw SQL queries

for row in resultproxy:
    dict_row = {column: value for column, value in row.items()}
    # Who said dealing with raw SQL couldn't be pythonic? 🐍


Here, resultproxy provides a healthy diet of generator interface for efficient row-by-row digestion.

Managing SQLAlchemy sessions - Life and (sudden) death

Keep a sharp eye on the scope of your session and convert rows before its expiry. This avoids complications with lazy-loaded attributes. SQLAlchemy sessions have a bizarre way of playing hide n seek, watch out!