SQLAlchemy - Trying Eager loading.. Attribute Error

Question

I access a a postgres table using SQLAlchemy. I want a query to have eagerloading.

from sqlalchemy.orm import sessionmaker, scoped_session, eagerload
from settings import DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, DATABASE_PORT, DATABASE_NAME
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, Boolean, MetaData, ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy.ext.declarative import declarative_base

def create_session():
  engine = create_engine('postgres://%s:%s@%s:%s/%s' % (DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, DATABASE_PORT, DATABASE_NAME), echo=True)
  Session = scoped_session(sessionmaker(bind=engine))
  return Session()

Base = declarative_base()
class Zipcode(Base):
  __tablename__ = 'zipcode'
  zipcode = Column(String(6), primary_key = True, nullable=False)
  city = Column(String(30), nullable=False)
  state = Column(String(30), nullable=False)

session = create_session()
query = session.query(Zipcode).options(eagerload('zipcode')).filter(Zipcode.state.in_(['NH', 'ME']))
#query = session.query(Zipcode.zipcode).filter(Zipcode.state.in_(['NH', 'ME']))
print query.count()

This fails with AttributeError: 'ColumnProperty' object has no attribute 'mapper'

One without eagerloading returns the records correctly.

I am new to SQLAlchemy. I am not sure what the problem is. Any pointers?

Answer 1

You can only eager load on a relation property. Not on the table itself. Eager loading is meant for loading objects from other tables at the same time as getting a particular object. The way you load all the objects for a query will be simply adding all().

query = session.query(Zipcode).options(eagerload('zipcode')).filter(Zipcode.state.in_(['NH', 'ME'])).all()

The query will now be a list of all objects (rows) in the table and len(query) will give you the count.