Model DML

Extends your SQLAlchemy models with DML mixins.

• Model DML
  • Features
  • How to grow with model_dml
    • Why not use model_dml
    • How not to use model_dml
    • What kind of change may be needed
  • Get model_dml for your project
    • Requirements
    • Installation
  • Use model_dml in your project
    • Modify your sqlalchemy Base
    • Compose your model with what you need
    • Use the DML methods

Features

• Four methods for DML operations: select, insert, update, delete.
• Add those methods to models through mixin classes.
• Support returning selected columns for all methods.

How to grow with model_dml

Why not use model_dml

• No typed return values. It is theoretically possible to add annotation that will infer the return type of the methods such as select(id, name) would return a namedtuple with id as int and name as str. If you want to add this feature, you should read the method code and implement it yourself. Without model_dml abstraction, the type inference works better.

How not to use model_dml

This package is not meant to be used systematically as an installed package. You may want to copy a specific function that you need in your project instead of installing the whole package.

What kind of change may be needed

Let's take the select method. It is implemented to return a sequence of rows. If you want to order the rows by a column, you need to change the SQL statement construction.

1. Find the source code of select method returning rows

   def _select_result(cls, where, columns, limit):
       stmt = sqlalchemy.select(*columns).where(*where).limit(limit)
       with cls.session_maker().begin() as session:
           return session.execute(stmt).all()
   

2. Copy implementation in your code

   # Your code
   stmt = sqlalchemy.select(*columns).where(*where).limit(limit)
   with cls.session_maker().begin() as session:
       return session.execute(stmt).all()
   # Your code
   

3. Update implementation accordingly to your needs

   ! Here session_maker should be the session factory of your project. Please see session_basics - using a sessionmaker.

   # Your code
   stmt = sqlalchemy.select(User.id, User.name).limit(10).order_by(User.name)
   with session_maker().begin() as session: 
       results = session.execute(stmt).all()
   # Your code
   

   Bravo :D

Get model_dml for your project

Requirements

• Python 3.8+

Installation

pip install model_dml

Use model_dml in your project

Modify your sqlalchemy Base

To use model_dml, you need to create a custom base class for your models with a session_maker method.

What is a sessionmaker:

• Using a sessionmaker
• sessionmaker documentation and example

class Base(sqlalchemy.orm.DeclarativeBase):
    @classmethod
    def session_maker(cls) -> sqlalchemy.orm.sessionmaker:
        return < Namespace >.Session

This method exist to allow DML methods to access the sessionmaker without creating a new reference that would also need to be monkey patched. By returning the sessionmaker from the namespace, only <Namespace>.Session needs to be monkey patched.

Compose your model with what you need

class User(base, model_dml.Insert, model_dml.Update, model_dml.Delete):
    __tablename__ = "users"
    id: sqlalchemy.orm.Mapped[int] = sqlalchemy.orm.mapped_column(primary_key=True)
    name: sqlalchemy.orm.Mapped[str] = sqlalchemy.orm.mapped_column(
        sqlalchemy.String(30))

You can use model_dml.DML which is a helper class that composes all the mixins together.

class User(base, model_dml.DML):
    __tablename__ = "users"
    id: sqlalchemy.orm.Mapped[int] = sqlalchemy.orm.mapped_column(primary_key=True)
    name: sqlalchemy.orm.Mapped[str] = sqlalchemy.orm.mapped_column(
        sqlalchemy.String(30))

Use the DML methods

User.insert(dict(name="John"))
User.insert({'name': "Jane"})
user = User.insert({'name': "Jack"}, returning=[User.id, User.name])