join multiple tables using sql alchemy

[Nitin Jain]

Hi all

I want to join multiple tables using sqlalchemy ORM package and really finding it very difficult.

Using raw SQL query i am able to do successful query but not using sql alchemy .

Please let me know if somebody can point out what mistake i am doing. I guess it is related to maintaining proper relationships. 

Models:-

 **class AnnotationData(db.Model):**

   __tablename__ = 'annotationdata'   

 id = db.Column(db.Integer, primary_key=True, autoincrement=True)

 annotation_id = db.Column(db.String(50), primary_key=True, 

     autoincrement=False)

 image_id = db.Column(db.String(50), db.ForeignKey('images.image_id', 

     ondelete='CASCADE'))

 catalog_id = db.Column(db.String(50), db.ForeignKey('code.code_id', 

    ondelete='CASCADE'))

  **class RecordingSession(db.Model):**

  

__tablename__ = 'recording_session'

   id = db.Column(db.Integer, primary_key=True, autoincrement=True)

   recording_id = db.Column(db.String(50), unique= True , 

  nullable=False)

  videos = db.relationship('VideoClip', backref='recording_session',

   lazy='dynamic')

 class VideoClip(db.Model):

    __tablename__ = 'video_clip'

    id = db.Column(db.Integer, primary_key=True , autoincrement=True)

    video_clip_id = db.Column(db.String(50), unique = True , 

    primary_key=True)

    recording_id = db.Column(db.String(50), 

    db.ForeignKey('recording_session.recording_id'))

    video_images = db.relationship('Images', backref='video_clip', 

    lazy='dynamic')

 **class Images(db.Model):**

    __tablename__ = 'images'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    image_id = db.Column(db.String(50), primary_key=True, unique=True)

    video_clip_id = db.Column(db.String(50), 

    db.ForeignKey('video_clip.video_clip_id',ondelete='CASCADE'))

     annotation_data = db.relationship('AnnotationData', 

    backref='images', 

    lazy='dynamic')

    

Working Raq SQL Query :-

SELECT recording_session.recording_id, video_clip.recording_id, video_clip.video_clip_id, 

Images.video_clip_id, Images.Image_id, 

annotationdata.image_id, annotationdata.annotation_id, annotationdata.catalog_id

FROM (((recording_session

INNER JOIN video_clip ON recording_session.recording_id = video_clip.recording_id)

INNER JOIN Images ON Images.video_clip_id = video_clip.video_clip_id)

INNER JOIN annotationdata ON annotationdata.Image_id = Images.Image_id)

    

Not working SQL Alchemy query :-

  query_ = db.session.query(RecordingSession)

  query_ = query_.join(VideoClip, RecordingSession.recording_id == 

     VideoClip.recording_id) \

    .join(Images, Images.video_clip_id == VideoClip.video_clip_id

    .join(Images, Images.video_clip_id == VideoClip.video_clip_id) \

    .join(AnnotationData, Images.image_id == AnnotationData.image_id)

Thanks

[Mike Bayer]

so you are right-nesting your JOINs, which can be done, but the Query.join() method by itself won't generate those, there are more special APIs that can generate that.   However, inner joins are associative so the parenthesization isn't needed.

    

Not working SQL Alchemy query :-

  query_ = db.session.query(RecordingSession)

  query_ = query_.join(VideoClip, RecordingSession.recording_id == 

     VideoClip.recording_id) \

    .join(Images, Images.video_clip_id == VideoClip.video_clip_id

    .join(Images, Images.video_clip_id == VideoClip.video_clip_id) \

    .join(AnnotationData, Images.image_id == AnnotationData.image_id)

the query appears to join to the Images entity twice, so I would remove that second .join(Images) as you already have one of those.  otherwise looks fine.

Thanks

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/287c5172-0b57-4d59-b90c-758f05273055%40googlegroups.com.