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. Note Anthony uses backref here, thats legacy, use back_populates on both parent and child. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

External Link stackoverflow.com [1] 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. backref is considered legacy # [2] 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. Note This post is a thought [3]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://stackoverflow.com/questions/51335298/concepts-of-backref-and-back-populate-in-sqlalchemy#answer-59920780 [2]: #backref-is-considered-legacy [3]: /thoughts/

Read a Range of Data - LIMIT and OFFSET - SQLModel SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness. sqlmodel.tiangolo.com [1] 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. session.query(User, Images).join(Images).where(User.id == 3).all() Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlmodel.tiangolo.com/tutorial/limit-and-offset/ [2]: /thoughts/

External Link stackoverflow.com [1] 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. @post_router.get("/posts/") async def get_posts( *, request: Request, session: Session = Depends(get_session), hx_request: Annotated[str | None, Header()] = None, accept: Annotated[str | None, Header()] = None, current_user: Annotated[User, Depends(try_get_current_active_user)], page_size: int = 10, page: int = 1, ) -> Posts: "get all posts" statement = ( select(Post) .where(Post.published) .order_by(Post.id.desc()) .limit(page_size) .offset((page - 1) * page_size) ) posts = session.exec(statement).all() posts = Posts(__root__=posts) if isinstance(current_user, RedirectResponse): is_logged_in = False else: is_logged_in = True if hx_request and page == 1 and len(posts.__root__) == 0: return HTMLResponse('<ul id="posts"><li>No posts</li></ul>') if hx_request and len(posts.__root__) == 0: return HTMLResponse("") if not hx_request and len(posts.__root__) == 0: return ["no posts"] if hx_request: return templates.TemplateResponse( "posts.html", { "request": request, "config":...

Column INSERT/UPDATE Defaults — SQLAlchemy 1.4 Documentation docs.sqlalchemy.org [1] sqlalchemy server_defaults end up as defaults in the database when new values are inserted. t = Table( "test", metadata_obj, Column("abc", String(20), server_default="abc"), Column("created_at", DateTime, server_default=func.sysdate()), Column("index_value", Integer, server_default=text("0")), ) CREATE TABLE test ( abc varchar(20) default 'abc', created_at datetime default sysdate, index_value integer default 0 ) Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://docs.sqlalchemy.org/en/14/core/defaults.html#server-invoked-ddl-explicit-default-expressions [2]: /thoughts/