Frazer McLean

It's useful to create a __repr__ method for your SQLAlchemy models, if not for debugging, then to easily print query results.

It's common to see the following, as shown in the SQLAlchemy documentation

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)

With my represent module (PyPI, readthedocs), this could be changed to the following:

from represent import ReprHelperMixin

class User(ReprHelperMixin, Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def _repr_helper_(self, r):
        r.keyword_from_attr('name')
        r.keyword_from_attr('fullname')
        r.keyword_from_attr('password')

The mixin class provides __repr__ and _repr_pretty_ (for IPython), and calls your class's _repr_helper_ method for what keywords to add.

It can be annoying to do this for every model class, so you can add it to the base class.

Where normally you'd create the base class like this:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

We can use as_declarative to add a mixin class. The SQLAlchemy inspection system can be used to get the attribute names (the key attribute), since column names can differ from the model attribute name.

from represent import ReprHelperMixin
from sqlalchemy.ext.declarative import as_declarative

@as_declarative()
class Base(ReprHelperMixin):
    def _repr_helper_(self, r):
        for col in inspect(self).mapper.column_attrs:
            r.keyword_from_attr(col.key)

The full example looks like this:

from represent import ReprHelperMixin
from sqlalchemy import Column, Date, Integer, String, create_engine, inspect
from sqlalchemy.ext.declarative import as_declarative
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite://')
Session = sessionmaker(bind=engine)


@as_declarative()
class Base(ReprHelperMixin):
    def _repr_helper_(self, r):
        for col in inspect(self).mapper.column_attrs:
            r.keyword_from_attr(col.key)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

Base.metadata.create_all(bind=engine)

Now add some users to the database.

session = Session()

session.add_all([
    User(name='wendy', fullname='Wendy Williams', password='foobar'),
    User(name='mary', fullname='Mary Contrary', password='xxg527'),
    User(name='fred', fullname='Fred Flinstone', password='blah')])

session.commit()

And finally print some query output:

result = session.query(User).all()
print(result)
[User(id=1, name='wendy', fullname='Wendy Williams', password='foobar'), User(id=2, name='mary', fullname='Mary Contrary', password='xxg527'), User(id=3, name='fred', fullname='Fred Flinstone', password='blah')]

pprint works great for larger models or sequences of models:

from IPython.lib.pretty import pprint
pprint(result)
[User(id=1, name='wendy', fullname='Wendy Williams', password='foobar'),
 User(id=2, name='mary', fullname='Mary Contrary', password='xxg527'),
 User(id=3, name='fred', fullname='Fred Flinstone', password='blah')]