Flask-SQLAlchemy join

Question

Hello I have a problem with join at flask-sqlalchemy. I am a beginner at database and flask.

These are my classes:

class Shopping(db.Model):
 __tablename__ = 'shoppings'
 id = db.Column(db.Integer, primary_key=True)
 product_name = db.Column(db.String(30), index=True, unique=False)
 price=db.Column(db.Float(10), index=True)
 date=db.Column(db.Date())
 s_type_id = db.Column(db.Integer, db.ForeignKey('shopping_types.id'))
 def __repr__(self):
 return 'Alisveris yeri :{0} Tutar :{1} Tarih: {2}'.format(self.product_name,self.price,self.date)
 def __list__(self):
 return [self.product_name,self.price,self.date]
class Shopping_Type(db.Model):
 __tablename__='shopping_types'
 id=db.Column(db.Integer,primary_key=True)
 type_name=db.Column(db.String(30), index=True, unique=True)
 types = db.relationship('Shopping', backref = 'shopping_types', lazy = 'dynamic')
 def __repr__(self):
 return '{0}'.format(self.type_name)

when I try on python terminal and run:

select shoppings.product_name ,shoppings.price, shoppings.date, shopping_types.type_name from shoppings join shopping_types ON shoppings.s_type_id=shopping_types.id

query

I get what I want but when I run flask-sqlalchemy command:

rslt=db.session.query(spng).join(st)
spng:Shopping(class)
st:Shopping_Type(class)

I get only Shopping data. I want to get Shopping + Shopping_Type data.

Thank you.

Answer 1

rslt = db.session.query(spng, st).join(st)

The result would be an enumerable of tuples of (Shopping, Shopping_Type)

• Burak Nebioglu: Thank you Van. I tried your solution at python terminal and it worked thank you so much.

Answer 2

rslt = db.session.query(spng, st).filter(spng.s_type_id == st.id).all()
for x, y in rslt:
 print("Product ID: {} Product Name: {} Sopping Type: {}".format(x.id, x.product_name, y.tye_name))

type(rslt) is a tuple, contains elements as the number of tables joining.