Tutos

• Advanced SQLAlchemy Features You Need To Start Using

Libs

• Sql alchemy mixins

Core vs ORM

SQLAlchemy core uses SQL Expression Language that provides a schema-centric usage paradigm whereas SQLAlchemy ORM is a domain-centric mode of usage.

• Core = Expression langage (select() etc ..)
• Orm = session.query(User) etc..

Queries

ORM

• session.query(User).filter(User.first_name == "toto")

Select

• select(User).where(User.first_name == “toto”)
• returns ORM obejcts
• scalars: use .scalars()to get the first row

Core / Expression language

• https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_expression_language.htm
• Lower level / closer to sql / rarer / more comprehensive
• Useful in hybrid properties

Select

• select(user_table).where(user_table.c.name == 'spongebob')
• returns Rows

Column Properties

• Like hybrid attributes but simpler

• Support overloaded python operators + sql expressions

• column properties won’t be populated before you commit the session, which might be unexpected when working with freshly created record

Hybrid attributes

• Allow both normal property access and sql generation to be used in queries (filters ..)

Many to Many Hybrid property

• join on secondary table

• See stack

Association proxy

• Proxy a one to many or M2M attribute
• Leaner code
• Can be used in queries

Aggregated attributes

• Creates a new column and populates it with the result of a property
• Faster performance than hybrid attributes
• Same syntax as an hybrid property
• Supposed to be automatic but I should test it more