sqlalchemy#

SQLAlchemy cheat-sheet

Architecture

https://aosabook.org/en/v2/sqlalchemy.html

Online tutorials

• https://leapcell.medium.com/leapcell-the-next-gen-serverless-platform-for-python-app-hosting-0722d1b32047

Generating ERD from sqlalchemy

This posts tests some popular Python tools (sqlalchemy_data_model_visualizer , sqlalchemy_schemadisplay, and eralchemy2) to generate ERD (Entity-Relation Diagram) from sqlalchemy models.

The test code can be found in this Github repo.

First try on Quart an asyncio re-implementation of Flask

Flask is a little bit old-fashioned today (I know it's still widely used), as it's not async native, among others. When I prepared my fastapi-demo this weekend, I discovered a new framework called Quart, which is maintained by Pallet Project, the same community maintaining Flask. They said "Quart is an asyncio re-implementation of the popular Flask micro framework API. This means that if you understand Flask you understand Quart.". So I decided to give it a try.

Sqlalchemy eager loading

This posts describes the differences on selectinload, joinedload, subqueryload, these 3 popular eager loading techniques in Sqlalchemy (so as to SQLModel)

Compiling SQLAlchemy query to nearly real raw sql query

SQLAlchemy mixin in method

If I'm not wrong, the SQLAlchemy official doc provides some examples to explain how to share a set of common columns, some common table options, or other mapped properties, across many classes. But I cannot find how to share common methods (e.g. your customized to_dict() method). This post will just show you a POC to achieve this goal by using Python Mixin.

Using Python SQLAlchemy session in concurrent threads or tasks

SQLAlchemy DB session is not thread safe for both sync and async session. AsyncSession is only a thin proxy on top of a Session

The concurrency model for SQLAlchemy's Session and AsyncSession is therefore Session per thread, AsyncSession per task.

The best way to ensure this use is by using the standard context manager pattern locally within the top level Python function that is inside the thread or task, which will ensure the lifespan of the Session or AsyncSession is maintained within a local scope.

For applications that benefit from having a "global" Session where it's not an option to pass the Session object to specific functions and methods which require it, the scoped_session approach can provide for a "thread local" Session object; see the section Contextual/Thread-local Sessions for background. Within the asyncio context, the async_scoped_session object is the asyncio analogue for scoped_session, however is more challenging to configure as it requires a custom "context" function.