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()

univerio · Accepted Answer · 2016-03-16 18:53:57Z

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 != "")

answered Mar 16, 2016 at 18:53

univerio

20.7k3 gold badges73 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

Trying to Convert SQL query to SQLAlchemy query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related