get_or_create with SQLAlchemy 2's ORM
Published 33 minutes ago.
I've long loved Django's get_or_create ORM function. If you're not familiar, typical usage looks something like this:
from django.contrib.auth.models import User
user, created = User.objects.get_or_create(
username="johndoe",
defaults={"email": "john.doe@example.com"}
)
The above code is pretty self-explanatory: if a user with the username
johndoe exists, return it, otherwise create it with an email of
john.doe@example.com. It's concise and beautiful.
I've had to use SQLAlchemy 2 recently and I was surprised to find that it didn't have the same and examples I found, only worked for SQLAlchemy 1.x.
Here's how you can implement this function with SQLAlchemy 2:
import typing as T
import sqlalchemy as sa
from sqlalchemy.orm import Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
__abstract__ = True
@classmethod
def get_or_create(
cls, session: Session, defaults=None, commit=True, **kwargs
) -> T.Tuple[T.Self, bool]:
"""Django-inspired get_or_create."""
predicates = [getattr(cls, k) == v for k, v in kwargs.items()]
instance = session.scalar(sa.select(cls).where(*predicates))
if instance:
return instance, False
defaults = defaults or {}
instance_kwargs = kwargs | defaults
instance = cls(**instance_kwargs)
session.add(instance)
if commit:
session.commit()
return instance, True
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
first_name: Mapped[str] = mapped_column(sa.Text, nullable=False)
last_name: Mapped[str] = mapped_column(sa.Text, nullable=False)
email: Mapped[str] = mapped_column(sa.Text, nullable=False)
engine = sa.create_engine("...")
make_session = sessionmaker(bind=engine)
with make_session() as session:
user, created = User.get_or_create(
session,
email="john.doe@example.com",
defaults={
"first_name": "John",
"last_name": "Doe"
}
)
# created will be True, and the change will be committed to the database
Boo ya.
