CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Byron Coetsee
Byron Coetsee

Reputation: 3593

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

I've got 3 tables

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

Upvotes: 0

Views: 620

Answers (1)

Byron Coetsee
Byron Coetsee

Reputation: 3593

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

Upvotes: 1

Related Questions