28 May 2016 ∞

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)