Trying to Convert SQL query to SQLAlchemy query

Question

I'm trying to translate raw SQL into an sqlalchemy query, but so far I'm getting different results. The results I get from my attempt at sqlalchemy is missing Property objects (I get back a tuple with (ListingCalendarDays, None, PricingData)), so I think something is missing in the translation.

Original query:

 result = session.execute("""SELECT p.id as property_id,
 p.home_code,
 c.listing_id,
 c.calendar_date,
 c.available,
 ab.price
 FROM listing_calendar_days c
 LEFT JOIN properties p
 ON (p.id::integer = c.listing_id and p.id is not null and p.id <> '')
 LEFT JOIN pricing_data ab
 ON c.listing_id = ab.listing_id
 AND c.calendar_date = ab.price_date
 WHERE c.calendar_date >= '%s'
 AND c.calendar_date <= '%s' ;""" % ( now.isoformat(), end.isoformat()))

My Attempt:

query = (session.query( ListingCalendarDays, Properties, PricingData )
 .outerjoin(Properties,
 and_(Properties.id == str(ListingCalendarDays.listing_id),
 Properties.id != None))
 .outerjoin(PricingData,
 and_(ListingCalendarDays.listing_id == PricingData.listing_id,
 ListingCalendarDays.calendar_date == PricingData.price_date))
 .filter(ListingCalendarDays.calendar_date.between( now, end )))
result = query.all()

Answer 1

You can print out the query that SQLAlchemy generates by doing

print(query)

In this particular case, your join condition for Properties is wrong. str(ListingCalendarDays.listing_id) produces the literal string "ListingCalendarDays.listing_id", not the column listing_id as casted to varchar. For a cast, you need the cast() construct:

cast(ListingCalendarDays.listing_id, String)

In addition, you're also missing the p.id <> '' condition.

Putting it all together:

and_(Properties.id == cast(ListingCalendarDays.listing_id, String),
 Properties.id != None,
 Properties.id != "")