exclude relationship model sqlalchemy

[Ramin Farajpour Cami]

Hi,

i study this, i doing exclude one filed in my relation model "Comments --> Attach --> ImageAttachReport " binary because this is a large for select my query,

sqlalchemy not support exclude columns in relationship models?

class Comments(Base):

        __tablename__ = 'comments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentTime = Column(Time)
        CommentDate = Column(Date)
        Message = Column(String(4000))
        ReportID = Column(Integer, ForeignKey('reports.ID'))
        Report = relationship("Reports",lazy="joined",join_depth=2, uselist=False)
        Usertype = Column(Integer)
        UserID = Column(Integer, ForeignKey('users.ID'))
        User = relationship("Users",lazy="joined",join_depth=2, uselist=False)
        Attach = relationship("Attachments",lazy="joined",join_depth=2, uselist=True)

class Attachments(Base):

        __tablename__ = 'attachments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentID = Column(Integer, ForeignKey('comments.ID'))
        Comment = relationship("Comments",lazy="joined",join_depth=2, uselist=False)
        ImageAttachReport = Column(Binary(1000))
        ImageName = Column(String(255))

[Ramin Farajpour Cami]

query :

               Session = sessionmaker(bind=connect.ConnectorMySql())
                ses = Session()
                exclude = ['ImageAttachReport']   ## Here for avoid this Column 
                our_model =  ses.query(Comments).filter_by(ReportID=pID).all() ## How ?
                ses.close()
                return our_model

[Mike Bayer]

Hi there -

This is a mailing list question. (If your bug report ends with a question mark, it's a question, not a bug! ) You are most likely looking for defer(): http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=defer#deferred-column-loading

sess.query(Comments).options(defaultload(Comments.attachments).defer('imageattachreport'))

[Ramin Farajpour Cami]

Thanks , i know i try a long time but not work :( are you sure? again i see value "ImageAttachReport" select on database

class Comments(Base):

        __tablename__ = 'comments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentTime = Column(Time)
        CommentDate = Column(Date)
        Message = Column(String(4000))
        ReportID = Column(Integer, ForeignKey('reports.ID'))
        Report = relationship("Reports",lazy="joined",join_depth=2, uselist=False)
        Usertype = Column(Integer)
        UserID = Column(Integer, ForeignKey('users.ID'))
        User = relationship("Users",lazy="joined",join_depth=2, uselist=False)
        Attach = relationship("Attachments",lazy="joined",join_depth=2, uselist=True)

class Attachments(Base):

        __tablename__ = 'attachments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentID = Column(Integer, ForeignKey('comments.ID'))
        Comment = relationship("Comments",lazy="joined",join_depth=2, uselist=False)
        ImageAttachReport = Column(Binary(1000))
        ImageName = Column(String(255))

Session = sessionmaker(bind=connect.ConnectorMySql())
                ses = Session()
                our_model = ses.query(pModel).options(defaultload(pModel.Attach).defer('ImageAttachReport'))
                our_model =  our_model.filter_by(ReportID=pID).all()
                ses.close()
                return our_model

[Mike Bayer]

can't reproduce. using a complete mcve:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Users(Base):
        __tablename__ = "users"

        ID = Column(Integer, primary_key=True,autoincrement=True)

class Reports(Base):
        __tablename__ = "reports"

        ID = Column(Integer, primary_key=True,autoincrement=True)

class Comments(Base):

        __tablename__ = 'comments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentTime = Column(Time)
        CommentDate = Column(Date)
        Message = Column(String(4000))
        ReportID = Column(Integer, ForeignKey('reports.ID'))
        Report = relationship("Reports",lazy="joined",join_depth=2, uselist=False)
        Usertype = Column(Integer)
        UserID = Column(Integer, ForeignKey('users.ID'))
        User = relationship("Users",lazy="joined",join_depth=2, uselist=False)
        Attach = relationship("Attachments",lazy="joined",join_depth=2, uselist=True)

class Attachments(Base):

        __tablename__ = 'attachments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentID = Column(Integer, ForeignKey('comments.ID'))
        Comment = relationship("Comments",lazy="joined",join_depth=2, uselist=False)
        ImageAttachReport = Column(Binary(1000))
        ImageName = Column(String(255))


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
pModel = Comments
query = s.query(pModel).options(defaultload(pModel.Attach).defer('ImageAttachReport'))
query = query.filter_by(ReportID=5)
query.all()

query output is:

SELECT comments."ID" AS "comments_ID", comments."CommentTime" AS "comments_CommentTime", comments."CommentDate" AS "comments_CommentDate", comments."Message" AS "comments_Message", comments."ReportID" AS "comments_ReportID", comments."Usertype" AS "comments_Usertype", comments."UserID" AS "comments_UserID", reports_1."ID" AS "reports_1_ID", users_1."ID" AS "users_1_ID", comments_1."ID" AS "comments_1_ID", comments_1."CommentTime" AS "comments_1_CommentTime", comments_1."CommentDate" AS "comments_1_CommentDate", comments_1."Message" AS "comments_1_Message", comments_1."ReportID" AS "comments_1_ReportID", comments_1."Usertype" AS "comments_1_Usertype", comments_1."UserID" AS "comments_1_UserID", attachments_1."ID" AS "attachments_1_ID", attachments_1."CommentID" AS "attachments_1_CommentID", attachments_1."ImageName" AS "attachments_1_ImageName" 
FROM comments LEFT OUTER JOIN reports AS reports_1 ON reports_1."ID" = comments."ReportID" LEFT OUTER JOIN users AS users_1 ON users_1."ID" = comments."UserID" LEFT OUTER JOIN attachments AS attachments_1 ON comments."ID" = attachments_1."CommentID" LEFT OUTER JOIN comments AS comments_1 ON comments_1."ID" = attachments_1."CommentID" 
WHERE comments."ReportID" = ?

if I remove the options, then we can see ImageAttachReport shows up:

 SELECT comments."ID" AS "comments_ID", comments."CommentTime" AS "comments_CommentTime", comments."CommentDate" AS "comments_CommentDate", comments."Message" AS "comments_Message", comments."ReportID" AS "comments_ReportID", comments."Usertype" AS "comments_Usertype", comments."UserID" AS "comments_UserID", reports_1."ID" AS "reports_1_ID", users_1."ID" AS "users_1_ID", comments_1."ID" AS "comments_1_ID", comments_1."CommentTime" AS "comments_1_CommentTime", comments_1."CommentDate" AS "comments_1_CommentDate", comments_1."Message" AS "comments_1_Message", comments_1."ReportID" AS "comments_1_ReportID", comments_1."Usertype" AS "comments_1_Usertype", comments_1."UserID" AS "comments_1_UserID", attachments_1."ID" AS "attachments_1_ID", attachments_1."CommentID" AS "attachments_1_CommentID", attachments_1."ImageAttachReport" AS "attachments_1_ImageAttachReport", attachments_1."ImageName" AS "attachments_1_ImageName" 
FROM comments LEFT OUTER JOIN reports AS reports_1 ON reports_1."ID" = comments."ReportID" LEFT OUTER JOIN users AS users_1 ON users_1."ID" = comments."UserID" LEFT OUTER JOIN attachments AS attachments_1 ON comments."ID" = attachments_1."CommentID" LEFT OUTER JOIN comments AS comments_1 ON comments_1."ID" = attachments_1."CommentID" 
WHERE comments."ReportID" = ?

[Ramin Farajpour Cami]

Thanks Again @zzzeek , yes i see query on select without "ImageAttachReport " but i see "ImageAttachReport " output fetch from database , and access to image blob from database , please see image

SELECT comments."ID" AS "comments_ID", comments."CommentTime" AS "comments_CommentTime", comments."CommentDate" AS "comments_CommentDate", comments."Message" AS "comments_Message", comments."ReportID" AS "comments_ReportID", comments."Usertype" AS "comments_Usertype", comments."UserID" AS "comments_UserID", siteowners_1."ID" AS "siteowners_1_ID", siteowners_1."FirstName" AS "siteowners_1_FirstName", siteowners_1."LastName" AS "siteowners_1_LastName", siteowners_1."Email" AS "siteowners_1_Email", siteowners_1."Phone" AS "siteowners_1_Phone", siteowners_1."Title" AS "siteowners_1_Title", siteowners_1."Message" AS "siteowners_1_Message", siteowners_1."OrganizationName" AS "siteowners_1_OrganizationName", siteowners_1."LinkWeb" AS "siteowners_1_LinkWeb", siteowners_1."IsActive" AS "siteowners_1_IsActive", siteowners_1."SiteUserName" AS "siteowners_1_SiteUserName", siteowners_1."UserVerifyed" AS "siteowners_1_UserVerifyed", siteowners_1."AboutSite" AS "siteowners_1_AboutSite", siteowners_1."LogoSiteImage" AS "siteowners_1_LogoSiteImage", siteowners_1."RequestDate" AS "siteowners_1_RequestDate", siteowners_1."RequestTime" AS "siteowners_1_RequestTime", siteowners_1."RequestAcceptDate" AS "siteowners_1_RequestAcceptDate", siteowners_1."RequestAcceptTime" AS "siteowners_1_RequestAcceptTime", siteowners_1."Policy" AS "siteowners_1_Policy", siteowners_1."Domains" AS "siteowners_1_Domains", siteowners_1."DateExpireSite" AS "siteowners_1_DateExpireSite", siteowners_1."MinAmount" AS "siteowners_1_MinAmount", siteowners_1."DescAmount" AS "siteowners_1_DescAmount", siteowners_1."UserID" AS "siteowners_1_UserID", users_1."ID" AS "users_1_ID", users_1."Username" AS "users_1_Username", users_1."Email" AS "users_1_Email", users_1."Name" AS "users_1_Name", users_1."Password" AS "users_1_Password", users_1."AboutMe" AS "users_1_AboutMe", users_1."PersonalSite" AS "users_1_PersonalSite", users_1."Expert" AS "users_1_Expert", users_1."ImageProfile" AS "users_1_ImageProfile", users_1."Date" AS "users_1_Date", users_1."Type" AS "users_1_Type", users_1."IsActive" AS "users_1_IsActive", bugs_1."ID" AS "bugs_1_ID", bugs_1."Title" AS "bugs_1_Title", bugs_1."Description" AS "bugs_1_Description", status_1."ID" AS "status_1_ID", status_1."Description" AS "status_1_Description", status_1."Title" AS "status_1_Title", status_1."Fatherid" AS "status_1_Fatherid", reports_1."ID" AS "reports_1_ID", reports_1."Date" AS "reports_1_Date", reports_1."Time" AS "reports_1_Time", reports_1."Thanks" AS "reports_1_Thanks", reports_1."Points" AS "reports_1_Points", reports_1."Amount" AS "reports_1_Amount", reports_1."Title" AS "reports_1_Title", reports_1."SiteID" AS "reports_1_SiteID", reports_1."UserID" AS "reports_1_UserID", reports_1."BugID" AS "reports_1_BugID", reports_1."StatusID" AS "reports_1_StatusID", users_2."ID" AS "users_2_ID", users_2."Username" AS "users_2_Username", users_2."Email" AS "users_2_Email", users_2."Name" AS "users_2_Name", users_2."Password" AS "users_2_Password", users_2."AboutMe" AS "users_2_AboutMe", users_2."PersonalSite" AS "users_2_PersonalSite", users_2."Expert" AS "users_2_Expert", users_2."ImageProfile" AS "users_2_ImageProfile", users_2."Date" AS "users_2_Date", users_2."Type" AS "users_2_Type", users_2."IsActive" AS "users_2_IsActive", comments_1."ID" AS "comments_1_ID", comments_1."CommentTime" AS "comments_1_CommentTime", comments_1."CommentDate" AS "comments_1_CommentDate", comments_1."Message" AS "comments_1_Message", comments_1."ReportID" AS "comments_1_ReportID", comments_1."Usertype" AS "comments_1_Usertype", comments_1."UserID" AS "comments_1_UserID", attachments_1."ID" AS "attachments_1_ID", attachments_1."CommentID" AS "attachments_1_CommentID" 
FROM comments LEFT OUTER JOIN reports AS reports_1 ON reports_1."ID" = comments."ReportID" LEFT OUTER JOIN siteowners AS siteowners_1 ON siteowners_1."ID" = reports_1."SiteID" LEFT OUTER JOIN users AS users_1 ON users_1."ID" = reports_1."UserID" LEFT OUTER JOIN bugs AS bugs_1 ON bugs_1."ID" = reports_1."BugID" LEFT OUTER JOIN status AS status_1 ON status_1."ID" = reports_1."StatusID" LEFT OUTER JOIN users AS users_2 ON users_2."ID" = comments."UserID" LEFT OUTER JOIN attachments AS attachments_1 ON comments."ID" = attachments_1."CommentID" LEFT OUTER JOIN comments AS comments_1 ON comments_1."ID" = attachments_1."CommentID" 
WHERE comments."ReportID" = :ReportID_1

what happened here @zzzeek ?

[Mike Bayer]

I see many more tables in that query. you'd need to show me a complete code example that includes all those tables "users", "siteowners", "bugs", "reports", etc. I'd imagine that you aren't applying defaultload() the right way for your specific query or perhaps something else is eagerly loading to Attach.

[Mike Bayer]

can you jiust take the test case I gave you and work with that? It shows that the option works. You need to work with it, add in all of your models until you figure out what's different about it vs. your actual program (this is "divide and conquer").

[Ramin Farajpour Cami]

Thanks Again, No ! Ok please look it,

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker



Base = declarative_base()

class Users(Base):
        __tablename__ = "users"

        ID = Column(Integer, primary_key=True,autoincrement=True)

class Reports(Base):
        __tablename__ = "reports"

        ID = Column(Integer, primary_key=True,autoincrement=True)

class Comments(Base):

        __tablename__ = 'comments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        # CommentTime = Column(Time)
        # CommentDate = Column(Date)
        Message = Column(String(4000))
        ReportID = Column(Integer, ForeignKey('reports.ID'))
        Report = relationship("Reports",lazy="joined",join_depth=2, uselist=False)
        Usertype = Column(Integer)
        UserID = Column(Integer, ForeignKey('users.ID'))
        User = relationship("Users",lazy="joined",join_depth=2, uselist=False)
        Attach = relationship("Attachments",lazy="joined",join_depth=2, uselist=True)

class Attachments(Base):

        __tablename__ = 'attachments'

        ID = Column(Integer, primary_key=True,autoincrement=True)
        CommentID = Column(Integer, ForeignKey('comments.ID'))
        Comment = relationship("Comments",lazy="joined",join_depth=2, uselist=False)
        ImageAttachReport = Column(String(1000))
        # ImageName = Column(String(255))


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

# Session = sessionmaker(bind=e)
s = Session(e)

ed_user = Users()
s.add(ed_user)
dd = Reports()
s.add(dd)
fd = Comments(Message="asdasd",ReportID=1,UserID=1)
s.add(fd)

att = Attachments(CommentID=1,ImageAttachReport="asdasdasdsad")
s.add(att)


pModel = Comments
query = s.query(pModel).options(defaultload(pModel.Attach).defer('ImageAttachReport'))
query = query.filter_by(ReportID=1)
lst = query.all()
print lst[0].Attach[0].ImageAttachReport
print "Done"

[Mike Bayer]

OK is the question, "why is "asdasdasdsad" coming back" ? the answer is because the object is already loaded in memory. no SQL is emitted for that value.

if you do this:

att = Attachments(CommentID=1,ImageAttachReport="asdasdasdsad")
s.add(att)
s.commit()
s.close()

now you'll see the first query as before, but you will also see:

SELECT attachments."ImageAttachReport" AS "attachments_ImageAttachReport" 
FROM attachments 
WHERE attachments."ID" = ?

which is the lazy load.