Menu
I am trying to translate SQL into SQLAlchemy. The SQL version of the query I want is as follows:
SELECT * from calendarEventAttendee JOIN calendarEventAttendanceActual ON calendarEventAttendanceActual.id = calendarEventAttendee.attendanceActualId LEFT JOIN (SELECT bill.id, bill.personId, billToEvent.eventId FROM bill JOIN billToEvent ON bill.id = billToEvent.billId) b ON b.eventId = calendarEventAttendee.eventId AND b.personId = calendarEventAttendee.personId WHERE b.id is NULL My SQLAlchemy query is as follows:
query = db.session.query(CalendarEventAttendee).join(CalendarEventAttendanceActual) sub_query = db.session.query(Bill, BillToEvent).join(BillToEvent, BillToEvent.billId == Bill.id).subquery() query = query.outerjoin(sub_query, and_(sub_query.Bill.personId == CalendarEventAttendee.personId, Bill.eventId == CalendarEventAttendee.eventId)) results = query.all() I am getting an error AttributeError: 'Alias' object has no attribute 'Bill'
If I adjust the SQLAlchemy query to the following:
sub_query = db.session.query(Bill, BillToEvent).join(BillToEvent, BillToEvent.billId == Bill.id).subquery() query = query.outerjoin(sub_query, and_(sub_query.Bill.personId == CalendarEventAttendee.personId, sub_query.BillToEvent.eventId == CalendarEventAttendee.eventId)) results = query.all()
I get an error AttributeError: Bill
Any help would be appreciated, thanks!
313
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
I've found that the following works to join two tables: result = session. query(User, Document). select_from(join(User, Document)). filter(User.
Python Flask and SQLAlchemy ORM All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
The select() method of table object enables us to construct SELECT expression. The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. Here, we have to note that select object can also be obtained by select() function in sqlalchemy.
Once you call subquery(), there is no access to objects, but only to columns via .c.{column_name} accessor.
Do the following for sub_query instead: load only the columns you need in order to avoid any name collisions:
sub_query = db.session.query( Bill.id, Bill.personId, BillToEvent.eventId ).join(BillToEvent, BillToEvent.billId == Bill.id).subquery() Then in your query use column names with .c.column_name:
query = query.outerjoin( sub_query, and_( sub_query.c.personId == CalendarEventAttendee.personId, sub_query.c.eventId == CalendarEventAttendee.eventId) ) results = query.all() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
