I have User(db.Model) and Photo(db.Model) and one to many relation between them, thus user has many photos.
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
photos = db.relationship('Photo', backref='user', lazy='dynamic')
def to_json(self):
json = {
'id': self.id,
'photos': [photo.to_json() for photo in self.photos] if self.photos else None,
'name': self.name
}
return json
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
name = db.Column(db.String(1024), nullable=False)
def to_json(self):
json = {
'id': self.id,
'user_id': self.user_id,
'name': self.name
}
return json
My goal was to select all of the users with all their photos with single query, and I did it using native query:
from sqlalchemy import text
statement = text('SELECT u.*, p.* FROM user u LEFT JOIN photo p ON u.id=p.user_id')
user_photo_query = db.session.query(User, Photo).from_statement(statement)
user_photo_tuples = user_photo_query.all()
users_dict = {}
db.session.expunge_all()
db.session.close()
for u, p in user_photo_tuples:
if not users_dict.get(u.id, None):
u.photos = list()
users_dict.update({u.id: u})
users_dict.get(u.id).photos.append(p)
users = users_dict.values()
return jsonify({'users': [user.to_json() for user in users]})
In the result of this query there is list of tuples (User_240298428, Photo_20394823). The photo is different in every record of this list. The user is the same for some records of the list. Because I don't want the users and the photos object to be bound to the session anymore I do:
db.session.expunge_all()
db.session.close()
So I iterate through the list and try to add all photos in the corresponding user.photos list.
users_dict.get(u.id).photos.append(p)
Here I get error:
Parent instance is not bound to a Session; lazy load operation of attribute 'user' cannot proceed
This is because of the relation that the user object has to the photo object:
photos = db.relationship('Photo', backref='user', lazy='dynamic')
What I want is to use user.photos as regular list, and not as SQLAlchemy relation. I want to add photos to the user list without this being connected somehow to sqlalchemy. Hope it's clear
lazy='raise'. So if you by accident try to access it in code, you get an exception and it does not incur joins on every query, unless you've included.options(joinedload('photos_collection'))or such to your query.