1

Python 3.6 and sqlalchemy 1.2.10 

result=db_query(db_session.query(orm_model).filter(orm_model.id==1,orm_model.time.between(1,2)).all())

if I return this result directly in a restful api:

return [x.to_json() for x in result]

Front end got empty content, and I found x.__dict__ has only one key "_sa_instance_state".

The "to_json()" is orm_model's function:

def to_json(self):
    jsonobj = {}
    for x in self.__dict__:
        jsonobj[x] = self.__dict__[x]
    if "_sa_instance_state" in jsonobj.keys():
        del jsonobj['_sa_instance_state']
    return jsonobj

If I print it before return, every thing is ok, front end got the right content.

print(result)
return [x.to_json() for x in result]

And if I remove the "all()":

result=db_query(db_session.query(orm_model).filter(orm_model.id==1,orm_model.time.between(1,2)))

every thing is ok, without print before return, front end got the right content.

The Model is:

Base = declarative_base()
class orm_model(Base):
    __tablename__ = 'orm_model'
    id = Column(INTEGER(11), primary_key=True)
    user = Column(INTEGER(11))
    ...
    def to_json(self):
        ...
    def __repr__(self):
        return '<orm_model %s>' % self.id

and I define the db_query is always with commit and rollback：

def db_query(sql=None):
    try:
        db_session.commit()
    except:
        db_session.rollback()
        return False
    else:
        return sql

My English is poor, sorry.

Peter Fung
  • 31
  • 3
  • Please include your model definitions. All in all you should provide a [mcve]: something others may test and use to verify your results. – Ilja Everilä Aug 27 '18 at 22:13
  • I add the model definitions in the question. – Peter Fung Aug 28 '18 at 05:31
  • I'm still certain that this is not complete. Do you commit or rollback between querying and returning your list of to-be-JSON? The default behaviour of SQLA is to expire all ORM loaded attributes on commit, which could explain your `print`: maybe your `__repr__` accesses the attributes and so they are reloaded. If you leave out the call to `all()` you have a `Query` object. Each time you iterate over it, it performs a new query, which would also explain your observations. – Ilja Everilä Aug 28 '18 at 06:15
  • You are right, I add the __repr__ and db_query definitions in the question. and the query always commit and rollback. So this query method is wrong ? And this link :https://stackoverflow.com/questions/1078383/sqlalchemy-difference-between-query-and-query-all-in-for-loops say the all() is suger of list(), will not query again, is that right? Thank you! – Peter Fung Aug 28 '18 at 07:16

0 Answers0