Built-in way to convert an ORM object (i.e. result of an ORM query) to a Python dictionary

[Markus Meskanen]

I suggest adding something that allows us to convert ORM objects directly into dictionaries like this:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(Text, required=True)
    birth_year = Column(Integer)

user = session.query(User).filter(User.birth_year==1996).one()
return dict(user)

This would not only allow extremely easy integration with JSON APIs, but sometimes it's better to handle these objects as dicts for speed etc.

Some things to consider:

• Recursion; what to do with relationships? Should one-to-many convert to a dict of lists? What if all of those many have more "sub" lists?
• Method of converting: 1. Support dict(obj), integrates neatly into Python and this way it supports more than just dict(), like custom mapping classes 2. Add a to_dict() method to the declarative base class. I like this the least, because an object shouldn't know how to convert itself into everything in the world 3. A custom function like sqlalchemy.orm.to_dict(obj). Imo better than option two, and it doesn't clutter the class either.
• Ignore fields, i.e. "convert this object to a dict, but ignore id column"

[Mike Bayer]

Hi there -

The title of this issue, "convert SQLAlchemy object to a dictionary", seems very trivial.

It is of course extremely simple to make a function that does the very direct dict() production:

from sqlalchemy import inspect
def orm_to_dict(obj):
    return {attr.key: getattr(obj, attr.key) for attr in inspect(obj).all_orm_descriptors}

But as you noted, the above function is already not sufficient. You suggest it should also handle recursion of relationships, handle collections. This suggests it also needs recursion detection so that it doesn't get into infinite loops. Also, the ability to "ignore" fields. So from a three line, to probably more of a 25-30 line function.

But what if I also want to be able to configure "ignore" recursively as well? E.g. go from A->b->c, A->d->e, but only from A->g and not g->h ? What if I want to be able to change the string name of fields from what they are in the ORM mapping to something else? What if I want special marshalling functions to run when various datatypes are encountered? What if I want to also marshall attributes on my ORM mappings that are not themselves mapped attributes? After I've defined all of these systems, wouldn't I perhaps want to be able to also go from a dictionary to an ORM mapped object? After all, I've never written an app that serializes to dictionaries, but doesn't also receive PUT/POST requests and go the other way as well.

We can start down the road of defining a whole configurational system to add all of these capabilties, since we now see that, "convert ORM object to a dict" is a very simple function to write for an exact use case, but to produce it in a way that is of general use without further programming needed is not simple at all. But we might want to ask, since this is a much bigger problem than it first seemed, are other people are already working on this.....a search like https://pypi.python.org/pypi?%3Aaction=search&term=sqlalchemy+json&submit=search shows that there is plenty.

When you are dealing with dictionary/json structures, you need to have an explicit schema that defines the structure you want to build. It needs to have hooks so that any aspect of serialization can be customized. and it definitely needs to be bidirectional.

This is kind of the paradox of the "library / end-user application". The end user application can add little object->dict helpers and all kinds of other helpers very easily, as these suit exactly the specific thing that the application needs at that moment. They can seem annoying to write since they are so simple, and maybe the library can provide them. But unfortunately it's not like that - when a feature moves up to be in the library, it now has to work for everybody, or it's useless. The real-world case for object->dict is way too complicated (and already addressed by the hard work of many third party library authors) to be part of the ORM directly.

I can go on here but I'm hoping you get the idea that "convert to a dict" is on its face much too trivial to be useful and in order to make it of generalized use would be something way out of scope for the SQLAlchemy library itself and is already something many third party libraries exist to handle.

[Mike Bayer]

I'm always open for discussion but currently IMO the task of serialization is bigger than the scope of within the library itself.