Posts tagged: sqlalchemy

Great example from Anthony showing how easy it is to practice building database orm models and playing with them in a repl. This is good practice even if you are in a big code base to be able to test and learn in a simplified code base that does not have a mountain of other code around atuh, permissions, security, and other complex things that come into real production code bases that might make it hard to focus on what you are trying to do.

Today I came across some sqlalchemy models that created some relationships, some used backref some used back_populates. I was stumped why, I had never came accross backref before and I felt skill issues sinking in.

https://docs.sqlalchemy.org/en/14/orm/backref.html

As stated in the sqlalchemy docs, backref is a legacy feature. Its shorthand to creating relationships between parent and child, but only adding it to the parent. While this is simpler it introduces some invisible magic.

Today I was running some sqlmodel queries through the sqlalchemy orm. Admittedly I’ve not done enough orm queries before, and I’ve done quite a bit of raw sql. I was trying to get objects from two separate models that had relationships setup.

session.query(User, Images).where(User.id == 3).all()

It is incredibly slow, and gives me the following warning.

SELECT statement has a cartesian product between FROM element(s)

What I learned from the SQLModel docs is that you should give it a join to correct this and go much faster.

How to sort results from a sqlalchemy based orm.

.order_by(model.Entry.amount.desc())

I needed this to enable paging on my thoughts api.

sqlalchemy server_defaults end up as defaults in the database when new values are inserted.