SQLAlchemy query from multiple tables

Question

I am trying to query from two tables.. my appointments and clients. the client number is my foriegn key in appointments that I can pull from the clients database on.

Right now, I am just returning guests to see what it is doing, I am getting this error: TypeError: repr returned non-string (type tuple)

 @app.route('/calendar')
def weeklycal():
    weekyrnum=request.args.get('weekyr')
    guests = db.session.query(Appointments,Clients).filter(Appointments.clientnumber == Clients.clientnumber).filter(Appointments.weekyr == weekyrnum).all()
return

render_template(calbyWeek.html",guests=guests)

How can I query everything from appointments and clients with clientnumber being the column to join on (which is defined as the foreign key in Appointments model), and filter by the week?

Philliproso · Accepted Answer · 2018-02-28 07:02:33Z

6

Reference the individual columns from multiple tables in you query, plus make sure you join to additional tables.

guests=db.session.query(Appointments.time,Clients.name).join(Clients).filter(Appointment.clientnumber==Clients.clientnumber).filter(Appointments.weekyr==weekyrnum).all()

If all you actually want is the guests but use Appoitments in you filter then you also just need to add a join.

guests=db.session.query(Clients).join(Appointments).filter(Appointments.clientnumber==Clients.clientnumber).filter(Appointments.weekyr==weekyrnum).all()

answered Feb 28, 2018 at 7:02

Philliproso

1,2761 gold badge9 silver badges16 bronze badges

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

2 Comments

user6307916 Over a year ago

I want all columns in Appointments with just 2 columns from Clients.. What is the most efficient way to do this? do I need to define every single column in my query()?

Philliproso Over a year ago

columns = Appointments.__table__.columns+ [Clients.name, Client.other_column] db.session.query(*columns)

Collectives™ on Stack Overflow

SQLAlchemy query from multiple tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related