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
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/287c5172-0b57-4d59-b90c-758f05273055%40googlegroups.com.
