query multiple tables sqlalchemy

Question

I am trying to query across 4 tables. They are all bound by foreign keys and they all have relationships defined. The problem I am having is that I can't seem to get the value I'm after.

I have 4 tables:

TDeviceReport:

ixDeviceReport(int, primary key) 
ixDeviceType(int, foreign key)   
ixReportType(int, foreign key)

TReportType:

ixReportType(int, primary key)   
sReportType(string)

TReportSection

ixReportSection(int, primary key)          
ixReportType(int, foreign key)
ixSection(int, foreign key)

and TSection: ixSection(int, primary key) sSection(string)

I am trying to get all the sections names (TSection.sSection) belonging to a deviceTypeID. The problem is I'm somewhat new to sqlAlchemy and not sure how to go about doing this. I've tried to set up a query but I'm not getting any results.

for section in DBSession.query(TDeviceReport, TReportType, TReportSection, TSection).join(TReportType).join(TReportSection).join(TSection).filter(TDeviceReport.ixDeviceType==deviceTypeID).all():
    print "------------------------------------------------"
    print "section : " + str(section )

any guidance would be appreciated

EDIT I redid the query a bit and this is the result that it prints

#get section headers
sections = []
for section in DBSession.query(TSection.sSection).join(TReportSection).join(TReportType).join(TDeviceReport).filter(TDeviceReport.ixDeviceType==deviceTypeID).all():
    sections.append(str(section[0]))
    print "section: " + str(section) + "------------------------------------------"

Printed results:

section: (u'Trip Unit Results',)------------------------------------------
section: (u'Generic Header',)------------------------------------------
section: (u'Circuit Breaker Data',)------------------------------------------
section: (u'Trip Unit Data',)------------------------------------------
section: (u'Sensor Data',)------------------------------------------
section: (u'Insulation Resistance',)------------------------------------------
section: (u'Contact Resistance',)------------------------------------------
section: (u'Breaker Inspection',)------------------------------------------
section: (u'Cell Inspection',)------------------------------------------

Miro K. · Accepted Answer · 2014-06-05 18:28:33Z

1

You can try this print section.TSection.sSection. 'section' should be a instance, and in that case this works. If that doesn't work, then your query and joining stuff is wrong, in that case this would help: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html

You didn't tell me what does that prints: print "section : " + str(section ). It would help a lot if you could tell me that, if it is empty then your query is wrong.

edited Jun 5, 2014 at 18:28

answered Jun 5, 2014 at 18:21

Miro K.

3451 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

john Over a year ago

hey thanks for the answer. I changed my query a bit( will post it as an edit). It now prints the sections (or at least it appears to be)

Miro K. Over a year ago

for my eyes that still looks bit funny, did that fix your problem? currently only this join makes sense for me ".join(TDeviceReport)"

john Over a year ago

well it seems to be printing the right information. I will look into it more

Collectives™ on Stack Overflow

query multiple tables sqlalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related