problem in execute query string with error lazy loading

[Ramin Farajpour Cami]

Hi,

when use this query first i get valid result of query but again i refresh page site i get this error:

Parent instance is not bound to a Session; lazy load operation of attribute 'City' cannot proceed

this is a my query:

SELECT p.ID AS 'persons_ID', p.FirstName AS 'persons_FirstName', p.LastName AS 'persons_LastName',
                            p.NationalCode AS 'persons_NationalCode', p.CityID AS 'persons_CityID', p.Mobile AS 'persons_Mobile',
                            p.Address AS 'persons_Address', cities_1.ID AS 'cities_1_ID', cities_1.Name AS 'cities_1_Name',
                            cities_1.ParentID AS 'cities_1_ParentID', cities_2.ID AS 'cities_2_ID', cities_2.Name AS 'cities_2_Name',
                            cities_2.ParentID AS 'cities_2_ParentID' , cast(@row := @row + 1 as unsigned) as 'persons_row_number'

                    FROM Persons p LEFT OUTER JOIN cities AS cities_2 ON cities_2.ID = p.CityID
                    LEFT OUTER JOIN cities AS cities_1 ON cities_1.ID = cities_2.ParentID , (select @row := 0) as init

execute query :

 lst = ses.query(Persons).options(joinedload('City')).from_statement(query).all()

model :

class Persons(Base):


        __tablename__ = 'persons'

        ID = Column(Integer, primary_key=True)
        FirstName = Column(String(50))
        LastName = Column(String(100))
        NationalCode = Column(String(10))
        CityID = Column(Integer, ForeignKey('cities.ID'))
        Mobile = Column(String(10))
        Address = Column(String(10))

        City = relationship("Cities",lazy="joined",join_depth=2, uselist=False)

        CitiesModel = Cities()

        ProvinceID = None

        HashID = None
        row_number = None

[Mike Bayer]

hi there -

there's no bug illustrated here. What's likely going on it that the Persons object is being expired and you are then trying to access Persons.City without a session present:

person = sess.query(Persons).options(...)...
sess.commit()  # expires all attributes, see http://docs.sqlalchemy.org/en/rel_1_0/orm/session_basics.html#committing
sess.close() # remove session
person.City # raises error

there's nothing unexpected about the above interaction. Please email the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy for questions on sessions and loading objects, thanks!

[Mike Bayer]

oh also, If from_statement() is used, you need to use contains_eager(), not joinedload(); see http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html?highlight=contains_eager#contains-eager. That also might be the problem here.

We can help a lot better if you provide a real test case; see http://stackoverflow.com/help/mcve for what is needed. thanks!

[Ramin Farajpour Cami]

Hi , Work it, Thanks, i post to stackoverflow.com but any answer :((,

i have one question, i have a model with access to another model , how to serializer in sqlalchemy?

please read my question:

how to serializer list object in sqlalchemy?

please answer i need this for convert to json list,

Thanks,Again,

[Mike Bayer]

hi -

this is a bug tracker where demonstrated bugs and feature requests can be reported for the project. For general use questions, please use the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy - there are a lot more helpful people there than there are here who can take the time to understand your questions and provide meaningful responses - thanks!

[Ramin Farajpour Cami]

sorry , thanks, sorry again for my question,