1

Let's say you want to iterate over the ORM attributes of an ORM class in sqlalchemy. So, you want a list of the ORM attributes. How do you get that list?

If the ORM class does not rename the attributes and thus the ORM attributes match the database columns, then you can use the solution from: https://stackoverflow.com/a/24748320/1023033 (btw, there is also a built-in (private) function _orm_columns() in the source code file /lib/sqlalchemy/orm/base.py that seems to provide this functionality)

But if the python ORM class has different names than the database columns (for example in these 3 ORM attributes):

>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column('pkey', Integer, primary_key=True)
...     name = Column('user_name', String)
...     fullname = Column('human_name', String)

then that method does not work. So, how do you do get the python version of the ORM attributes?

Community
  • 1
  • 1
TaiwanGrapefruitTea
  • 1,045
  • 2
  • 14
  • 25

3 Answers3

5

This is already implemented using the inspection system:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import inspect

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    y = Column(Integer)

print inspect(A).c
print inspect(A).c.x
print inspect(A).column_attrs
print inspect(A).column_attrs.x
print inspect(A).column_attrs.x.expression

http://docs.sqlalchemy.org/en/latest/core/inspection.html

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.columns

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.column_attrs

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.c

Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • the list of attributes is available using the .items() method so I don't have to know the names of the attributes beforehand: print( inspect(python_orm_class).column_attrs.items() ) – TaiwanGrapefruitTea Jan 14 '15 at 17:12
  • a nice overview of how to get information via inspection: http://docs.sqlalchemy.org/en/rel_0_9/orm/mapping_styles.html#runtime-intropsection-of-mappings-objects – TaiwanGrapefruitTea Jan 15 '15 at 08:18
  • When I try this I get the error: No inspection system is available for object of type ResultProxy – slashdottir Jul 10 '15 at 20:21
  • 6
    When are we going to see a simple built-in way to convert an ORM object into a dict (with support for non-circular recursive relationships etc.)? When using Flask-SQLAlchemy, it would be kinda awesome to do: `return jsonify([row.to_dict() for row in sqlalch_query.all()])` – Markus Meskanen Feb 24 '17 at 13:00
0

I defined a mixin class to augment 

Base = declarative_base()

with the mixin class defind as:

import inspect
import sqlalchemy as sqla 

class orm_mixin_class(object):
    """Additional functionality wanted in orm classes (that inherit from Base)."""

    @classmethod
    def orm_class_info(cls):
        """
        Get info about the orm class (as opposed to the database table it is mapped to).

        Returns:
            list of dict where each dict describes a orm class attribute.  
            Keys: s_class_attribute_name
        """
        o_leaf_level_class = cls   # this turns out the be the leaf class instead of this mixin class
        l_orm_attribute_pairs = inspect.getmembers(o_leaf_level_class)

        l_orm_class_info_dicts = []
        for (s_class_attribute_name, o_attr_type) in l_orm_attribute_pairs:
            d_attr_info = {}
            b_orm_attribute = False
            try:
                o_inspect = sqla.inspection.inspect(o_attr_type) 

                if isinstance(o_inspect, sqla.orm.attributes.QueryableAttribute):
                    b_orm_attribute = True
                    d_attr_info['s_class_attribute_name'] = s_class_attribute_name
            except:
                pass            

            if b_orm_attribute:
                # only orm attributes have an entry in the output list
                l_orm_class_info_dicts.append(d_attr_info)

        return(l_orm_class_info_dicts)

so the ORM attribute list can be easily obtained from a method call.

The ORM class declaration is now:

class User(Base, orm_mixin_class):
davidism
  • 121,510
  • 29
  • 395
  • 339
TaiwanGrapefruitTea
  • 1,045
  • 2
  • 14
  • 25
0

Modifications to the mixin class / class method approach from davidsim to return a list of the attribute names while filtering out sqlalchemy.orm.RelationshipProperty attribute types (e.g., foreign models that link to the model class?):

import inspect

import sqlalchemy

class orm_mixin_class(object):
    """
    Additional functionality wanted in orm classes (that inherit from Base).
    """

    @classmethod
    def col_names(cls):
        """
        Get info about the orm class (as opposed to the database table it is mapped to).

        Returns:
            list: orm class attribute names
        """
        column_names = []
        member_pairs = inspect.getmembers(cls)
        for name, type in member_pairs:
            try:
                inspected_type = sqlalchemy.inspection.inspect(type)
                if isinstance(
                    inspected_type, sqlalchemy.orm.attributes.QueryableAttribute
                ) and not isinstance(
                    inspected_type.property, sqlalchemy.orm.RelationshipProperty
                ):
                    column_names.append(name)
            except Exception:
                pass

        return column_names

Then modified my base class as:

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase, orm_mixin_class):
    [...]
Mark Frey
  • 1