Returning null where values don't exist in SQLAlchemy (Python)

Question

I've got 3 tables

tblOffers (tsin, offerId)
tblProducts (tsin)
tblThresholds (offerId)

I'm trying to do a select on columns from all 3 tables. The thing is, there might not be a record in tblThresholds which matches an offerId. In that instance, I still need the information from the other two tables to return... I don't mind if those columns or fields that are missing are null or whatever in the response. Currently, I'm not getting anything back at all unless there is information in tblThresholds which correctly matches the offerId.

I suspect the issue lies with the way I'm doing the joining but I'm not very experienced with SQL and brand new to SQLAlchemy. (Using MySQL by the way)

query = db.select([
    tblOffers.c.title,
    tblOffers.c.currentPrice,
    tblOffers.c.rrp,
    tblOffers.c.offerId,
    tblOffers.c.gtin,
    tblOffers.c.status,
    tblOffers.c.mpBarcode,
    tblThresholds.c.minPrice,
    tblThresholds.c.maxPrice,
    tblThresholds.c.increment,
    tblProducts.c.currentSellerId,
    tblProducts.c.brand,
    tblOffers.c.productTakealotURL,
    tblOffers.c.productLineId
]).select_from(
    tblOffers.
    join(tblProducts, tblProducts.c.tsin == tblOffers.c.tsinId).
    join(tblThresholds, tblThresholds.c.offerId == tblOffers.c.offerId)
)

I'm happy to add to this question or provide more information but since I'm pretty new to this, I don't entirely know what other information might be needed.

Thanks

Byron Coetsee · Accepted Answer · 2020-04-10 20:33:52Z

Try for hours -> ask here -> find the answer minutes later on your own 🤦‍♂️

So for those who might end up here for the same reason I did, here you go.

Turns out SQLAlchemy does a right join by default (from what I can tell - please correct me if I'm wrong). I added a isouter=True to my join on tblThresholds and it worked!

Link to the info in the docs: https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=join#sqlalchemy.orm.query.Query.join.params.isouter

Final code:

query = db.select([
    tblOffers.c.title,
    tblOffers.c.currentPrice,
    tblOffers.c.rrp,
    tblOffers.c.offerId,
    tblOffers.c.gtin,
    tblOffers.c.status,
    tblOffers.c.mpBarcode,
    tblThresholds.c.minPrice,
    tblThresholds.c.maxPrice,
    tblThresholds.c.increment,
    tblProducts.c.brand,
    tblOffers.c.productTakealotURL,
    tblOffers.c.productLineId
]).select_from(
    tblOffers.
    join(tblProducts, tblProducts.c.tsin == tblOffers.c.tsinId).
    join(tblThresholds, tblThresholds.c.offerId == tblOffers.c.offerId, isouter=True)
)

Collectives™ on Stack Overflow

Returning null where values don't exist in SQLAlchemy (Python)

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