How to perform a natural join on two tables using SQLAlchemy and Flask?

Question

I have two tables Entry and Group defined in Python using Flask SQLAlchemy connected to a PostgresSQL database:

class Entry (db.Model):
    __tablename__ = "entry"
    id = db.Column('id', db.Integer, primary_key = True)
    group_title = db.Column('group_title', db.Unicode, db.ForeignKey('group.group_title'))
    url = db.Column('url', db.Unicode)
    next_entry_id = db.Column('next_entry_id', db.Integer,  db.ForeignKey('entry.id'))

    entry = db.relationship('Entry', foreign_keys=next_entry_id)
    group = db.relationship('Group', foreign_keys=group_title)

class Group (db.Model):
    __tablename__ = "group"
    group_title = db.Column('group_title', db.Unicode, primary_key = True)
    group_start_id = db.Column('group_start_id', db.Integer)
    #etc.

I am trying to combine the two tables with a natural join using the Entry.id and Group.group_start_id as the common field.

I have been able to query a single table for all records. But I want to join tables by foreign key ID to give records relating Group.group_start_id and Group.group_title to a specific Entry record.

I am having trouble with the Flask-SQLAlchemy query syntax or process

I have tried several approaches (to list a few):

db.session.query(Group, Entry.id).all()

db.session.query(Entry, Group).all()

db.session.query.join(Group).first()

db.session.query(Entry, Group).join(Group)

All of them have returned a list of tuples that is bigger than expected and does not contain what I want. I am looking for the following result: (Entry.id, group_title, Group.group_start_id, Entry.url)

I would be grateful for any help.

Astha Saxena · Accepted Answer · 2016-06-09 17:23:53Z

3

I used the following query to perform a natuaral join for Group and Entry Table: db.session.query(Entry, Group).join(Group).filter(Group.group_start_id == Entry.id).order_by(Group.order.asc())

I did this using the .join function in my query which allowed me to join the Group table to the Entry table. Then I filtering the results of the query by using the Group.group_start_id which is a foreign key in the Group table which referred to the Entry.id which is the primary key in the Entry table.

answered Jun 9, 2016 at 17:23

Astha Saxena

511 silver badge8 bronze badges

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

Comments

Michal Frystacky · Accepted Answer · 2016-05-31 22:34:56Z

0

Since you have already performed the basic join by using the relationship() call. We can focus on getting the data you want, a query such as db.session.query(Entry, Group).all() returns tuples of (Entry, Group) type, from this you can easily do something like:

test = db.session.query(Entry, Group).one()
print(test[0].id)             #prints entry.id
print(test[1].group_start_id) # prints Group.group_start_id
#...

SQLAlchemy has great article on how joins work

answered May 31, 2016 at 22:34

Michal Frystacky

1,49522 silver badges38 bronze badges

2 Comments

Astha Saxena Over a year ago

Thanks for the suggestion! Unfortunately, that isn't exactly what I'm looking for. Using the query tool in pgAdmin, I can use this query to get the table I'm looking for: select * from "group" join "entry" on "group".group_start_id = "entry".id;. How can I run this using the SQLAlchemy model system? I'm able to run the raw query via db.engine.execute, and I get the result I want, however it is a tuple and not dictionary style object as would be preferred.

Michal Frystacky Over a year ago

Does the sample code I wrote not work? Or is there something else wrong with it?

Collectives™ on Stack Overflow

How to perform a natural join on two tables using SQLAlchemy and Flask?

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related