SQLAlchemy one(): No Results Found

Question

I keep getting a Traceback complaining about the system not finding a result. This should not be the case given that the object is actually going through (I know this as I have a print command to confirm what is going through the query). I believe the issue is found in the method of create_category_rating(..).

When I use .one(), it throws the complaint I have below. When I use .all, it returns AttributeError: 'list' object has no attribute '_sa_instance_state' (some one else had this issue). The docs at SQLAlchemy does a good job defining these methods, but for some reason my code crashes.

The issue: What is causing the program to break if the object is going through the retrieve method? There is a bug somewhere and I have been struggling with squashing it.

I am using SQLAlchemy and python2.7:

Traceback:

ERROR: notssdb.test.test.test1
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/Users/ack/code/venv/NotssDB/notssdb/test/test.py", line 111, in test1
    api.create_category_rating(2, 'Decision-Making', 'baseball', 'Becoming a Leader')
  File "/Users/ack/code/venv/NotssDB/notssdb/api/convenience.py", line 41, in create_category_rating
    assessment_results = self.retrieve_assessment_results(owner, assessment)
  File "/Users/ack/code/venv/NotssDB/notssdb/api/object.py", line 324, in retrieve_assessment_results
    filter(Assessment_Results.owner == owner).one()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2478, in one
    raise orm_exc.NoResultFound("No row was found for one()")
NoResultFound: No row was found for one()
   # output from Traceback 
   Decision-Making
   baseball
   Becoming a Leader
   Becoming a Leader
   test1 <Assessment(name='Becoming a Leader', text='better decisions')>

convenience.py (create method):

def create_category_rating(self, category_rating_int, category_name, username, name):
    category = self.retrieve_category(category_name)
    owner = self.retrieve_user(username) # added
    assessment = self.retrieve_assessment(name) #added
    assessment_results = self.retrieve_assessment_results(owner, assessment)
    return super(ConvenienceAPI, self).create_category_rating(category_rating_int, category, assessment_results)

object.py (retrieve method):

def retrieve_assessment_results(self, *args):
    id, assessment, owner = None, None, None
    if len(args) == 1:
        id, = args[0]
    elif len(args) == 2:
        assessment, owner = args
    else:
        raise ValueError('Value being passed is an object')
    if id is not None:
        return self.session.query(Assessment_Results).\
        filter(Assessment_Results.id == id).one()
    elif owner is not None:
        print 'test1', owner
        return self.session.query(Assessment_Results).\
        filter(Assessment_Results.owner == owner).one()
    elif assessment is not None:
        print 'test2', assessment
        return self.session.query(Assessment_Results).\
        filter(Assessment_Results.assessment == assessment).one()

Answer 1

You have the following:

self.retrieve_assessment_results(owner, assessment)

...but the arguments for "retrieve_assessment_results" is the following (with my comment added)...

def retrieve_assessment_results(self, *args):
    id, assessment, owner = None, None, None
    if len(args) == 1:
        id, = args[0]
    elif len(args) == 2:
        assessment, owner = args # Note this line!
    else:
        raise ValueError('Value being passed is an object')

You pass owner, then assessment. But if you detect two objects, you will read assessment, then owner. In other words, you are calling the method with one order of objects, and retrieve assuming the reverse order. You can even see this occur in the debug line:

print 'test1', owner

You'd expect this to print something like test1 <User(...)>, but instead it printed...

test1 <Assessment(name='Becoming a Leader', text='better decisions')>

Also, consider using Python keyword arguments instead:

def retrieve_assessment_results(self, id=None, assessment=None, owner=None):
    ...

Then your call becomes less ambiguous:

self.retrieve_assessment_results(owner=owner, assessment=assessment)

Answer 2

one() will throw an exception if the result set is empty. Use first() and you will get the first result or Python None if no results are present.

Ditto about all() because the question does not contain relevant traceback and code.